Error message in query too complex...numeric expression complicate

M

MCSInc

Hope someone helps. I'm getting the error message: Expression typed
incorrectly, or too complex to be evaluated. Numeric expression may contain
too many complicated elements.

I have a function named StatusCriteria where I am assigning a variable
either a single digit (1-5) or a <4 or >0. Then I refer to that variable as
StatusCriteria() in my criteria line. It works for the single statuses 1-5
but not the less then or greater than criterias. The field is a number field
and my variable is a string in order to do the < or >. I'm sure that's the
problem. How do I get around it?

Thanks for you help
 
M

[MVP] S.Clark

Short answer: you can't do that.

Long answer: I think your WHERE clause must read:

WHERE [somefieldname] = StatusCriteria()

And, when StatusCriteria() returns "< 4" then the WHERE clause becomes:

WHERE [somefieldname] = < 4

This don't work. <=4 works, but not =<4.
 
M

MCSInc

Can you suggest any other way of doing it? I have the variable in a query
which I'm using to export data. I'm trying to avoid having to create a
temporary table and then exporting.
Thanks for your help though.

Grace

[MVP] S.Clark said:
Short answer: you can't do that.

Long answer: I think your WHERE clause must read:

WHERE [somefieldname] = StatusCriteria()

And, when StatusCriteria() returns "< 4" then the WHERE clause becomes:

WHERE [somefieldname] = < 4

This don't work. <=4 works, but not =<4.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

MCSInc said:
Hope someone helps. I'm getting the error message: Expression typed
incorrectly, or too complex to be evaluated. Numeric expression may contain
too many complicated elements.

I have a function named StatusCriteria where I am assigning a variable
either a single digit (1-5) or a <4 or >0. Then I refer to that variable as
StatusCriteria() in my criteria line. It works for the single statuses 1-5
but not the less then or greater than criterias. The field is a number field
and my variable is a string in order to do the < or >. I'm sure that's the
problem. How do I get around it?

Thanks for you help
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

IF those are the only choices you might be able to do this with some complex
where clause.

SELECT ...
FROM YourTable
WHERE YourField
Between Switch(StatusCriteria()="<4",0,StatusCriteria()=">0",0,True,StatusCriteria())
And Switch(StatusCriteria()="<4",3,StatusCriteria()=">0",999,True,StatusCriteria())

Criteria: Between Switch(StatusCriteria()="<4",0,StatusCriteria()=">0",0,True,StatusCriteria())
And Switch(StatusCriteria()="<4",3,StatusCriteria()=">0",999,True,StatusCriteria())

Or change you StatusCriteria to return two values with a delimiter ("1/1") or
("0/4") and then parse that, something like

BETWEEN Left(StatusCriteria(),Instr(StatusCriteria(),"/")-1) AND
Mid(StatusCriteria(),Instr(StatusCriteria(),"/")+1)
 
Top