Sorting a query is bringing up a parameter request for some fields

D

Donna

I have created a qry that uses a few fields created by IIF statements. When I run the query it prompts me only for the paramater of [enter year] that I put in it. But as soon as I add a sort on one of the fields (not the IIF statement fields), it immediately asks me for a parameter value for 2 of my 4 IIF statement fields. Is there any way I can keep that from happening? This is my IIF statement field: GCRS: (IIf([CRS] Like 5 & "*",[CRS],(IIf([CRS] Like 6 & "*",[CRS]))))

By the way, this is the only way I could figure out how to do an IIF statement asking for "If CRS begins with a 5 or a 6 then give me the CRS number, else leave it blank. Do you have a better way to do this also?
 
T

tina

for the IIf() function, how about:

GCRS: IIf(Left([CRS], 1) In(5, 6), [CRS], Null)

if that doesn't work, try

GCRS: IIf(Left([CRS], 1) = 5 Or Left([CRS], 1) = 6, [CRS], Null)

hth


Donna said:
I have created a qry that uses a few fields created by IIF statements.
Click to expand...
When I run the query it prompts me only for the paramater of [enter year]
that I put in it. But as soon as I add a sort on one of the fields (not the
IIF statement fields), it immediately asks me for a parameter value for 2 of
my 4 IIF statement fields. Is there any way I can keep that from happening?
This is my IIF statement field: GCRS: (IIf([CRS] Like 5 &
"*",[CRS],(IIf([CRS] Like 6 & "*",[CRS])))).
By the way, this is the only way I could figure out how to do an IIF
Click to expand...
statement asking for "If CRS begins with a 5 or a 6 then give me the CRS
number, else leave it blank. Do you have a better way to do this also?
 
J

John Spencer (MVP)

What version of Access?
What does the SQL statement that fails look like?
Which parameters are you prompted for?
 
J

John Vinson

By the way, this is the only way I could figure out how to do an IIF statement asking for "If CRS begins with a 5 or a 6 then give me the CRS number, else leave it blank. Do you have a better way to do this also?
Click to expand...

How about

IIF([CRS] LIKE "[56]*", [CRS], Null)
 
Top