running a query in MS Access

[minckle]

I have create an application using Delphi 5 and MySQL, and now im trying to convert it to use MS Access, but im having problems with the SQL part.

For the MySQL bit i can run the below SQL and it seems to work OK, but when i connect it to Access it errors. The narrowed it down to a problem with '%' bit which is supposed to be a wildcard character.

Can anyone tell me what SQL i need???

SELECT *
FROM contact
WHERE id >=5
AND ((forename LIKE arSearch '%')
OR (surname LIKE arSearch '%'))


Thanks

 

[whosrdaddy]

Hi,

use '*' instead of '%'

--------------------------------------
What You See Is What You Get

 

[minckle]

Hi Yeh ive tried this but i get errors

this is my SQL

SELECT *
FROM contact
WHERE id >=5
AND ((forename LIKE aram '*')
OR (surname LIKE aram '*'))

This is the Error when i activate the Query

General SQL Error.
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in quey expresssion 'id >=5 AND ( forename LIKE Pa_RaM000 '*')'


Sorry i should have probably told you it was connecting via ODBC

 

[whosrdaddy]

add the * character to paramater value instead of doing it in the query.
if my memory serves me well % should also be possible on access.

your string should look like this :'id >=5 AND ( forename LIKE Pa_RaM000*)'


--------------------------------------
What You See Is What You Get

 

[GHolden]

Hi, I think you're missing the '&'

Try...

SELECT *
FROM contact
WHERE id >=5
AND ((forename LIKE :param & '*')
OR (surname LIKE :param & '*'))

There are two ways to write error-free programs; only the third one works.

 

[GHolden]

BTW.

If you're writing queries directly in Access you need to use '*'. If you're connecting via ADO for example you need to use '%' and ADO does the conversion for you (therefor '*' won't work as a wild card through an ADO query).





There are two ways to write error-free programs; only the third one works.