PDA

View Full Version : Solved: Excel/Access - SQL statement - WHERE/AND (Office '97)



phendrena
08-05-2009, 02:10 AM
Hi There,

I currently have the following SQL statement in a userform :-
sSQL = "SELECT [RSA Errors].[ID], " & _
"[RSA Errors].[Month], " & _
"[RSA Errors].[DealerNumber], " & _
"[RSA Errors].[DealerName], " & _
"[RSA Errors].[CustomersName], " & _
"[RSA Errors].[PolicyRegNo], " & _
"[RSA Errors].[OriginalCM], " & _
"[RSA Errors].[CallReason], " & _
"[RSA Errors].[Comments], " & _
"[RSA Errors].[GenuineError], " & _
"[RSA Errors].[AmendmentComments], " & _
"[RSA Errors].[Scheme] " & _
"FROM [RSA Errors] " & _
"WHERE [RSA Errors].[Month] LIKE '%" & strMonth & "%' " & _
"AND [RSA Errors].[GenuineError] '%" & strRecords & "%' " & _
"ORDER BY [RSA Errors].[ID] AND [RSA Errors].[OriginalCM];"
This code doesn't locate any records unless i remove the AND statement. Is anyone able to suggest a solution as to why the inclusion of the AND statement doesn't work?

Thanks,

Bob Phillips
08-05-2009, 02:37 AM
You should frst check what strRecords actually contains. Then you should probably add an operator in the clause.

stanl
08-05-2009, 03:05 AM
like xld said

AND [RSA Errors].[GenuineError] '%" & strRecords & "%' "

probably needs

AND [RSA Errors].[GenuineError] = '%" & strRecords & "%' "
or
AND [RSA Errors].[GenuineError] > '%" & strRecords & "%' "
or
AND [RSA Errors].[GenuineError] < '%" & strRecords & "%' "
or
AND [RSA Errors].[GenuineError] LIKE '%" & strRecords & "%' "

or whatever

phendrena
08-05-2009, 05:14 AM
d'oh i forgot the LIKE operator!

thanks!