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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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?
 
What version of Access?
What does the SQL statement that fails look like?
Which parameters are you prompted for?
 
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)
 
Back
Top