multiple field query

[Floyd]

My query prompts for values from multiple fields, though it displays ALL
records that contain one or more of the values entered. If three values are
entered I want it to display only the records that contain all 3 values, and
the same applies I f I enter 1,2,4 or 5 values. The query prompts for 5
values to be entered, though I may not require to use them all, so how do I
get it to ignore a null value if I choose not to use that field?

 

[John Vinson]

My query prompts for values from multiple fields, though it displays ALL
records that contain one or more of the values entered. If three values are
entered I want it to display only the records that contain all 3 values, and
the same applies I f I enter 1,2,4 or 5 values. The query prompts for 5
values to be entered, though I may not require to use them all, so how do I
get it to ignore a null value if I choose not to use that field?

Use criteria such as

([Fieldname] = [Prompt for first field:] OR [Prompt for first field:]
IS NULL)

using AND to connect these five composite criteria.

Access will make an utter HASH of the query grid when you do this -
it's simplest to create these criteria in SQL view.

You can avoid the whole problem by using a Form to do your searching,
using the Query By Form technique.

John W. Vinson[MVP]

 

[Floyd]

Thanx John. I found this works, seems I have to place it in all fields when I
first create the query, rather than add further field prompts later as the
later ones don't work properly. Anyhow, it's all good in the end. I will have
a crack at that query by form. One thing that I would really like to do there
is use the calendar control for "Between [Start Date] and [End Date]" value
entry. Any Ideas?
Thanx again for your help so far. Hard to beleive I got such a quick response,
Cheers.

My query prompts for values from multiple fields, though it displays ALL
records that contain one or more of the values entered. If three values are
entered I want it to display only the records that contain all 3 values, and
the same applies I f I enter 1,2,4 or 5 values. The query prompts for 5
values to be entered, though I may not require to use them all, so how do I
get it to ignore a null value if I choose not to use that field?

Use criteria such as

([Fieldname] = [Prompt for first field:] OR [Prompt for first field:]
IS NULL)

using AND to connect these five composite criteria.

Access will make an utter HASH of the query grid when you do this -
it's simplest to create these criteria in SQL view.

You can avoid the whole problem by using a Form to do your searching,
using the Query By Form technique.

John W. Vinson[MVP]

 

[John Vinson]

Thanx John. I found this works, seems I have to place it in all fields when I
first create the query, rather than add further field prompts later as the
later ones don't work properly.

Of course they work properly - if you do it right. Again, it's easiest
to work in SQL view and you do need to keep track of parenthesis
nesting - which Access will happily mess up for you!

Anyhow, it's all good in the end. I will have
a crack at that query by form. One thing that I would really like to do there
is use the calendar control for "Between [Start Date] and [End Date]" value
entry. Any Ideas?

That would require *two* Calendar controls, no?

This is getting sufficiently complex that you might want to consider
using an unbound Form (with textboxes, combos, calendar controls,
etc.) with VBA code to parse through the form and build a SQL string,
using only non-NULL controls.

John W. Vinson[MVP]