Access 2010 query help

'is null" is SQL syntax. In Access expressions, use function isnull:

iif (isnull(expression), true-part, false-part)

or

iif(nz(expression,'')="", true-part,false-part)

Further "true-part" and "false-part" should be expressions resulting in some value, so maybe Like "*" & [Forms]![SearchArmature]![txtSlots] & "*" needs to go in quotes - depending on what you expect as the result.

Another method is:

Where (Slots = [Forms]![SearchArmature]![txtSlots] OR [Forms]![SearchArmature]![txtSlots] Is Null)
AND (fld2 = [Forms]![SearchArmature]![fld2]  OR [Forms]![SearchArmature]![fld2]  Is Null)
AND (fld3 = [Forms]![SearchArmature]![fld3]  OR [Forms]![SearchArmature]![fld3]  Is Null)
AND ....

Using this method, parentheses are critical since the expression contains both AND and OR operators.

I assume you are attempting to implement this within a saved query?

And that you want to filter a recordset based on a value in a field [Slots] as it relates to the entry in your txtSlots control on your form.

Normally, I would write the query criteria as:

WHERE [Slots] = IIF([Forms]![SearchArmature]![txtSlots] & "" = "", [Slots], [Forms]![SearchArmature]![txtSlots])

This compares [Slots] to [Slots] if the field on your form is blank or NULL, but compares [Slots] to the value in the textbox if it is not blank or null

Another option would be:

WHERE [Forms]![SearchArmature]![txtSlots] & "" = ""
OR [Slots] = [Forms]![SearchArmature]![txtSlots]




The

Strange... I'd expect Access to bulk if Is Null is illegal... in fact it changes is null to Is Null.

Either way... IIF fails as well.  Wrapping the Like statement in quotes yields a too complex error.

Is this possibly beyond Access capability?

The Like statement by itself works fine (no data shows everything).

At the end of the day what I am trying to do is create a Search Form that will contain 20 fields.  I wish the user to enter any data desired... thus some fields may be empty.

the "Like" will not work here..

you can not use "=" and "Like" in the same criteria

did you try fyed post at http:#a39711382 ?

capricorn1:  Yes... doesn't return anything whether I leave it blank or not.  Troubleshooting it now.

Given that you are defining a search form, I would recommend that you build your criteria via VBA, using a command button to look at each of the 20 controls you want to filter on and build the critiera string based on the values in the controls.   If there is no value, then ignore that field in your criteria string.

Check out Helen_Feedema's response to a similar question:

ok, I was wrong. I now created trivial database, and put ehre query with criteria exactly as you posted in your quesstion, and there was no error. It did not return any data obviously, since I didn't have any, but there was no syntax errors either. So how is yours not working? any error messages?

The sample database is attached. Open form1, then run qiery1.
db.mdb

vadimrapp1:  My query doesn't error... it only returns data if it the prompt.  So, if I enter a 22 then I get all 22 slots.  If I leave it blank I get nothing.

what do you want it to return if the field txtSlots is blank? if all records, then the sql for the query can be

SELECT Table1.thefield
FROM Table1
WHERE
thefield like '*' & nz([Forms]![SearchArmature]![txtSlots]) & '*'

If txtSlots is blank I want all records.  So basically if I show all 20 fields the user can pick and chose which info to show and then they'll see all records that satisfy the query.  

So, I'm just starting with 1 field and trying to get it to return either all records or only records that match txtSlots.  Thus if txtSlots is empty (Null?) then all records come back.

The problem with:

thefield like '*' & nz([Forms]![SearchArmature]![txtSlots]) & '*'

is that if I enter a 2 then I get anything with a 2 in it.

If when criteria is entered you want strict match, then

SELECT Table1.thefield
FROM Table1
WHERE nz([Forms]![SearchArmature]![txtSlots].[text],""))="" OR
           thefield=[Forms]![SearchArmature]![txtSlots].[text]

Try

Did you try my suggestion?  I showed you how to combine the criteria for multiple fields.  The example ignores empty criteria fields and AND's the ones with values.

I should warn you though, Access will rewrite your selection criteria if you switch to QBE view before saving.  I always save queries with complex criteria in SQL view so the QBE won't mess up the format.

With 20 fields I might build the criteria on the fly rather than creating a saved querydef.

Dim sBaseQuery as String
Dim sCriteria as String
sBaseQuery = "Select ... From ..."
sCriteria = ""
If Me.fld1 & "" = "" Then
else
    sCriteria = " Where fld1 = " & Me.fld1
End If
If Me.fld2 & "" = "" then
Else
    If sCriteria = "" Then
        sCriteria = "Where fld2 = " & Me.fld2
    Else
        sCriteria = sCriteria & " fld2 = " & Me.fld2
    End If
End If
If Me.fld3 & "" = "" Then
    If sCriteria = "" Then
        sCriteria = "Where fld3 = " & Me.fld3
    Else
        sCriteria = sCriteria & " fld3 = " & Me.fld3
    End If
End If
If ....

Select allOpen in new window

Keep in mind that dates need to be enclosed in #'s and text fields need to be enclosed in double quotes.  Numeric values are not enclosed in delimiters.

Correction to my post:

I noticed a typo in my pseudo code as I posted.  I forgot to include the "AND" operators.

so
sCriteria = sCriteria & " fld2 = " & Me.fld2
should be
sCriteria = sCriteria & " AND fld2 = " & Me.fld2

@classnet

When you reply to someones post, please indicate who you are replying to, and it would make it even better if you could include the hyperlink to the specific response you are replying too.

Trying to write all of the possible combinations of input to over twenty fields in a search form into a saved query will be quite challenging.  If you are going to do this via a search form, filtering on multiple fields based on user input, and are not going to simply use the FilterByForm method, I strongly recommend that you take a look at Helen_Feedema's examples in her response to a similar question.  She has some really good examples that apply to multiple criteria as you indicate you intend to do.

That did it!  I am actaully, in the data view, entering
Like IIf(Nz([forms]![SearchArmature]![txtPromptName]),[forms]![SearchArmature]![PromptName],"*")
In the criteria field

---