Access Form Allowing for Blank Fields and Query Question

[jfg863]

I have a question to do with having 4 criteria (4 separate textboxes in a form) that I would like to tie to my query. If I add the 4 fields into the criteria area in the query (such as [Forms]![ValidationForm1_3]![Field]) and I place information in each of the 4 fields in the form, the results comes back as expected.



But what if I only want to query for one of the 4 fields and I leave the other 3 fields blank on the form but keep them in the criteria in the query, then no results will come back.

Is there a way I can allow some of the fields to be blank while also having the option to put all of the data in for each field if I want (meaning, without having to take out parts of the query every time, but instead allowing me to click my macro on my form, regardless of blank fields or not, and it coming back with what I wanted.

For example, say I have 4 fields (field1, field2, field3, field4) on my form as textboxes where you can enter information to query the database.
I then put "Cat" in field1, nothing in field2, "55" in field3, and nothing in field4. The query would then come back with all the records that have Cat for field1 and 55 for field3 (these are just made up examples).

What happens now though is if I leave one field blank on the form it comes back with no results. I have tried a few iif statements like for example:
=IIf(IsNull([Forms]![Form1]![Field2])=True,"",[Forms]![Form1]![Field2]) is one of them for example which I put in the criteria area.

Hopefully This explains it enough that someone understands what I'm trying to do? I appreciate any help, hopefully someone has done this before.

[June7]

A method for text (and I think date/time) fields is to use LIKE operator and wildcard. Number field requires different approach.

LIKE [Forms]![ValidationForm1_3]![Field] & "*"

Is it possible that some records will not have data in these fields? If you still want those records, need to handle the Nulls.

LIKE [Forms]![ValidationForm1_3]![Field] & "*" OR Is Null

[orange]

You probably have an AND condition between each of the criteria. You could try changing to OR and see if that gives you what you want.
You may get some ideas from this free video.