Multisearch form for a query in access

Question

I should make a query in Access that have 4 criteria. If I Run this query by the structure view of the query it works. Then I built a form to insert the criteria in 4 text boxes and get more easy the use of the query. I create the form using the tutorial on the official site of microsoft 1; i tried first the query with only one text box and one criterium and it works; when I use 4 text box, following the tutorial, it doesn't work. The criterium that I use for each field in the query is the follow:

Switch(Not IsNull([Forms]![frmRICmp]![cod]),[Forms]![frmRICmp]![cod])

I tried to use also

IIf(IsNull([Forms]![frmRICmp]![cod]), Like "*", [Forms]![frmRICmp]![cod])

but also in this case it doesn't work.

can someone tell me the right instrtuction to use in the query's criteria tnks

izzymo izzymo · Accepted Answer · 2015-02-16T09:47:02

So if I gather correctly you need to perform a multi search where if any of the boxes are null you would like to return all the values. and more than one text box can be used simultaneously. TO do this you have to amend do the following.

Amend the Query Field (Note i'm referring to field and not criteria) For the first Text Box Assuming name is COD and Field Name is also COD If the Current field name is COD insert another field with the same name and amend to

  [COD]=[Forms]![frmRICmp]![cod] OR [Forms]![frmRICmp]![cod] Is NULL
then in the criteria field use the following value
  TRUE

For the second Text Box Assuming name is COD2 and Field Name is also COD2 If the Current field name is COD2 insert another field with the same name and amend to

[COD2]=[Forms]![frmRICmp]![cod2] OR [Forms]![frmRICmp]![cod2] Is NULL
then in the criteria field use the following value
  TRUE

and continue the same process for all 4 text boxes.

Multisearch form for a query in access

1 Answers