Using wildcards in queries

[stuart_adair]

Hello all,

i've got a query running on a database that asks for a user input when its run in the "Client" field. Users can enter details and the query extracts data for that specific client.

However, some users want to be able to extract data for all clients. How do I tell Access to extract all data ? Have tried the obvious wildcards such as *.* and * but they dont work.

Thanks
Stu

 

[georgedwilkinson]

Yes, they wouldn't work unless you change the where clause of the query to use "like" instead of "=".

 

[stuart_adair]

Thanks for the quick reply. I've just had a quick look at the "Like" clause on the internet and wanted to check my understanding..

If I make a change to the criteria in the client field from =[Please enter client name] to Like [Please enter client name].
Users can enter Fred Smith to see the details of client Fred Smith
To see all data they would enter A-Z

Thanks
Stu

 

[georgedwilkinson]

No, that is not how it works (in this case).

In your scenario, users could enter Fred Smith and get only clients who's names are exactly Fred Smith. Alternatively, they could put in standard Access wild cards (* and _).

For instance, if they entered "*" they would get all clients. If they entered "Fred*" they would get all clients whose first name started with Fred. "*red*" would give them all the Freds and any other client with the substring red anywhere in their name.

 

[stuart_adair]

Thats great then. I'll have a play when I get to work tomorrow and let you know how I get on. Thanks for your help

 

[stuart_adair]

Hello again,

Finally got some time today to check the solution offered and put this into the criteria field

Like [Please enter client name]

Which works a treat when I run the query, entering * brings up all data.

But (And you knew there was a but coming!!!) - I have a crosstab that runs from this data which is now returning the message "Microsoft Jet Database does not recognise [Please enter client name]"

Any advice?
Thanks
Stu

 

[DCrake]

Right

First you need to create a public variable in a standard module

Public strCustomer As String

Next Create a public Function

Public Function GetCustomer() As String
   GetCutomer = strCustomer
End Function

Then design the report that previously used a parameter and delete that and replace with

Like "*" & GetCustomer() & "*"

Save the query

Finally in the form that was currently firing off the query and have a text box for the user to select a customer

When the user types in a full or partial customer name use the after update to pass the resonse to the public variable

Sub TxtCustomer_AfterUpdate()
  StrCustomer = Me.TxtCustomer
End Sub

There is a more detailed explanation of this Here.

David