Access 2010 query help

The intent is that if the user supplies something in the prompt that they get those records otherwise they get everything.

IIf([Forms]![SearchArmature]![txtSlots] Is Null,Like '*' & [Forms]![SearchArmature]![txtSlots] & '*',[Forms]![SearchArmature]![txtSlots])

and

IIf([Forms]![SearchArmature]![txtSlots] Is Null,Like "*" & [Forms]![SearchArmature]![txtSlots] & "*",[Forms]![SearchArmature]![txtSlots])

both fail.

Any thoughts?
classnetAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Vadim RappConnect With a Mentor Commented:
or:

SELECT Table1.thefield
FROM Table1
WHERE (((Table1.thefield) Like IIf([Forms]![SearchArmature]![txtSlots].[text]="",'*',[Forms]![SearchArmature]![txtSlots].[text])));
0
 
Vadim RappCommented:
'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.
0
 
PatHartmanCommented:
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.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Dale FyeCommented:
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
0
 
classnetAuthor Commented:
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?
0
 
classnetAuthor Commented:
The Like statement by itself works fine (no data shows everything).
0
 
classnetAuthor Commented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
the "Like" will not work here..

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

did you try fyed post at http:#a39711382 ?
0
 
classnetAuthor Commented:
capricorn1:  Yes... doesn't return anything whether I leave it blank or not.  Troubleshooting it now.
0
 
Dale FyeCommented:
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:
0
 
Vadim RappCommented:
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
0
 
classnetAuthor Commented:
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.
0
 
Vadim RappCommented:
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]) & '*'
0
 
classnetAuthor Commented:
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.
0
 
classnetAuthor Commented:
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.
0
 
Vadim RappCommented:
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]
0
 
IrogSintaCommented:
Try
Like Nz([Forms]![SearchArmature]![txtSlots],*)
0
 
PatHartmanCommented:
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 ....

Open 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.
0
 
IrogSintaCommented:
Correction to my post:
Like Nz([Forms]![SearchArmature]![txtSlots],"*")
0
 
PatHartmanCommented:
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
0
 
Dale FyeCommented:
@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.
0
 
classnetAuthor Commented:
That did it!  I am actaully, in the data view, entering
Like IIf(Nz([forms]![SearchArmature]![txtPromptName]),[forms]![SearchArmature]![PromptName],"*")
In the criteria field
0
 
Vadim RappCommented:
---
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.