Query works in Access but not with .Execute, yet there is no error.

[Puebles]

Greetings,



I am trying to replace a large volume of slow moving code that goes through each record in a table of key words and phrases (currently 32 records and subject to change) and searches the comment field of another table. When a match is found the field [ExcludeMe] is incremented by 1. This code below works exceptional fast and the [ExlucdeMe] field is properly updated when the SQL is run in the Accesss Query builder. However, when run in this code, there is no error, but the [ExcludeMe] field is not updated.

Do While Not rs5.EOF
    strExcludeThisWord = rs5![ExcludeThisWord]
    strSetToZero = " UPDATE tblComments SET tblComments.ExcludeMe = 0 WHERE (([AFS_NUMBER]>1)); "
    db.Execute strSetToZero, dbFailOnError
    strExcludeThese = "UPDATE tblComments SET tblComments.ExcludeMe = IIf(InStr(1,[comment],'" & strExcludeThisWord & "'),[ExcludeMe]+1,[ExcludeMe]) WHERE (((tblComments.AFS_NUMBER)>1)); "
'    MsgBox (strExcludeThese)
    db.Execute strExcludeThese, dbFailOnError
    rs5.MoveNext
Loop

Thank you in advance.

[Puebles]

Found it. The reset of the [ExcludeMe] field is inside the loop. It needs to be outside/before the loop.

[rpeare]

make your msgbox (strExcludethese) a debug.print strExcludeThese, cut and paste the code into a query designer, then try to run it, see what kind of error it generates and go backward from there.

This snippet of your code isn't much to go on but I think your code:

strSetToZero = " UPDATE tblComments SET tblComments.ExcludeMe = 0 WHERE (([AFS_NUMBER]>1)); " 
    db.Execute strSetToZero, dbFailOnError

Should be outside your loop, because what you're doing right now is setting your excludeme value to 0 for every record you process then incrementing it by 1 only if it meets an instr criteria. so I would think that the last record you're processing does not meet the criteria so in essence you're setting your value to zero, then adding zero to 0 then adding a maximum of 1 for every record in your database so the most you could possibly end up with is 1, but in all likelyhood it's going to be a 0 because a vast majority of your records will not meet the criteria.

strSetToZero = " UPDATE tblComments SET tblComments.ExcludeMe = 0 WHERE (([AFS_NUMBER]>1)); " 
db.Execute strSetToZero, dbFailOnError     
    
Do While Not rs5.EOF     strExcludeThisWord = rs5![ExcludeThisWord]     
     strExcludeThese = "UPDATE tblComments SET tblComments.ExcludeMe = IIf(InStr(1,[comment],'" & strExcludeThisWord & "'),[ExcludeMe]+1,[ExcludeMe]) WHERE (((tblComments.AFS_NUMBER)>1)); " 
     'MsgBox (strExcludeThese)     
     db.Execute strExcludeThese, dbFailOnError     
     rs5.MoveNext 
Loop