Show words that is in table to a query

**CJ_London** · 11-01-2019, 01:45 PM

in the function

**hendrikbez** · 11-01-2019, 11:59 PM

Morning Ajax

I did change the field name

Code:

sqlStr = "SELECT fword FROM wordlist where fword in ('" & s & "')"

the field name in wordlist is fword.

But when I test the function, getting compile error.

Click image for larger version.

Name: nou.png
Views: 15
Size: 32.9 KB
ID: 40101

**CJ_London** · 11-02-2019, 03:24 AM

For the future, please copy/paste your code and surround with code tags. When I try to respond, the image disappears so I cannot see the code

Also, highlight which line of code the error is referring to

A quick scan of the code shows this
'convert to css, removing any double spaces (this will need to be a loop if there are triple spaces)
s = Replace(Trim(Replace(s, " ", " ")), " ", ",")
is now only replacing a single space with a single space

you have changed this section as well - should only have substituted fWord

While Not rst.EOF
s = Replace(s, "," & rst!words & ",", ",")
rst.MoveNext

The code is annotated so you know what each bit of code is doing - take the time to understand what the code is doing rather than just that it is 'doing something'

As for the error, I don't see anything relating to the error - except in the immediate window you don't appear to have applied an argument there

**hendrikbez** · 11-02-2019, 04:25 AM

I have changed the code and a words to fword

Code:

Function WordsNotInTable(s As String) As String
Dim rst As dao.Recordset
Dim sqlStr As String

'convert to css, removing any double spaces (this will need to be a loop if there are triple spaces)
s = Replace(Trim(Replace(s, " ", " ")), " ", ",")

'get fword that exist
sqlStr = "SELECT fword FROM wordlist where fword in ('" & s & "')"
Set rst = CurrentDb.OpenRecordset(sqlStr)

'remove found words from s
'add commas to front and end to make comparison easier and avoid 'words within words'

s = "," & s & ","
While Not rst.EOF
s = Replace(s, "," & rst!fword & ",", ",")
rst.MoveNext
Wend

'tidy up
rst.Close
Set rst = Nothing
WordsNotInTable = Mid(s, 2, Len(s) - 2) 'may need a bit of work if no words returned
Debug.Print WordsNotInTable

End Function

When I am running my quary now, getting error "Undefined fuction 'WordsNotin Table' in expression"

When I only run the function, getting new error Compile error "Sub or Fucction not defined"

**CJ_London** · 11-02-2019, 05:57 AM

When I am running my quary now, getting error "Undefined fuction 'WordsNotin Table' in expression"

I presume the space here 'WordsNotin Table' is not really there but added by the forum

your function needs to be in a standard module (not a form module) - and the module must have a different name

you still haven't fixed this

'convert to css, removing any double spaces (this will need to be a loop if there are triple spaces)
s = Replace(Trim(Replace(s, " ", " ")), " ", ",")

When I only run the function, getting new error Compile error "Sub or Fucction not defined"

I do not get this error - the relevant bit of code should be highlighted - and to be clear, when running in the immediate window you have something like

?wordsnotintable("abc")

**hendrikbez** · 11-02-2019, 06:24 AM

Yes the form has changed the name and put a space in between the words.

I have changed it to

Code:

s = Replace(Trim(Replace(s, " ")), " ", ",")

from this, as it was like this

Code:

s = Replace(Trim(Replace(s, " ", " ")), " ", ",")

I did make a new module under modules, with only the code that you have giving me, and rename it to "wordsnotintable".
Bur still get compile error.

**CJ_London** · 11-02-2019, 07:00 AM

you need to read my posts more carefully

and rename it to "wordsnotintable".

I said

your function needs to be in a standard module (not a form module) - and the module must have a different name

**hendrikbez** · 11-02-2019, 08:04 AM

I have changed the name.

When I run the query now it gives compile error on the following

Code:

s = Replace(Trim(Replace(s, " ")), " ", ",")

with this query

Code:

SELECT WordsNotInTable([TheWord])
FROM tblSynonyms

BUT when I change this

Code:

s = Replace(Trim(Replace(s, " ", " ")), " ", ",")

with same query, it gives compile error now here

Code:

WordsNotInTable = Mid(s, 2, Len(s) - 2) 'may need a bit of work if no words returned

**CJ_London** · 11-02-2019, 08:19 AM

I repeat - there should be two (2) spaces. you only have 1

s = Replace(Trim(Replace(s, " ", " ")), " ", ",")

with same query, it gives compile error now here

are you sure that is a compile error (i.e. you click dubug>compile) or a runtime error (i.e. run from the immediate window?)

what is the value of s being passed to the function?

**hendrikbez** · 11-02-2019, 09:43 AM

