Query Criteria reflex what I want to see on my chart. Is there a way to . . . . . . .

[MFS]

Good morning everyone,


I'm looking to change what my chart data shows by adjusting the time criteria in my Query from the form the chart is located on.
In other words, my Query only provides data for what is currently written in the criteria.

Example : Field [CDate([date1] & " " & [time_at_catcher])]
Criteria [Between DateAdd("h",-2,Now()) And Now()]

Is there anyway to change the -2 in the criteria from my form, say from a drop down or text box????

I hope my question is clear.

Mike

[ItsMe]

Maybe
Between DateAdd("h", Forms!MyFormName.ComboName, Now()) And Now()]

[MFS]

Thank You ItsMe, That makes a bunch of sense.
I'm trying to work with your code but keep coming up with the attached error.



This is the code I'm working with . . . .
[Between DateAdd("h",[Forms]![Cell 4].[combo442],Now()) And Now()]

I will continue to keep working with it, I'm sure I'm looking over something very simple.

MikSpeck

[MFS]

Sorry the error was not attached ,
"This expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain to many complicated elements. Try simplifying the expression by assigning parts of the expression to variables"

[ItsMe]

Let me see if I can test it

[ItsMe]

I was able to get the following to work in a sample DB
WHERE (((tblEquip.A_Date) Between DateAdd("h",[Forms]![Form3].[txtDouble],Now()) And Now()));

So to translate to your names....WHERE (((TableName.FieldName) Between DateAdd("h",[Forms]![Cell 4].[combo442],Now()) And Now()));


After adjusting the syntax, the only way I was able to get the error you are getting was to not have the form open when the query ran.

[MFS]

I know that it's Friday and I'm wanting to get out of here for the week but I'm still not getting it. (So Sorry to be putting you through this)
Here's what I've got in SQL . . . .

[SELECT dbo_CELL_4_TT_DEVICE_1.date_1, dbo_CELL_4_TT_DEVICE_1.time_1, dbo_CELL_4_TT_DEVICE_1.Cope_Temp_1, dbo_CELL_4_TT_DEVICE_1.Drag_Temp_1, dbo_CELL_4_TT_DEVICE_1.Hydraulic_temp_1, dbo_CELL_4_TT_DEVICE_1.Ambient_Temp_1, CDate([date_1] & " " & [time_1]) AS Expr1, dbo_CELL_4_TT_DEVICE_1.date_1, dbo_CELL_4_TT_DEVICE_1.time_1
FROM dbo_CELL_4_TT_DEVICE_1
WHERE (((dbo_CELL_4_TT_DEVICE_1.date_1)="6-13-2014") AND ((CDate([date_1] & " " & [time_1]))=where((([dbo_CELL_4_TT_DEVICE_1].[date_1]) Between DateAdd("h",[Forms]![Cell 4].[combo442],Now()) And Now())))) ]

Here is the error . . . "Undefined function 'where' in expression.

[ItsMe]

EDIT:

So maybe
WHERE (((dbo_CELL_4_TT_DEVICE_1.date_1)="6-13-2014") AND ([dbo_CELL_4_TT_DEVICE_1].[date_1]) Between DateAdd("h",[Forms]![Cell 4].[combo442],Now()) And Now())) ]

I think that is the correct number of parenthesis. You just need to remove the extra WHERE operator/predicate from the middle of your Original WHERE CLause. Here I replaced the extra WHERE with the AND Operator

I noticed another issue with your AND operators. You were throwing another expression where it won't fit. I provided a complete argument. So I edited this post by removing
AND ((CDate([date_1] & " " & [time_1]))

Also, June is correct about questioning the Brackets. When I tested the SQL you provided earlier, I broke it down into small pieces in order to test and then when I had something working, I added it to the design grid in the query builder.

[June7]

Why the outer [] ?

[MFS]

Isn't my code supposed to be bracketed when posting to this forum? Never mind, I see what I did wrong,

My code should be wrapped like this.

Okay, ItsMe, I'm going to try what you recommended on Friday. I will let you know the outcome shortly.