VBA DAO.Recordset not returning same results as Access Query

[boblarson]

Give me an example of what you are typing into the Service Text box.

 

[Chauncey7]

This is what the SQL Statement comes out to be:

[DataType] <> 'CDMD' AND [VALVE_NO] = 'XX-X-13' AND [SYSTEM] = 'OIL TANK' AND [LOCATION] = '1-11-11-X' AND [SERVICE] = 'VALVE,1 1/2'' BALL,FO STOR TK 5-26-0-F'

VALVE,1 1/2'' BALL,FO STOR TK 5-26-0-F'

is the service

 

[boblarson]

This is what the SQL Statement comes out to be:

[DataType] <> 'CDMD' AND [VALVE_NO] = 'XX-X-13' AND [SYSTEM] = 'OIL TANK' AND [LOCATION] = '1-11-11-X' AND [SERVICE] = 'VALVE,1 1/2'' BALL,FO STOR TK 5-26-0-F'

VALVE,1 1/2'' BALL,FO STOR TK 5-26-0-F'

is the service

Well, that would explain it. It would be the double quote in the text which is causing the problem.

You need to change your code (to replace any quotes):

strcriteria = "[DataType] <> '" & Form_CompareFullSubform.DataType & "' AND " & "[VALVE_NO] = '" & Form_CompareFullSubform.VALVE_NO & "' AND " & "[SYSTEM] = '" & Form_CompareFullSubform.SYSTEM & "' AND " & "[LOCATION] = '" & Form_CompareFullSubform.Location & "' AND " & "[SERVICE] = '" & Replace(Form_CompareFullSubform.SERVICE, Chr(34), """") & "'"


Be sure to do that for any of them that might have quotes in the entries being used.

 

[Chauncey7]

Bob that didn't work either. The criteria string actually does work for others that have quotes in the SERVICE. I replaced the code anyway, but still to no avail. I am at my whits end with this thing.:banghead:

 

[vbaInet]

Upload a stripped down version of your db, let's see it run.

 

[Chauncey7]

Can't do that. Classified info.

 

[vbaInet]

Obviously strip off confidential info.