Microsoft access query criteria using a form

Say your field name is [CustName],
use alias name of CustName1:[CustName]  in your query
In its criteria cell under it add:

IIF(fnCustName1()="<All>",[CustName1], fnCustName1())
In a module, add:
 
Function fnCustName1() As Variant
On Error GoTo 10
Dim Temp
Temp=Nz(Forms!MyFormName!txtCustName1,"<All>") ' you vave called the text box a in your question I guess

Msgbox Temp  ' remove this line after you make sure its value is being read correctly

fnCustName1=Temp
ExitFunction
10:
fnCustName1="<All>"
End Function

Also try changing

IIF(fnCustName1()="<All>",[CustName1], fnCustName1())

to

IIF(fnCustName1()="<All>",[CustName], fnCustName1())

One of them should work. Now, after you test it for your first text bos, build yoyr second, third, and fourth on in a similar mannaer. I will show the second one. Add them one at a time.

txtCustName2  ' on your form

Alias name of CustName2:[CustName]  in your query

IIF(fnCustName2()="<All>",[CustName2], fnCustName2())    'in your query under CustName2 alias name


Function fnCustName1() As Variant
On Error GoTo 10
Dim Temp
Temp=Nz(Forms!MyFormName!txtCustName1,"<All>")
fnCustName1=Temp
ExitFunction
10:
fnCustName1="<All>"
End Function

in a module.
 
Mike

Peter57r:

I get no records with your proposal.

Eghtebas:

You solution works...but partially.  The problem is not with your code.  Perhaps I need to clarify a little bit more.  The query field called "TopLevelPartNumber" comes from a field that has 10 characters.  Examples of data includes part number like 1111111-A01 and 1111111-A02.  So for example, if the user inputs 1111111-A01 in box "a", then only records with 1111111-A01 should show up.  However, I would also like the user to simply input 1111111 in box "a" and the query should return records that contain "1111111-A01" AND "1111111-A02".  I got your code working.  I think we are almost there.....................

Try:

Like IIF(fnCustName1()="<All>",[CustName1], iif(instr(fnCustName1(),"-")>0,fnCustName1() & "*",fnCustName1))

Where if your entry lacks - then it returns 1111111* which is bound to include both "1111111-A01" AND "1111111-A02".  

if you have a fix number of 11 characters always then try:

Like IIF(fnCustName1()="<All>",[CustName1], iif(len(fnCustName1()<11,fnCustName1() & "*",fnCustName1))

Then for entry like 1111111-B0 it wil return:

1111111-B00
1111111-B01
1111111-B04
1111111-B08
1111111-B09

Also try Like() with paranthesis,

Like(IIF(fnCustName1()="<All>",[CustName1], iif(len(fnCustName1()<11,fnCustName1() & "*",fnCustName1)))

Now things don't work at all.  I have attached screen shots of the code and the query with the criteria

You are missing Like at the start of the criteria. There are two options, try them both.

Use:

Like(IIF(....))

Or

Like IFF(...)

The word Like is a part of the syntax.

typo...    Like IIF(...)

BTW, when you want to add your second criteria, use Or option by entering it one row below the first one and so fort.

Like IIF(fnCustName1...)                                                                                                  Like "W*"
                                       Like IIF(fnCustName2=...)                                                         Like "W*"
                                                                                   Like IIF(fnCustName3=...)             Like "W*"

Here is my field: PartNumber1: Top Level Part Number

Here is the criteria below this field:    

Like IIf(fnPartNumber1()="<All>",[Top Level Part Number],IIf(InStr(fnPartNumber1(),"-")>0,fnPartNumber1() & "*",fnPartNumber1()))

I enter the following part number in my form "10A0988-A01" and I get the results I am looking for.  However, when I type in "10A0988", I get no results........

I will build something similar to what you have to debug it.

I tried your proposal with the len function below:

Like IIF(fnCustName1()="<All>",[CustName1], iif(len(fnCustName1()<11,fnCustName1() & "*",fnCustName1))

and I am not getting a "wrong number of arguments error.........also are you missing a () at the end

Ok thanks.  Sorry for being a pain.......

Like IIf(fnPartNumber1()="<All>",[Top Level Part Number],IIf(Left(fnPartNumber1(),7),fnPartNumber1() & "*",fnPartNumber1()))

This seems to be working.........

also try:

Like(IIf(fnPartNumber1()="<All>",[Top Level Part Number],fnPartNumber1() & "*")

It is much simpler.

Like(IIf(fnPartNumber1()="<All>",[Top Level Part Number],fnPartNumber1() & "*"))

) was missing at the end

I am continuing with my QC involving a second criteria box...