But if you look , you will see that there are 2 spaces already in there, see the third code from my previous post

I am trying to run Debug from query, it goes like this

1. on this line i mark it were to begin debug

Code:

Function WordsNotInTable(s As String) As String

Press Shift f8

Code:

s = Replace(Trim(Replace(s, " ", " ")), " ", ",")

Showing when hover over it "s = "Lelik"
Press shift f8

Code:

sqlStr = "SELECT fword FROM wordlist where fword in ('" & s & "')"

Sohwing when hover over it "sqlstr=""
press shift f8

Code:

Set rst = CurrentDb.OpenRecordset(sqlStr)

Showing rst=nothing, but on the above line, it has changed to

Code:

sqlStr = "SELECT fword FROM wordlist where fword in ('" & s & "')"

Showing when hover over it "sqlstr="SELECT fword FROM wordlist where fword in (Lelik)"
press shift 8

Code:

 s = "," & s & ","

s="Lelik"
press shift f8

Code:

 While Not rst.EOF

showing rst.eof ="false"
press shift f8

Code:

s = Replace(s, "," & rst!fword & ",", ",")

shows rst!fword = "Lelik"
shitf f8

Code:

 rst.MoveNext

shift f8

Code:

wend

shift 8

Code:

 While Not rst.EOF

showing rst.eof ="true"
shift 8

Code:

rst.colse

shift 8

Code:

Set rst = Nothing

shows nothing="nothing"
shidt 8

Code:

WordsNotInTable = Mid(s, 2, Len(s) - 2) 'may need a bit of work if no words returned

showing wordsnotintable=""
shift f8
runtime error 5"
invalid prosudure or argument

**CJ_London** · 11-02-2019, 10:56 AM

Press Shift f8
Code:
s = Replace(Trim(Replace(s, " ", " ")), " ", ",")

going back to post 12 - it should be

s = Replace(Trim(Replace(s, " ", " ")), " ", "','") (2 spaces!)

Showing rst=nothing, but on the above line, it has changed to
Code:
sqlStr = "SELECT fword FROM wordlist where fword in ('" & s & "')"
Showing when hover over it "sqlstr="SELECT fword FROM wordlist where fword in (Lelik)"

based on the code assigning a value to sqlStr - this should be

"sqlstr="SELECT fword FROM wordlist where fword in ('Lelik')"

for some reason, it is dropping the single quotes - which are required because it is a string

I have to go out now, back sometime tomorrow

**hendrikbez** · 11-02-2019, 11:19 AM

Thank you, enjoy your night or day, it is now 19:20 here

I have changed this code, for you to see I have put 1 and 2 in to see if it is what hyou need it to be

Code:

s = Replace(Trim(Replace(s,12"12"1,12"12")),12"12"1,12"12,12")

Stll give same error at the end

**accesstos** · 11-02-2019, 05:40 PM

Hi Hendrik!

I don't know if I have understood completely what you asking, but, did you try a query like this?

Code:

SELECT tblSynonyms.TheWord, Wordlist.Fword 
FROM tblSynonyms LEFT JOIN Wordlist ON tblSynonyms.TheWord = Wordlist.Fword 
WHERE Wordlist.Fword Is Null;

which returns the words of table tblSynonyms that not appears in table Wordlist.

P.S.:
It seems that you keep a separate table for each attribute of the words, which is not a good practice. Add the appropriate fields in table Wordlist and keep the words only into this table. Work with queries, with the corresponding fields for each attribute, instead of separate tables (e.g. qrySynonyms instead of tblSynonyms). So, no need to search about missing words.
Maybe this sounds difficult at this moment but will make your life easier for the future.

**hendrikbez** · 11-02-2019, 10:36 PM

Hi accesstos

Yes thank you, this is working, but how can I get it to if the word in tblSynonyms is 3 words(wont' be more than 3 words)eg. "Together we stand". and only stand is not in the wordlist. can it then when you run the query only show the word stand in the list.

Will it work on a table with 2 or 3 columns with words, that I want to also check if they are in wordlist.

I do not understand very well what you are saying

It seems that you keep a separate table for each attribute of the words, which is not a good practice

my wordlist table is only to have one word in, that is to when you search for a word eg. "standing"and in the search box type s???di??, it will give you all the words that is there with the same letters in the same place.
Wordlist is all the words in the language than can be used.

**CJ_London** · 11-03-2019, 03:37 AM

@Hendrik

Please find attached a small db I put together with a few words and phrases in it - is this what you are looking for?

Show words that is in table to a query

Thread Tools

Similar Threads

How to change words in a column in a table

Query To Look For Specific Words within a Large Database?

converting number into words in query

How to show table's title when combining 2 table in query

Query to find exactly matched words in Access

Posting Permissions