how to combine fields on a form in a query as criteria?

H

Henro

I saw once code for a module that would allow the use of '*' as any in a
criterium.
I want to make a form that has 6 fields. I want people to be able to enter
values in those fields that will work as criteria in a query that will feed
a report. I know how to do that but I do not know how to use an 'any'
operator. I think above mentioned code would be (at least a big part) the
key to this challenge but I need some more info.

What do I exactly want?

I want to make an unbound form that has a number of fields (probably six or
so).
The values entered by the user on the form will be used as criteria in a
query:

SELECT [FieldOne]
FROM Query
WHERE ((([FieldOne])=[Forms]![SearchForm]![FieldOne]));

But, and here comes the trick: I want one of three things to happen:
1) If a value on the form is blank that should be read as 'any'
2) users should have the ability to choose f.e. * for any
3) The fields on the form would be comboboxes where multiple values can be
picked. The chosen values in the comboboxes will be then used in the query:
SELECT [FieldOne]
FROM Query
WHERE ((([FieldOne])=[Forms]![SearchForm]![FieldOne] AND second value in
combobox AND third value in combobox)); where as again no value entered
should mean any or '*' should be available as a choice that means any.

Then a user would be able to find records using a number of (combined)
criteria

I would prefer solution no 3 but I would be happy with any of the three
options mentioned above.

Any suggestions as to where to look?

TIA! Henro
 
R

Rick B

You don't need to do much of what you mentioned. To allow blank to pull
all, simply put your query criteria as...
like [EnterValue] & "*"


If the user enters "Smit" for example, the query would look like...
Like "Smit*"


If they leave the entry blank, all records would be pulled because the query
would show...
Like "*"


Hope that helps,
Rick B



I saw once code for a module that would allow the use of '*' as any in a
criterium.
I want to make a form that has 6 fields. I want people to be able to enter
values in those fields that will work as criteria in a query that will feed
a report. I know how to do that but I do not know how to use an 'any'
operator. I think above mentioned code would be (at least a big part) the
key to this challenge but I need some more info.

What do I exactly want?

I want to make an unbound form that has a number of fields (probably six or
so).
The values entered by the user on the form will be used as criteria in a
query:

SELECT [FieldOne]
FROM Query
WHERE ((([FieldOne])=[Forms]![SearchForm]![FieldOne]));

But, and here comes the trick: I want one of three things to happen:
1) If a value on the form is blank that should be read as 'any'
2) users should have the ability to choose f.e. * for any
3) The fields on the form would be comboboxes where multiple values can be
picked. The chosen values in the comboboxes will be then used in the query:
SELECT [FieldOne]
FROM Query
WHERE ((([FieldOne])=[Forms]![SearchForm]![FieldOne] AND second value in
combobox AND third value in combobox)); where as again no value entered
should mean any or '*' should be available as a choice that means any.

Then a user would be able to find records using a number of (combined)
criteria

I would prefer solution no 3 but I would be happy with any of the three
options mentioned above.

Any suggestions as to where to look?

TIA! Henro
 
Top