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

  • Thread starter Thread starter Henro
  • Start date Start date
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
 
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
 
Back
Top