Using a like clause in a filter ?

[AlexT.]

Folks

I have a form where I programacticaly apply a fillter based on some
input by the user - this works just fine so far.

I would now try to construct a filter containing a LIKE clause
([MY_FIELD] LIKE "aa") and in doesn't seem to work.

Is this "legal" and if so any reason why it would not work ?

Thanks & regards

--alexT

 

[Douglas J. Steele]

Yes, it's legal, but your example is no different than if it were
([MY_FIELD] = "aa") (except that it'll be less efficient).

If your intent is to return only those rows where the content of MY_FIELD
starts with aa, you need

([MY_FIELD] LIKE "aa*")

If your intent is to return only those rows where the content of MY_FIELD
ends with aa, you need

([MY_FIELD] LIKE "*aa")

If your intent is to return only those rows where the content of MY_FIELD
contains aa anywhere in the string, you need

([MY_FIELD] LIKE "*aa*")

Note that if you're using ADO instead of DAO, you need to use % instead of
*.

 

[AlexT.]

Yes, it's legal, but your example is no different than if it were
([MY_FIELD] = "aa") (except that it'll be less efficient).

good point about my lousy syntax

Thanks