Intermittent Error Message

[Stang70Fastback]

Small Change = Error Message

SELECT DISTINCT [Shift List].[Employee Number]
FROM [Shift List]
WHERE ((([Shift List].[Day Of Week])=[Frame11]) AND ((TimeValue([Start Time]))>=TimeValue([TempVars]![Start]) And (TimeValue([Start Time]))<TimeValue([TempVars]![End]))) OR ((([Shift List].[Day Of Week])=[Frame11]) AND ((TimeValue([End Time]))>TimeValue([TempVars]![Start]) And (TimeValue([End Time]))<=TimeValue([TempVars]![End])));

Hey guys, I've got a bit of a mystery that I can't seem to solve :banghead:
I am getting an error message for one of my queries (the code for the query is shown above. The error is shown below.

The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

The query was working fine and I never had any issues until I tweaked one thing. The two instances where it says [Frame11] above used to just be the number 3 until I changed it to allow for other numbers based on a user's input on the form. As soon as I did that, the query went haywire. Frame11 is an option group that can be anything from 1-6. When I try to view the query without going through the Form, it prompts me for a value for [Frame11] so that part seems to be working. After I enter the value, that's when I get the error.

Any ideas what my problem might be?

 

[pr2-eugin]

Hello Stang70Fastback, when you are referring to controls outside thr Forms you have to follow the Sequence as,

Forms![formName]![controlName]

Where formName will be the name of the form that you have used to get the options from the user. So you have to change it as,,

SELECT DISTINCT [Shift List].[Employee Number]
FROM [Shift List]
WHERE ((([Shift List].[Day Of Week])=[B][COLOR=Red]Forms![COLOR=Blue]formName[/COLOR]![Frame11][/COLOR][/B]) AND ((TimeValue([Start Time]))>=TimeValue([TempVars]![Start]) And (TimeValue([Start Time]))<TimeValue([TempVars]![End]))) OR ((([Shift List].[Day Of Week])=[COLOR=Red][B]Forms![COLOR=Blue]formName[/COLOR]![/B][B][Frame11][/B][/COLOR]) AND ((TimeValue([End Time]))>TimeValue([TempVars]![Start]) And (TimeValue([End Time]))<=TimeValue([TempVars]![End])));

Change the blue bits..

 

[Stang70Fastback]

Thank you for your reply. I did what you suggested, however I am still receiving the same error message! As I said, I don't think that's the issue, as Frame11 is always on the active form,and if it isn't then the program just prompts me for the value of Frame11. It's better practice to do it the way you suggested, so I have changed it, but that is not the reason for my error message. I just can't figure it out!!!

 

[Stang70Fastback]

So does anyone else have any other suggestions, or am I SOL at this point? I just can't picture the equation I have being "too complicated" considering some of the other stuff I've seen on these boards...