Solved

Access 2010 query help

Posted on 2013-12-11
23
426 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
  • 8
  • 6
  • 3
  • +3
23 Comments
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
'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 34

Expert Comment

by:PatHartman
Comment Utility
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 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
 

Author Comment

by:classnet
Comment Utility
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
Comment Utility
The Like statement by itself works fine (no data shows everything).
0
 

Author Comment

by:classnet
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
capricorn1:  Yes... doesn't return anything whether I leave it blank or not.  Troubleshooting it now.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
Comment Utility
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
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Try
Like Nz([Forms]![SearchArmature]![txtSlots],*)
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 500 total points
Comment Utility
or:

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

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
Correction to my post:
Like Nz([Forms]![SearchArmature]![txtSlots],"*")
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
@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
Comment Utility
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
Comment Utility
---
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now