Excel vba syntax for Access query

[rodders450]

Hi everyone, I'm new to the forum, therefore this may have been solved before, but searching hasn't found the answer. I have an Excel vba code which selects a record from an Access Database which all works. I now want to find a number of records that begin with a variable, so I have tried to append the wildcard "*" to the variable name (VarIn) and so far have not found any syntax that works????

Code is


Src = "SELECT ITEM.item, ITEM.description, ITEM.[product-code]" & _
"FROM Item WHERE ITEM.[product-code] Like '" & VarIn & "*'"

Anyone solved this before?

Many thanks :yes

Edited 11-Jun-06 by geekgirlau. Reason: insert vba tags

[Norie]

As far as I can see that should work.

How isn't it working?

[rodders450]

It just returns no records - I think! Removing the "*" from the code and then setting VarIn as a specific value the code returns the Recordset into an Excel sheet. Adding the "*" then only setting VarIn as the first few characters appears to return no records therefore I'm assuming Recordset is null. As I know records exist in the Database I'm assuming the code is not passing the "*" to the query ????

Any suggestions?

[acw]

Hi

How about building a string that includes the variable and the * in one go to see if that brings back anything.

Something like

Src = "SELECT ITEM.item, ITEM.description, ITEM.[product-code]" & _
"FROM Item WHERE ITEM.[product-code] Like 'X*'"

Replace the X with a valid entry.

Structure of SQL string looks to be OK to me.


Tony
Edited 11-Jun-06 by geekgirlau. Reason: insert vba tags

[MountainVogu]

Rodders,

Change your asterisk (*) wildcard for a percetage (%)

This is becase % is an SQL standard and the astersik is an OS level thing your query is specifically trying to return your varin parameter with a literal appended not a wildcard.

_ is also used as a single char for example t_o (t something o)

Cheers

[rodders450]

Many thanks MountainVogu. Thats the answer. All works fine now. Cheers!

[geekgirlau]

Don't forget to mark this thread as "Solved" by clicking on "Thread Tools" at the top of the screen.