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

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" wrote in message
...
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?

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

Donna wrote:

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?

On Sat, 29 May 2004 18:16:02 -0700, "Donna"
wrote:

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?

How about

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


John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public