Solved

access iif Wildcard in Query

Posted on 2011-03-06
4
660 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
[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
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

710 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