Update Query doesn't do what I expected (1 Viewer)

I have a table called 'WORDLIST' containing a list of words and code to copy words from that table to another called 'temp' according to criteria inputted on a form. Any non-alphabetic character is treated as a wildcard. A list box on the form then displays the selected words. The problem is with the last of the Update queries before I add the words to the table 'temp'. It eliminates the words I want as well, leaving nothing. I can't see why it won't work, probably something to do with my logic.

s = Text6.Text '[this is the input from the form]
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE WORDLIST SET fIncludeWord = 1;" '[it starts by marking all words as included]
For I = 1 To Len(s)
j = Asc(Mid$(s, I, 1))
If j < 63 Or j = 64 Or (j > 90 And j < 97) Or j > 122 Then
If I = 1 Then
s = "?" & Mid$(s, 2)
ElseIf I = Len(s) Then
s = Left$(s, Len(s) - 1) & "?"
Else
s = Left$(s, I - 1) & "?" & Mid$(s, I + 1)
End If
End If
Next I
Text6.Text = s '[that just converts non-alphabetic characters to a question mark, ie, wildcards]
L = Len(s)
DoCmd.RunSQL "UPDATE WORDLIST SET fIncludeWord = 0 WHERE Len(fWord) <> " & L & ";" '[the words of incorrect length are eliminated]
For I = 1 To L
j = Asc(Mid$(s, I, 1))
r = Mid$(s, I, 1)
If (j > 64 And j < 91) Or (j > 96 And j < 123) Then
DoCmd.RunSQL "UPDATE WORDLIST SET fIncludeWord = 0 WHERE fIncludeWord = 1 And 'Mid$(fWord, " & I & ", 1)' <> " & Chr$(34) & r & Chr$(34) & ";" '[I want to eliminate words without the specific characters in the correct position. It eliminates everything???]
End If
Next I
DoCmd.RunSQL "DELETE * FROM temp;"
DoCmd.RunSQL "INSERT INTO temp SELECT * FROM WORDLIST WHERE fIncludeWord = 1;"
List21.Requery

I would be grateful for assistance. Thank you.

For the start, I could not see where you have assigned value for fWord. What is it?

Please describe exactly what you are trying to do. Do you really need a temp table?
Maybe there are options to achieve what you are trying to do.

What determines "incorrect length"?

I recommend you use Option Explicit at the top of your module to ensure all variables are DIMmed.

fWord is a field name from the table WORDLIST

I compile crosswords which friends and family have a go at. I have a list of words in a table which I want to search using wildcards. For instance, if I have a space in my puzzle that needs a seven letter word with particular letters in particular positions within the word, I want to search for as many options as possible to fit it, and choose from the results.
I added a field to the table which can be 1 or 0. I wanted to manipulate this field according to how the words match what I am searching for, ie: word length and the characters and their positions in the word.
I realise now I possibly don't need the other table. I can use the main table for the listbox, but would the table's much greater size slow things down? The main table (wordlist) has over 32000 words. The second table 'temp' would just have the words found in the search.

I don't think 32000 word/records is a major issue re speed.

Do you have a specific issue that you can describe with real values?
Or can you post a sample of your database in mdb format sufficient to highlight the problem/issue? Remove anything confidential or personal.

I have acc2003 and can not use accdb file.

The database will not save in .mdb format so I've reproduced the form, table and code.

Rony,

I'm not sure exactly what you were doing in the Click event.
Your application interested me, so I created something similar in Acc2003.

I have a Wordlist table (used your definition).
Created a form (Form1) similar to yours.

In order to search for a term, I needed a "dictionary of words".
I found a dictionary with 172,804 words (English). I copied it from the source and pasted it as a textfile.

The dictionary origin is :
http://homepage.ntlworld.com/adam.bozon/Dictionary.htm

Then using some code in the module1 I read the words and parsed them into the Wordlist table.

In the form, I have a procedure in the form Open event. And also a cSearch onclick event, where most everything occurs. I have put comments in the code, so you could follow it.

There is a button to do the Search and a Button to clear the Searchtext box.

I have indexed with no duplicates all of the fWords so it is pretty quick.

You can enter alphabetic char followed by ?(single wildcard char) or any non-alphabetic (except "*") too get converted to single wild card characters.
Note, an "*" represents a string of characters, so if you say Search for
A?L*y, it will search for words beginning with "A", that have "L" in position 3, and end with "y" . These words could be any length.
The number of words in the dictionary(wordlist) that match your search is displayed above the listbox lstResults.

I did not use a Temp table. And I didn't use the fIncludeWord field although I left it in the Table. You could use that field as an indicator if you add more words from other dictionaries, or manually yourself.

I zipped the file as Crossword.zip, but it is too large 3.67 mb to load at AWF.

The zip file contains the file xword.mdb, which is the database in mdb (2003) format.

You should be able to download the zip file from
http://www.4shared.com/zip/0ZUS7Djk/Crossword.html

I hope it's useful to you.
Post back as necessary.

jdraw, thank you very much for that. It works great. I have looked at the code and there is stuff for me to learn there, using 'Like' for instance. I used to use recordsets a lot at one time but Access 2010 doesn't seem to like them.