Querying Access DB Partial Matching Issues

[randym]

I would like to start by stating I am fairly new to vb, but do not hold that against me. I have been writing a little viewer program to extract data from an Access db and display it in a ListView control. I have created a set of buttons that change the sql query and call a function that loads the ListView and displays it. All that is working fine. Here comes the weird part....

I have added functionality to take the contents of a textbox box control and use it to put together a sql statement. Unfortunately, this is the part is not working. The code executes, but acts like it does not find any data that matches. Thinking the problem is due to my poorly written sql, I tried to write this query, although hard coded, in Access and was able to successfully execute it. I then got Access to show me the sql code and pasted it directly into my program.

sql = "SELECT modeldesc.ModelDescription, modeldesc.CertCode, certcode.Price " &
"FROM certcode INNER JOIN modeldesc ON certcode.CertCodeKey = modeldesc.CertCode " &
"WHERE (((modeldesc.ModelDescription) Like '*AMP*'));"

Same results... No errors, no messages, no data either.

If I remove the where clause it works like a champ.
Was hoping someone had a little insight on the problem here.

Thanks, Randy

[dunfiddlin]

Wildcards in the Like section: % not *

And what are all those parentheses for?

[randym]

I am not sure why Access uses the '*', I have used '%' in MS sql queries - I wrote the query in Access using their built in wizard and got it to run successfully and then got Access to show the SQL statement and simply pasted it into my VB program just to make the silly thing work. I agree that the parentheses are over the top, I put it down to the query wizard, but since it worked in Access, I did not want to change anything....

Just to test things, I replaced the '*' with '%', also removed extra parens and the query now works! Thanks for assistance.

FYI and for anyone else who cares... I went back to Access and tried the query with '%'s instead and it did not complain, but retreived no data...

Thanks again, Randy

[Shaggy Hiker]

Access goes totally crazy with parentheses. Those wildcards are likely to be engine dependent. You are probably using the JET database engine in VB, but may not be in Access anymore, especially if you are in a 64-bit version, since there isn't a 64-bit JET driver, so there could easily be slight syntactical differences.

Sounds like you went about it the right way, though.