in the function
in the function
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
I have changed the code and a words to fword
When I am running my quary now, getting error "Undefined fuction 'WordsNotin Table' in expression"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 only run the function, getting new error Compile error "Sub or Fucction not defined"
I presume the space here 'WordsNotin Table' is not really there but added by the forumWhen I am running my quary now, getting error "Undefined fuction 'WordsNotin Table' in expression"
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, " ", " ")), " ", ",")
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 likeWhen I only run the function, getting new error Compile error "Sub or Fucction not defined"
?wordsnotintable("abc")
Yes the form has changed the name and put a space in between the words.
I have changed it to
from this, as it was like thisCode: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".Code:s = Replace(Trim(Replace(s, " ", " ")), " ", ",")
Bur still get compile error.
you need to read my posts more carefullyI saidand rename it to "wordsnotintable".
your function needs to be in a standard module (not a form module) - and the module must have a different name
I have changed the name.
When I run the query now it gives compile error on the following
with this queryCode:s = Replace(Trim(Replace(s, " ")), " ", ",")
BUT when I change thisCode:SELECT WordsNotInTable([TheWord]) FROM tblSynonyms
with same query, it gives compile error now hereCode:s = Replace(Trim(Replace(s, " ", " ")), " ", ",")
Code:WordsNotInTable = Mid(s, 2, Len(s) - 2) 'may need a bit of work if no words returned
I repeat - there should be two (2) spaces. you only have 1
s = Replace(Trim(Replace(s, " ", " ")), " ", ",")
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?)with same query, it gives compile error now here
what is the value of s being passed to the function?
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 debugPress Shift f8Code:Function WordsNotInTable(s As String) As String
Showing when hover over it "s = "Lelik"Code:s = Replace(Trim(Replace(s, " ", " ")), " ", ",")
Press shift f8
Sohwing when hover over it "sqlstr=""Code:sqlStr = "SELECT fword FROM wordlist where fword in ('" & s & "')"
press shift f8
Showing rst=nothing, but on the above line, it has changed toCode:Set rst = CurrentDb.OpenRecordset(sqlStr)
Showing when hover over it "sqlstr="SELECT fword FROM wordlist where fword in (Lelik)"Code:sqlStr = "SELECT fword FROM wordlist where fword in ('" & s & "')"
press shift 8
s="Lelik"Code:s = "," & s & ","
press shift f8
showing rst.eof ="false"Code:While Not rst.EOF
press shift f8
shows rst!fword = "Lelik"Code:s = Replace(s, "," & rst!fword & ",", ",")
shitf f8
shift f8Code:rst.MoveNext
shift 8Code:wend
showing rst.eof ="true"Code:While Not rst.EOF
shift 8
shift 8Code:rst.colse
shows nothing="nothing"Code:Set rst = Nothing
shidt 8
showing wordsnotintable=""Code:WordsNotInTable = Mid(s, 2, Len(s) - 2) 'may need a bit of work if no words returned
shift f8
runtime error 5"
invalid prosudure or argument
going back to post 12 - it should bePress Shift f8
Code:
s = Replace(Trim(Replace(s, " ", " ")), " ", ",")
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
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
Stll give same error at the endCode:s = Replace(Trim(Replace(s,12"12"1,12"12")),12"12"1,12"12,12")
Hi Hendrik!
I don't know if I have understood completely what you asking, but, did you try a query like this?
which returns the words of table tblSynonyms that not appears in table Wordlist.Code:SELECT tblSynonyms.TheWord, Wordlist.Fword FROM tblSynonyms LEFT JOIN Wordlist ON tblSynonyms.TheWord = Wordlist.Fword WHERE Wordlist.Fword Is Null;
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.
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
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.It seems that you keep a separate table for each attribute of the words, which is not a good practice
Wordlist is all the words in the language than can be used.
@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?