criteria prompt and like function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

Is there a way of including the like function into a prompt. For example if
i wanted to search by customer name an input box would appear asking "Please
enter a customer name". To search for 'australia' is there a way i can search
for like 'aust' from the input box.

When i try to input *aust* it displays no records, but if i run the query
design and change the criteria from a prompt to *aust* it shows the records.

Also is there a way to make the input box display a list of all of the
possible selections. E.g. display a choice of all of the customer names in a
list, select it and press ok? (This would avoid the above problem!)

Thanks in advance
 
You can set up your criteria like this:
Like [Enter a customer name] & "*"
That works because it uses the Like operator and appends the wildcard to
whatever the user enters.

Parameters are very limited. You cannot use a drop-down list. You cannot
range-check the entry. If you have several, the boxes popping up one after
another are annoying and you cannot back-track to a previous one if you made
a mistake. Even worse, you cannot efficiently just leave one blank and not
bother about the criteria on that field.

There are workarounds for some of these, e.g. opening a form where the user
can enter criteria, and setting the criteria in the query to:
Forms!Form1!Combo1

In general, though, I find it much easier to create the SQL statement
dynamically. Just build the WhereCondition for the OpenReport from the
non-blank boxes on a form, or build the entire SQL statement as a string and
assign it to the RecordSource of the form.
 
Bill Gates said:
Hello,

Is there a way of including the like function into a prompt. For example if
i wanted to search by customer name an input box would appear asking "Please
enter a customer name". To search for 'australia' is there a way i can search
for like 'aust' from the input box.

When i try to input *aust* it displays no records, but if i run the query
design and change the criteria from a prompt to *aust* it shows the records.

Also is there a way to make the input box display a list of all of the
possible selections. E.g. display a choice of all of the customer names in a
list, select it and press ok? (This would avoid the above problem!)

Thanks in advance
Click to expand...

You have obviously made an error in your query, but without seeing the SQL
it's impossible to say what.

The following does exactly what you want in Northwind:

SELECT Customers.CompanyName
FROM Customers
WHERE (((Customers.CompanyName) Like [Enter Name]));

No, you can't display a list in a query's parameter dialog box. To do that,
you would have to build a pop-up form which displayed the selection criteria
in the way you wanted, and have the form construct and open the query.
 
Back
Top