Find form

[Guest]

I am designing a find form for my database. The user will fill in whichever
fields he needs to search by, then a query will open up the table,
restricting the results to those that fit the fields. If a field is empty,
then that one will select all records for that 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

 

[fredg]

I am designing a find form for my database. The user will fill in whichever
fields he needs to search by, then a query will open up the table,
restricting the results to those that fit the fields. If a field is empty,
then that one will select all records for that 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

Several problems.
1) Width is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

2) After you change that Field/Control name change the query criteria
to:
Like IIf(IsNull([Forms]![FormFind]![TheWidth]),"*",
[Forms]![FormFind]![TheWidth])

Note I've changed Width to TheWidth.

3) Simply create a form using the query as it's record source. Then
instead of running the query, open the form:
DoCmd.OpenForm "FormName"
The current form must be open when you open the query display form.

 

[Guest]

Thank you very much, fred. You have solved my problem for me.

I am designing a find form for my database. The user will fill in whichever
fields he needs to search by, then a query will open up the table,
restricting the results to those that fit the fields. If a field is empty,
then that one will select all records for that 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

Several problems.
1) Width is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

2) After you change that Field/Control name change the query criteria
to:
Like IIf(IsNull([Forms]![FormFind]![TheWidth]),"*",
[Forms]![FormFind]![TheWidth])

Note I've changed Width to TheWidth.

3) Simply create a form using the query as it's record source. Then
instead of running the query, open the form:
DoCmd.OpenForm "FormName"
The current form must be open when you open the query display form.

 

[Guest]

fred,
I have now set up the form and query the way you showed me. The query works
fine when I change into datasheet view and manually enter in all the
parameters, but when I hit the search button on my form (which opens up
another form with a sub form in it designed to look like a table in datasheet
view), it displays all the records in the table, and doesn't filter any out.

I am designing a find form for my database. The user will fill in whichever
fields he needs to search by, then a query will open up the table,
restricting the results to those that fit the fields. If a field is empty,
then that one will select all records for that 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

Several problems.
1) Width is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

2) After you change that Field/Control name change the query criteria
to:
Like IIf(IsNull([Forms]![FormFind]![TheWidth]),"*",
[Forms]![FormFind]![TheWidth])

Note I've changed Width to TheWidth.

3) Simply create a form using the query as it's record source. Then
instead of running the query, open the form:
DoCmd.OpenForm "FormName"
The current form must be open when you open the query display form.