• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 686
  • Last Modified:

access iif Wildcard in Query

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
skillilea
Asked:
skillilea
  • 2
  • 2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Try this


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

mx  
0
 
skillileaAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
skillileaAuthor Commented:
Sorry for the confusion and thanks for the help.  You had it the first time.

tnx
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now