Quoting wild card (*) in SELECT statement

[operator]

Could someone give me a heads up on how to quote * in the select statement??

Here is my code. I am not sure where to put the * and how to quote it.
I am trying to catch the month. For example catch January by using Jan*
----------------------------------------------------------------------
records.Open "SELECT record.amount FROM record WHERE vendor_code = '" & vendor & "' AND period_covered LIKE '" & SelectMonth & "*';", Application.CodeProject.Connection, adOpenStatic, adLockOptimistic, adCmdText
----------------------------------------------------------------------

Thanks a bunch!!

 

[FoFa]

OK, all code aside, it depends on what you are trying to do.
A plain LIKE "*" will give you everything.
A LIKE "ABC*" will give you anything that starts with ABC
A LIKE "*XYZ" will give you anything that ends with XYZ
A LIKE "*AAA*" Would give you anything that contained AAA in it

Does that help?

 

[operator]

Thanks FoFa. I understand that but I am actually looking for the syntax.

SelectMonth is a combo control that I am quoting in the SELECT statement. At the same time, I also want to add * at the end of SelectMonth to catch the full word of the month like "January". I couldn't figure out the proper syntax for puting the SelectMonth and * together.

 

[grnzbra]

You might want to create a string variable and set it equal to your SQL statement and then look at it in the immediate window to see exactly what you are getting.

What is SelectMonth?

If you are using the first three letters of the month, it might be easeier to use a WHERE Left(period_covered,3)

 

[operator]

selectMonth is set as a String. It contains the value from a combo box control.
vendor is another String that contains the value from another combo box.

The purpose of the select statement is to extract the "amount" value from the records where the records have both "vendor" and "selectMonth" that are selected in the combo box.

Since the period_cover can be "Jan 31 - Feb 05", I don't think "WHERE Left(period_covered,3)" would work, would it??

What I got so far from the statement is the column header by display the name of the header in the msgbox. It does display the header, which is "amount". However, I also had another msgbox that tries to display the value , which gives me nothing.

I figure that's probably caused by not placing the * properly.
Is there any other possible reason that the value is not displaying??

 

[grnzbra]

Either way, it would pick up anything that started with Jan.

I think the best thing to do would be to go with a string variable and put your SQL statement into it and look at it in the immediate window. That may reveal the problem.

 

[operator]

Hmm... I used % instead of * and it worked.
------------------------------------------------
records.Open "SELECT record.amount FROM record WHERE vendor_code = '" & vendor & "' AND period_covered LIKE '" & SelectMonth & "%';", Application.CodeProject.Connection, adOpenStatic, adLockOptimistic, adCmdText
------------------------------------------------

Could anyone explain when to use * and when to use % for wildcard?
I should use * if I am running a SQL statement like in Query??
I should use % if I am quoting a SQL statement in VBA??

Appreciated for the help, grnzbra and FoFa!!

 

[grnzbra]

I just checked for wildcard in help and it has the same definition for both characters, but one is ANSI-89 and the other is ANSI-92. I remember having to use % in SQL Server stored procedures.

Do they both work in your Access application or just the %?

 

[operator]

* didn't return blank when I used the msg box to show me the value.
% returned the value in the column so just % works in my database.