Search Form

[Guest]

I am designing a search request form. If a field is blank, then all records
for this field will be displayed, but otherwise it will filter according to
the contents of the field. When there is a value in the
field, it works fine, but when there isn't, it doesn't show any records. The
actual code that I am using is this:
IIf([Forms]![FormFind]![width]=Null,Like "*",[Forms]![FormFind]![width])
Also, can you please tell me how to go from my search form, to the query,
and use this to open another form, showing only the selected records?
Thank you very much

 

[John Vinson]

I am designing a search request form. If a field is blank, then all records
for this field will be displayed, but otherwise it will filter according to
the contents of the field. When there is a value in the
field, it works fine, but when there isn't, it doesn't show any records. The
actual code that I am using is this:
IIf([Forms]![FormFind]![width]=Null,Like "*",[Forms]![FormFind]![width])
Also, can you please tell me how to go from my search form, to the query,
and use this to open another form, showing only the selected records?
Thank you very much

Well, you don't need to open the Query datasheet at all, and in fact
should not do so. Instead, simply base the second form on the Query as
its recordsource, and put a command button (the wizard will help here)
on the search form which opens the second form directly.

Your IIF isn't working for a couple of reasons. For one, nothing is
equal to NULL (or unequal either!); any expression involving NULL is
neither true nor false, it's NULL (but is treated as if it were
false). Secondly, you can't pass *operators* such as LIKE in an
expression, only actual values.

Instead, use a criterion of

=[Forms]![FormFind]![width] OR [Forms]![FormFind]![width] IS NULL

The IS NULL syntax is the SQL method of checking for null values
(rather than = NULL).

If you open this query in design view you'll see that the query
optimizer makes it look VERY wierd, especially if you have multiple
criteria fields. It may be preferable to either write VBA code to
parse through the form building a SQL string, or - and this may be
simplest of all - just open a form based on the table and use the
builtin Query By Form icon on the toolbar.

John W. Vinson[MVP]

 

[Guest]

Thank you very much for your help. I understand now how to open the second
form, but I can't find the Query By Form button on my toolbar.

Your criterion helped a bit, but it doesn't select the records when there is
a value in the search field.
Thank you

I am designing a search request form. If a field is blank, then all records
for this field will be displayed, but otherwise it will filter according to
the contents of the field. When there is a value in the
field, it works fine, but when there isn't, it doesn't show any records. The
actual code that I am using is this:
IIf([Forms]![FormFind]![width]=Null,Like "*",[Forms]![FormFind]![width])
Also, can you please tell me how to go from my search form, to the query,
and use this to open another form, showing only the selected records?
Thank you very much

Well, you don't need to open the Query datasheet at all, and in fact
should not do so. Instead, simply base the second form on the Query as
its recordsource, and put a command button (the wizard will help here)
on the search form which opens the second form directly.

Your IIF isn't working for a couple of reasons. For one, nothing is
equal to NULL (or unequal either!); any expression involving NULL is
neither true nor false, it's NULL (but is treated as if it were
false). Secondly, you can't pass *operators* such as LIKE in an
expression, only actual values.

Instead, use a criterion of

=[Forms]![FormFind]![width] OR [Forms]![FormFind]![width] IS NULL

The IS NULL syntax is the SQL method of checking for null values
(rather than = NULL).

If you open this query in design view you'll see that the query
optimizer makes it look VERY wierd, especially if you have multiple
criteria fields. It may be preferable to either write VBA code to
parse through the form building a SQL string, or - and this may be
simplest of all - just open a form based on the table and use the
builtin Query By Form icon on the toolbar.

John W. Vinson[MVP]

 

[John Vinson]

Thank you very much for your help. I understand now how to open the second
form, but I can't find the Query By Form button on my toolbar.

Sorry... Filter by Form. Looks like a funnel next to a little form
icon; it's the 15th icon from the left in the A2003 default form
toolbar.

Your criterion helped a bit, but it doesn't select the records when there is
a value in the search field.

Please post the actual SQL of your query, and indicate the value in
the form control, and the datatype and typical values of the table
field you're searching.

John W. Vinson[MVP]

 

[Guest]

The code I am now using is this:
Like
IIf(IsNull([Forms]![FormFind]![sheet_width]),"*",[Forms]![FormFind]![sheet_width])
I have similar criterion for each of 5 other fields in the query, and 2 of
them are number fields, while the others are text fields (including a drop
down box).

