Report query based on form selections

  • Thread starter Thread starter Bill Taylor
  • Start date Start date
B

Bill Taylor

I don't know how to set criteria from a form based on selections being
blank. for example I want the query's criteria for the Company field to be a
selection from the form but nothing if nothing is selected. Should I be
using the IIF. and if so what is the syntax.
 
Bill,

Assuming your form is calld Form1 and the control for Company selection on
it is called Ctrl1, then change your query criterion to:

Like Forms!Form1!Ctrl1 & "*"

This will produce the following behaviour:
- if the control is left blank, all records will be returned
- if you type/select a full Company name, records will be filered on it
- if you type just part of the beginning of a Company name, e.g. "Acme",
records for both "Acme Investments" and "Acme Development" will be returned.

If you change the criterio to:\

Like "*" & Forms!Form1!Ctrl1 & *

then you will be able to filter on partial name anywhere in the Company
name, not just the beginning. To extend the previous example, you would also
get records for "The Acme Company".

HTH,
Nikos
 
Yes, simple and effective. Thank you.

Nikos Yannacopoulos said:
Bill,

Assuming your form is calld Form1 and the control for Company selection on
it is called Ctrl1, then change your query criterion to:

Like Forms!Form1!Ctrl1 & "*"

This will produce the following behaviour:
- if the control is left blank, all records will be returned
- if you type/select a full Company name, records will be filered on it
- if you type just part of the beginning of a Company name, e.g. "Acme",
records for both "Acme Investments" and "Acme Development" will be returned.

If you change the criterio to:\

Like "*" & Forms!Form1!Ctrl1 & *

then you will be able to filter on partial name anywhere in the Company
name, not just the beginning. To extend the previous example, you would also
get records for "The Acme Company".

HTH,
Nikos


be
Click to expand...
 
Only one caution, which may not be applicable in your case.

If the field contains a null value (blank) then the record will not be returned.
Like "*" does not match fields with a null value, it will match those with a
zero-length string.
 
Back
Top