I've rewritten the expression to make it easier to understand, but I cannot
see any problem with your expression. Are you sure that SHOP contains the
letter "A" in a field. Is it possible that SHOP is a LOOKUP Field and
actually contains a value that is other then what is displayed.
RECFROM1:
IIf([RECFROM] IN ("FAB","DEV"),[RECFROM]
,IIf([RECFROM]="JOB" And [prog]="XXX","XXXX"
,IIf([RECFROM]="JOB" And [prog]="SL","LAUNCH"
,IIf([RECFROM]="JOB" And [prog]="SEA" And [SHOP] Like "*A*","SEA"
,IIf([RECFROM]="JOB" And [prog] Not In ("P8-A","SL","SEA"),"JOB","OTHER")))))
Lookup fields can contain data other than what is displayed. If you open your
table in datasheet view and the SHOP field has a combobox for data entry then
you have a lookup field. If so, post back for a discussion on why lookup
fields can be a problem.
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
It didn't work with the percent signs. Here is the entire statement.
RECFROM1:
IIf([RECFROM]="FAB","FAB",IIf([RECFROM]="DEV","DEV",IIf([RECFROM]="JOB" And
[prog]="XXX","XXXX",IIf([RECFROM]="JOB" And
[prog]="SL","LAUNCH",IIf([RECFROM]="JOB" And [prog]="SEA" And [SHOP] Like
"%A%","SEA",IIf([RECFROM]="JOB" And [prog]<>"P8-A" And [prog]<>"SL" And
[prog]<>"SEA","JOB","OTHER"))))))
John Spencer said:
Wildcards do work in an IIF statement. You didn't post the entire statement,
so the problem may be caused elsewhere.
You can try using % instead of *. THe % as a wildcard would be used if you
had check Ansi 92 compatible SQL or if you were using an ADP instead of and
MDB as the backend.
IIf([RECFROM]="JOB" And [PROG]="Seattle" And [SHOP] Like "%A%","Seattle","Not
Seattle")
Otherwise, post the entire expression you are attempting to use.
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
djf wrote:
Do wildcards not work in an IIF statement?
:
Maybe you could use the Like *A* in the selection criterion for that [SHOP]
field, and leave it out of the IIF() statement...
Regards
Jeff Boyce
Microsoft Office/Access MVP
In the Field: line of the access query grid.
:
Where are you running this code?
Regards
Jeff Boyce
Microsoft Office/Access MVP
The wildcard search is not working for the SHOP field in the following
code.
I want to search the SHOP field for all strings that contain an A. I
have
this code in an Access query. Everything works correctly except for the
*
parameter. What is the proper syntax?
IIf([RECFROM]="JOB" And [PROG]="Seattle" And [SHOP] Like
"*A*","Seattle"
