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

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

Guest

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
 
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.
 
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...
 
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)
 
Back
Top