Query criteria based on Form TextBox value

[stildawn]

HI All



I have a form called Add/Edit AFR, with a textbox called Text309.

I have a Query which is basically a mirror of my main table, but in field "FWO Number" I have this expression in the criteria:

IIf(IsNull([Forms]![Add/Edit AFR]![Text309]),"*","Like "*"" & [Forms]![Add/Edit AFR]![Text309] & "*")

However this doesn't seem to be working at all.

I used to have just: "Like "*"" & [Forms]![Add/Edit AFR]![Text309] & "*"

This one worked, however I noticed that it if Text309 was blank, it would the query would exclude all the records that didnt have an FWO Number (empty fields). This is not ideal if the Text309 is empty it should show every single record in the table.

So I tried the IIF expression above but now no matter what I put in Text309 nothing comes up at all.

If should be checking if Text309 is blank, if blank it should be putting "*" into the criteria which would bring up ever record (or so I'm told), if its not blank then it should be performing the Like *Text309.Value* criteria.

Any ideas?

Thanks

[stildawn]

Thinking about this, if this IIF statement above cant work, is there a way via VBA in the actual FORM to simply populate the criteria field on the Query if Text309 is entered?

So something like this in a command button on the Add/Edit Form: (I'm making this code up by the way)

If Me.Text309 Is Null Then
Else
Query[SearchQ]![FWO Number]![Criteria] = "Like "*"" & [Forms]![Add/Edit AFR]![Text309] & "*"
End If

So the command button would populate the criteria on the query only if Text309 had value.

???

Thanks

[Bob Fitz]

Try:
IIf(IsNull([Forms]![Add/Edit AFR]![Text309]),"","Like "*"" & [Forms]![Add/Edit AFR]![Text309] & "*")

[stildawn]

I tried that.

When empty I get no results, and when I enter something I get an expression error. I entered "11" which is the starting number for all FWO numbers??

Is there a way to set query field criteria via VBA like my made up example? As that would work better I think.

[stildawn]

Gah this is annoying. First I experimented with criteria to get a all results shown criteria. This worked for that: Is Null Or Like "*"

This included everything in my table which is great. So then I tried putting that into the IIF statement, I tried all these versions:

IIf(IsNull([Forms]![Add/Edit AFR]![Text309]),Is Null Or Like "*",Like "*" & [Forms]![Add/Edit AFR]![Text309] & "*")
IIf(IsNull([Forms]![Add/Edit AFR]![Text309]),"Is Null Or Like "*"","Like "*" & [Forms]![Add/Edit AFR]![Text309] & "*"")
IIf(IsNull([Forms]![Add/Edit AFR]![Text309]),"Is Null Or Like " & ""*"","Like " & ""*"" & [Forms]![Add/Edit AFR]![Text309] & ""*"")

None of them worked, if Text309 was blank it came up with expression error, if it had "11" in it it just didnt show anything (even though every single FWO number has 11 in it).

I know that: Is Null Or Like "*"

Works by itself in the criteria, and that: Like "*" & [Forms]![Add/Edit AFR]![Text309] & "*"

Works by itself, but I just can't put the two together in a IF statement???

[Bob Fitz]

Have you tried:
IIf(([Forms]![Add/Edit AFR]![Text309]) Is Null,"","Like "*"" & [Forms]![Add/Edit AFR]![Text309] & "*")

[stildawn]

Just tried that one now.

Blank results in no records shown
"11" results in expression error (the invalid or to complex error)

[Bob Fitz]

Maybe:
IIf(([Forms]![Add/Edit AFR]![Text309]) Is Null,"","Like "*" & [Forms]![Add/Edit AFR]![Text309] & "*")

[stildawn]

That one came up with a error when entering the criteria

"expression is an invalid string" it wont let me save it like that.