Solved

access iif Wildcard in Query

Posted on 2011-03-06
4
651 Views
Last Modified: 2012-05-11
Sorry this is so basic but...struggling.

I have a dropdown that I need to change and then the Query adjusts

Looks like this:
ddBizProcess  (data)
0 = All
1 = xxx
2 = yyyy

In the appropriate field in query design I have

IIf([Forms]![test]![ddBizProcess]>0,[forms]![test]![ddBizProcess],"*")


I keep getting an error.

basically if they select all then bypass the criteria  if not match the field.

hmmm...

tnx experts
0
Comment
Question by:skillilea
  • 2
  • 2
4 Comments
 
LVL 75
ID: 35049256
Try this


IIf([Forms]![test]![ddBizProcess]>0,[forms]![test]![ddBizProcess],[TheNameOfTheCriteriaField])
                                                                match the field itself        ^^^^^^^^^^^^^^^^^^^^^

mx  
0
 

Author Comment

by:skillilea
ID: 35049572
IIf([Forms]![test]![ddBizProcess]>0,[forms]![test]![ddBizProcess],[TheNameOfTheCriteriaField])


ddBizProcess = the DropDown that has the choices

if ddBizProcess is empty (NULL) or user selected ALL ( value  = 0) then I want to show ALL records or "*"   ELSE I want to match ddBizProcess to the field CRTYpeID

population of ddBizProcess is:

SELECT crTypeID From ....

tnx

sorry for the confusion
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 35049940
What is the Name of the Field in the query that you are using this criteria on?
Substitute that for TheNameOfTheCriteriaField  in the expression below. I also added the case for Null which you didn't mention before.

IIf( Nz([Forms]![test]![ddBizProcess],0) >0, [forms]![test]![ddBizProcess],[TheNameOfTheCriteriaField])

0
 

Author Closing Comment

by:skillilea
ID: 35050076
Sorry for the confusion and thanks for the help.  You had it the first time.

tnx
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

821 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