Solved

Access 2010 query help

Posted on 2013-12-11
23
435 Views
Last Modified: 2013-12-11
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?
0
Comment
Question by:classnet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 3
  • +3
23 Comments
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39711334
'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
 
LVL 37

Expert Comment

by:PatHartman
ID: 39711370
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
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 39711382
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
More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

 

Author Comment

by:classnet
ID: 39711394
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
 

Author Comment

by:classnet
ID: 39711399
The Like statement by itself works fine (no data shows everything).
0
 

Author Comment

by:classnet
ID: 39711406
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39711422
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
 

Author Comment

by:classnet
ID: 39711432
capricorn1:  Yes... doesn't return anything whether I leave it blank or not.  Troubleshooting it now.
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 39711439
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39711440
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
 

Author Comment

by:classnet
ID: 39711454
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39711475
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
 

Author Comment

by:classnet
ID: 39711493
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
 

Author Comment

by:classnet
ID: 39711500
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39711546
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39711551
Try
Like Nz([Forms]![SearchArmature]![txtSlots],*)
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 500 total points
ID: 39711561
or:

SELECT Table1.thefield
FROM Table1
WHERE (((Table1.thefield) Like IIf([Forms]![SearchArmature]![txtSlots].[text]="",'*',[Forms]![SearchArmature]![txtSlots].[text])));
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39711600
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39711602
Correction to my post:
Like Nz([Forms]![SearchArmature]![txtSlots],"*")
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39711607
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
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 39711612
@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
 

Author Closing Comment

by:classnet
ID: 39711614
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39712251
---
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question