'select all' as option in a query?

My users need a query based on 3 criteria:

Criteria 1: A, B, C or all of them
Criteria 2: A, B, C or all of them
Criteria 3: 1, 2, 3 or all of them

I'v got no problem with the query if distinct values are selected (i.e.
A-C-2), but what's the best way to include the 'all of them' options?

I've made invisible fields on my form and used "*" for the string
criterias and this works, but how to do it with integers?

Thanks a lot!

Jerome

Jerome, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The article has a downloadable sample database that shows you how to build a
Filter string based on the controls where you did enter criteria, and ignore
the boxes where you entered none.

It also explains that the criteria:
Like "*"
does not return all records: those that are null (field left blank) are not
returned.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jerome" wrote in message
...
My users need a query based on 3 criteria:

Criteria 1: A, B, C or all of them
Criteria 2: A, B, C or all of them
Criteria 3: 1, 2, 3 or all of them

I'v got no problem with the query if distinct values are selected (i.e.
A-C-2), but what's the best way to include the 'all of them' options?

I've made invisible fields on my form and used "*" for the string
criterias and this works, but how to do it with integers?

Thanks a lot!

Jerome

For any field that are going to have criteria, to remove potential null
fields that wouldn't normally be picked up by a criteria, concatenate a zero
length string at the back e.g. FIELD1 & "" this way for any records that are
null they'll become zero length string and be picked up by the criteria is
the form combox has *, in addition you can put in the your queries criteria
LIKE
IIF(isnull(FORMS!frmNAME!cboNAME.Column(boundolumn )),"*",FORMS!frmNAME!cboNAME.Column(boundolumn)).

HTH
KM

"Allen Browne" wrote:

Jerome, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The article has a downloadable sample database that shows you how to build a
Filter string based on the controls where you did enter criteria, and ignore
the boxes where you entered none.

It also explains that the criteria:
Like "*"
does not return all records: those that are null (field left blank) are not
returned.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jerome" wrote in message
...
My users need a query based on 3 criteria:

Criteria 1: A, B, C or all of them
Criteria 2: A, B, C or all of them
Criteria 3: 1, 2, 3 or all of them

I'v got no problem with the query if distinct values are selected (i.e.
A-C-2), but what's the best way to include the 'all of them' options?

I've made invisible fields on my form and used "*" for the string
criterias and this works, but how to do it with integers?

Thanks a lot!

Jerome

Kevin, the suggestion might be useful to get someone out of a tight corner,
but it is still less than ideal:

a) The field becomes read-only, so is not suitable for forms where you want
to edit the data.

b) Concatenating a zero-length string turns the field into Text, which
messes up criteria and sorting, and is likely to give problems with numeric
operations on the field (such as summing them.)

c) The approach is inefficient, since Access is unable to use any index on
the field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kevin McCartney" wrote in
message
...
For any field that are going to have criteria, to remove potential null
fields that wouldn't normally be picked up by a criteria, concatenate a
zero
length string at the back e.g. FIELD1 & "" this way for any records that
are
null they'll become zero length string and be picked up by the criteria is
the form combox has *, in addition you can put in the your queries
criteria
LIKE
IIF(isnull(FORMS!frmNAME!cboNAME.Column(boundolumn )),"*",FORMS!frmNAME!cboNAME.Column(boundolumn)).

HTH
KM

"Allen Browne" wrote:

Jerome, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The article has a downloadable sample database that shows you how to
build a
Filter string based on the controls where you did enter criteria, and
ignore
the boxes where you entered none.

It also explains that the criteria:
Like "*"
does not return all records: those that are null (field left blank) are
not
returned.

"Jerome" wrote in message
...
My users need a query based on 3 criteria:

Criteria 1: A, B, C or all of them
Criteria 2: A, B, C or all of them
Criteria 3: 1, 2, 3 or all of them

I'v got no problem with the query if distinct values are selected (i.e.
A-C-2), but what's the best way to include the 'all of them' options?

I've made invisible fields on my form and used "*" for the string
criterias and this works, but how to do it with integers?

Thanks a lot Allen!

Allen Browne wrote:
Jerome, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The article has a downloadable sample database that shows you how to build a
Filter string based on the controls where you did enter criteria, and ignore
the boxes where you entered none.

It also explains that the criteria:
Like "*"
does not return all records: those that are null (field left blank) are not
returned.

Hi Allen,

I really appreciate your method but I'm wondering if I can really use it
since you're working with filters and I need the query for a report?

How do I integrate your querystring into the SQL query of my report?
Or have I overlooked something?

Thanks a lot,

Jerome

Allen Browne wrote:
Jerome, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The article has a downloadable sample database that shows you how to build a
Filter string based on the controls where you did enter criteria, and ignore
the boxes where you entered none.

It also explains that the criteria:
Like "*"
does not return all records: those that are null (field left blank) are not
returned.

If the goal is a report, you can create the filter string, and use it as the
WhereCondition for OpenReport.

You leave the criteria out of the query, build the string, and then use it
like this:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

You won't be able to do that if the fields you are filtering on are not
actually in the report (e.g. if you select records from a period, and
aggregate them, so the report's RecordSource does not have the date field
you wish to filter on.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jerome" wrote in message
...

I really appreciate your method but I'm wondering if I can really use it
since you're working with filters and I need the query for a report?

How do I integrate your querystring into the SQL query of my report?
Or have I overlooked something?

Thanks a lot,

Jerome

Allen Browne wrote:
Jerome, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The article has a downloadable sample database that shows you how to
build a Filter string based on the controls where you did enter criteria,
and ignore the boxes where you entered none.

It also explains that the criteria:
Like "*"
does not return all records: those that are null (field left blank) are
not returned.

Thanks, I've now used the SQL query statement that you had presented as
an alternative. It works like a charm (I don't use dates in it) with my
report.

Thanks again!

Allen Browne wrote:
If the goal is a report, you can create the filter string, and use it as the
WhereCondition for OpenReport.

You leave the criteria out of the query, build the string, and then use it
like this:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

You won't be able to do that if the fields you are filtering on are not
actually in the report (e.g. if you select records from a period, and
aggregate them, so the report's RecordSource does not have the date field
you wish to filter on.)