Hi Jenny, Jenny Campbell wrote:

Hi there

I know there's probably a really simple answer to this but I can't see it for looking. I've made an ODBC connection to a Access 97 database and am trying to run the following query:

select ph_user, ph_location, ph_directno, ph_extension, ph_mobile_ext from tblPhone where ph_user matches '*John*' order by 1

but get the following error:

Error, sql.error: ('37000', -3100, "[Microsoft][ODBC Microsoft Access 97 Driver] Syntax error (missing operator) in query expression 'ph_user matches '*John*''.")

Ideally, I would like to be able to search against a table for ph_user using their first name only and return all the other fields. Would appreciate any advice. We're using Zope 1.10.3

I'm not familar with Access, but most databases use something like: like '%John%' or ideally: like @lower('%john%') to catch all posibilities Please refer to your Access manual for this. If you would like to distinguish first name, second name, you could make seperate fields for the names. HTH Tino Wildenhain

Jenny, You are both right and wrong with your syntax. In Access through the Jet engine a wildcard is indeed '*' but when done through ODBC it gets translated and you need to use '%' on the client end. So your query should look like select ph_user, ph_location, ph_directno, ph_extension, ph_mobile_ext from tblPhone where ph_user matches '%John%' order by 1 That should then get translated by ODBC into whatever Access needs to do the query correctly. HTH Phil phil.harris@zope.co.uk ----- Original Message ----- From: "Jenny Campbell" <jjc@eec.co.nz> To: <zope@zope.org> Sent: Tuesday, April 04, 2000 6:46 AM Subject: [Zope] Simple SQL (?)

Hi there

I know there's probably a really simple answer to this but I can't see it for looking. I've made an ODBC connection to a Access 97 database and am trying to run the following query:

select ph_user, ph_location, ph_directno, ph_extension, ph_mobile_ext from tblPhone where ph_user matches '*John*' order by 1

but get the following error:

Error, sql.error: ('37000', -3100, "[Microsoft][ODBC Microsoft Access 97 Driver] Syntax error (missing operator) in query expression 'ph_user matches '*John*''.")

Ideally, I would like to be able to search against a table for ph_user using their first name only and return all the other fields. Would appreciate any advice. We're using Zope 1.10.3

Thanks

Jenny

_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )