filtering a form using three unbound fields

[S Himmelrich]

I would like to have three unbound fields all of which can be used in
a search, two of them using like statement. If there is nothing in
the unbound field for the search, then it should not be
included...what is the best practice using MS Access VB code to do
this filter.form, SQL, etc. etc.?

 

[JamesDeckert]

I do this with form, and the way I do it is to have a button that the user
clicks when the unbound fields are filled to the users satisfaction. The
unbound field and query button are on the form, with the data contained in a
subform. At that point you can use VBA to look at the fields and build an SQL
statement. Once the string is built, do the following.

DoCmd.Hourglass True
[Form_formA Subform].Filter = strQuery
[Form_formA Subform].FilterOn = True
DoCmd.Hourglass False

 

[S Himmelrich]

Thank you for your assistance, I understand what you are telling me,
but since I don't have the VBA experience this isn't an option.

I do this with form, and the way I do it is to have a button that the user
clicks when the unbound fields are filled to the users satisfaction. The
unbound field and query button are on the form, with the data contained in a
subform. At that point you can use VBA to look at the fields and build an SQL
statement. Once the string is built, do the following.

DoCmd.Hourglass True
[Form_formA Subform].Filter = strQuery
[Form_formA Subform].FilterOn = True
DoCmd.Hourglass False

I would like to have three unbound fields all of which can be used in
a search, two of them using like statement. If there is nothing in
the unbound field for the search, then it should not be
included...what is the best practice using MS Access VB code to do
this filter.form, SQL, etc. etc.?

 

[JamesDeckert]

I guess you'd have to do 'IIF' statements to check for Null fields on the
form, and if there is something in the field, then use this as a criteria in
the query.
It would be similar to

=IIf(IsNull([Forms]![CSR dg]![txtEarliestBatchNo]),[Diamond All].[BatchNo],[Forms]![CSR dg]![txtEarliestBatchNo]) And <=IIf(IsNull([Forms]![CSR dg]![txtLatestBatchNo]),[Diamond All].[BatchNo],[Forms]![CSR dg]![txtLatestBatchNo])

This is from a query I have, you should be able to see the concept here and
modify for your needs.
This line is in the Critereria row of the [BatchNo] field.
[txtEarliestBatchNo] and [txtLatestBatchNo] are fields on the form.

Thank you for your assistance, I understand what you are telling me,
but since I don't have the VBA experience this isn't an option.

I do this with form, and the way I do it is to have a button that the user
clicks when the unbound fields are filled to the users satisfaction. The
unbound field and query button are on the form, with the data contained in a
subform. At that point you can use VBA to look at the fields and build an SQL
statement. Once the string is built, do the following.

DoCmd.Hourglass True
[Form_formA Subform].Filter = strQuery
[Form_formA Subform].FilterOn = True
DoCmd.Hourglass False

I would like to have three unbound fields all of which can be used in
a search, two of them using like statement. If there is nothing in
the unbound field for the search, then it should not be
included...what is the best practice using MS Access VB code to do
this filter.form, SQL, etc. etc.?