The only trouble I am now having is that when I have the form open, the
query doesn't seem to work; when I don't have it open and have to manually
input the fields, it does work.

Thanks for helping me!

 

[John Vinson]

The code I am now using is this:
Like
IIf(IsNull([Forms]![FormFind]![sheet_width]),"*",[Forms]![FormFind]![sheet_width])

This of course bears no resemblance to what I posted. Did you try it?

I have similar criterion for each of 5 other fields in the query, and 2 of
them are number fields, while the others are text fields (including a drop
down box).

A wildcard criterion won't work (well) for number fields. The OR ...
IS NULL will work fine. The combo box will find the value of the
Combo's Bound Column (which might not be the visible value) - make
sure you're searching the correct field with the correct value!

The only trouble I am now having is that when I have the form open, the
query doesn't seem to work; when I don't have it open and have to manually
input the fields, it does work.

In what way does it "not work"? No results? Wrong results? Finds all
records?

John W. Vinson[MVP]

 

[Guest]

I tried your code, but it always displayed every single record in the table.

The query doesn't work because it just displays all the records in the table.

Once again, thanks a lot for your help

The code I am now using is this:
Like
IIf(IsNull([Forms]![FormFind]![sheet_width]),"*",[Forms]![FormFind]![sheet_width])

This of course bears no resemblance to what I posted. Did you try it?

I have similar criterion for each of 5 other fields in the query, and 2 of
them are number fields, while the others are text fields (including a drop
down box).

A wildcard criterion won't work (well) for number fields. The OR ...
IS NULL will work fine. The combo box will find the value of the
Combo's Bound Column (which might not be the visible value) - make
sure you're searching the correct field with the correct value!

The only trouble I am now having is that when I have the form open, the
query doesn't seem to work; when I don't have it open and have to manually
input the fields, it does work.

In what way does it "not work"? No results? Wrong results? Finds all
records?

John W. Vinson[MVP]

 

[John Vinson]

I tried your code, but it always displayed every single record in the table.

The query doesn't work because it just displays all the records in the table.

Please post the full SQL view of the query.

John W. Vinson[MVP]

 

[Guest]

This is my SQL code that I am using:

SELECT Table_Sheets.sheet_width, Table_Sheets.sheet_height,
Table_Sheets.company, Table_Sheets.color, Table_Sheets.job_number
FROM Table_Sheets
WHERE (((Table_Sheets.sheet_width) Like
IIf(IsNull([Forms]![FormFind]![sheet_width]),"*",[Forms]![FormFind]![sheet_width]))
AND ((Table_Sheets.sheet_height) Like
IIf(IsNull([Forms]![FormFind]![sheet_height]),"*",[Forms]![FormFind]![sheet_height]))
AND ((Table_Sheets.company) Like
IIf(IsNull([Forms]![FormFind]![company]),"*",[Forms]![FormFind]![company]))
AND ((Table_Sheets.color) Like
IIf(IsNull([Forms]![FormFind]![color]),"*",[Forms]![FormFind]![color])) AND
((Table_Sheets.job_number) Like
IIf(IsNull([Forms]![FormFind]![job_number]),"*",[Forms]![FormFind]![job_number])));

 

[John Vinson]

This is my SQL code that I am using:

SELECT Table_Sheets.sheet_width, Table_Sheets.sheet_height,
Table_Sheets.company, Table_Sheets.color, Table_Sheets.job_number
FROM Table_Sheets
WHERE (((Table_Sheets.sheet_width) Like
IIf(IsNull([Forms]![FormFind]![sheet_width]),"*",[Forms]![FormFind]![sheet_width]))
AND ((Table_Sheets.sheet_height) Like
IIf(IsNull([Forms]![FormFind]![sheet_height]),"*",[Forms]![FormFind]![sheet_height]))
AND ((Table_Sheets.company) Like
IIf(IsNull([Forms]![FormFind]![company]),"*",[Forms]![FormFind]![company]))
AND ((Table_Sheets.color) Like
IIf(IsNull([Forms]![FormFind]![color]),"*",[Forms]![FormFind]![color])) AND
((Table_Sheets.job_number) Like
IIf(IsNull([Forms]![FormFind]![job_number]),"*",[Forms]![FormFind]![job_number])));

Hrm. What's the Recordsource of FormFind (it should be blank, no
record source)? What are the actual values in FormFind!sheet_width and
so on? What do you see if you type a value into one of the fields that
you know is never going to be valid?

John W. Vinson[MVP]