filter by form

[kenjones]

I'm using filter by form and have an issue with email addresses

For example if I search on - "kenjones@somewhere.com" that's OK

But if I search on - "ken.jones@somewhere.com" I get the following error message;

"The expression you entered contains invalid syntax

You may have entered an operand without an operator"

Or in other words I cant run the filter if there is a period [.] before the [@]

Any thoughts?

 

[jzwp22]

I assume that there is some code/macros that run when you conduct the search. Could you provide that code/macro?

 

[kenjones]

There is no special code or macro as far as I'm aware

I'm just using the Access filter by form buttons that are on the toolbar. Click the first and you see the form with all fields blank. Enter the value you want to search/filter for in the appropriate field and then press the second button that runs the filter/search

This works OK for the other fields on the form and will work for an email address that does not have the [.] before the [@]

 

[jzwp22]

I created an example database, and the only time I could generate the error you discussed is when I intentionally misspell the e-mail address such that it is not an exact match to a record in the database AND I do not enclose the string in double quotes.

If I type an existing e-mail address correctly AND do not enclose the string with double quotes, the search will work just fine.

If I enclose an incorrectly typed e-mail address in double quotes, the search runs without any errors but of course does not return any results.

If I enclose a search string in double quotes and include the (*) wildcards, Access automatically searches using the LIKE operator and the search runs as expected.

 

[kenjones]

The double quotes work

So

"kenjones@somewhere.com" - OK

kenjones@somewhere.com - OK

"ken.jones@somewhere.com" - OK

ken.jones@somewhere.com - Error message

Why do you think the double quotes make a difference?

And can I alter any of the properties of the field so I dont have to use the double quotes?

Is it something to do with special charcters? Is there a way of setting the properties of a field to allow the use of special characters?

And lastly thank you very much for your help so far

 

[jzwp22]

The double quotes work because you are searching a text field. In Access as well as many other databases, text data seaches have to be enclosed in some kind of delimiter. In Access, dates have to be enclosed by # signs. Numerical values generally do not need delimiters.