Open ended criteria (1 Viewer)

andysh

New member
Local time
Today, 02:29
Joined
Nov 26, 2020
Messages
5
Hi All,

I'm new to access and feeling my way in the dark a little.

I'm building a form for users to enter parameter criteria and it's coming along nicely. The form is such that a user can enter data for some or all parameters or any combination of them. All good.

Here comes the but...

In one field I need the user to be able to enter 'A11111111' and the search will return results for:

A11111111
A11111111#A
AAA@A11111111#A

The part I'm struggling with is the syntax for the wildcards at the start and end of the user entry especially the special characters. If it helps, the format will always be the same as the three examples, where 'A' is any letter, '1' is any number and the @ and # are as written.

The SQL for that part of the query is:

AND ((Table.PRODUCT = [Forms]![frm_SearchTransactions]![txtProduct]) OR ([Forms]![frm_SearchTransactions]![txtProduct] Is Null))

Any help or suggestions would be greatly appreciated.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:29
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

Not sure if it's what you want, but give this one a try.

SQL:
...AND ((Table.PRODUCT Like "*" & [Forms]![frm_SearchTransactions]![txtProduct] & "*") OR ([Forms]![frm_SearchTransactions]![txtProduct] Is Null))

Hope that helps...
 

andysh

New member
Local time
Today, 02:29
Joined
Nov 26, 2020
Messages
5
Thanks, DBguy

Tried that and it didn't work

Just tried again after closing the form and it works fine.

Lesson learned...close the forms before you try new stuff. Did say I was new to access :)
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:29
Joined
Oct 29, 2018
Messages
21,358
Thanks, DBguy

Tried that and it didn't work

Just tried again after closing the form and it works fine.

Lesson learned...close the forms before you try new stuff. Did say I was new to access :)
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom