Wildcard in SQL statement

[dynamictiger]

I have the following SQL statement as a qdfTemp:
SQLOutput "SELECT AusPostCodes.ID, AusPostCodes.Pcode, AusPostCodes.Locality, " & _
"AusPostCodes.State, AusPostCodes.Comments, AusPostCodes.DeliveryOffice, " & _
"AusPostCodes.PresortIndicator, AusPostCodes.ParcelZone, AusPostCodes.BSPnumber, " & _
"AusPostCodes.BSPname, AusPostCodes.Category " & _
"FROM AusPostCodes "
"WHERE (((AusPostCodes.Locality) Like [Forms]![frmNewRecord]![txtsuburb] & ' * ') " & _
"AND ((AusPostCodes.State) Like [Forms]![frmNewRecord]![txtstate] & "*&quot AND " & _
"((AusPostCodes.Comments) Like "PO" & "*&quot);, _
qdfTemp

I haven't tried using wildcards before in this type of statemtn and the code won't compile. I am sure this is not correct, but MSDN keeps crashing on the most useful looking page. As you can see this is a copy of a query SQL and it works well in the query. I tried substituting strings for the like statement and didn't have much success with that idea either.

 

[Steve101]

DynamicTiger,

(a) Since the SQL only contains a single table, it can be simplified by removing the AusPostCodes. qualifier where it appears.

(b) The problem you are having I think related to your use of quotes around text fields. In some cases, you are excluding required quotes; in other cases, you are confusing Access by using double quotes around a text constant in part of the sql building string, which itself requires these quotes to start and end it. I've replaced the quotes used around the text fields in trhe sql, by a single quote symbol. The corrected sql syntax then looks like this.

(c) I've also taken licence to remove unrequired brackets and make some other minor corrections. These do not alter the solution, but do simplify the SQL.

"SELECT ID, Pcode, Locality, " & _
" State, Comments, DeliveryOffice, " & _
" PresortIndicator, ParcelZone, BSPnumber, " & _
" BSPname, Category " & _
"FROM AusPostCodes "
"WHERE Locality Like '" & Forms!frmNewRecord!txtsuburb & "*' " & _
"AND State Like '" & Forms!frmNewRecord!txtstate & "*' " & _
"AND Comments Like 'PO*';"

Try incorporating this corrected SQL into your SQLOutput statement.

I hope that this helps,
Cheers,
Steve

 

[dynamictiger]

Thanks for the assistance with this. The inclusion of a wildcard confused me, now I can see the answer I understand the syntax better, and appreciate why I was confused.