Enter Query Date Range on the Fly

[netrusher]

Below is the expression I have in a query. I have a Combo Box on a form that will show all my query's so the one need at the time can be selected. My question is: is there anyway when I select this query to run from the Combo Box on the form that I can input the date range on the fly? The date range will change periodically and I would like to somehow input the date range when running the query from the Combo Box.

FORMS: DCount("[Date_of_Change]","all_trucks_table","[FORM #]=True AND [Date_of_Change] Between #06/30/05# and #07/31/05#")

Thanks for all help!

 

[netrusher]

The above expression? is in the filed box of the Query

 

[netrusher]

The above Expression?? is in the Field Box of the Query

 

[PHV]

In your form create 2 textbox named, say, txtStartDate and txtEndDate.
Then the expression in the query:
FORMS: DCount('Date_of_Change','all_trucks_table','[FORM #]=True AND Date_of_Change Between #' & [Forms]![name of form]![txtStartDate] & '# and #' & [Forms]![name of form]![txtEndDate] & '#')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[netrusher]

Let me modify my posting to the following: Below is the SQL of my query. What I really would like to do is to enter one date range and have information (totals) for all fields below fall within that date range. I would like to be able to enter the date range from my form or when from the Combo Box when I select the Query to run. All I am seeking from each field is a total number for the date range, i.e. Form's=225, PQC's=22, ECN's=27, MCN's=114 etc etc. The query SQL below provides that the way it is written. I just would like to enter the Date Range one time for all four Fields on the Fly from the Form.

SELECT DCount("[Date_of_Change]","all_trucks_table","[FORM #]=True AND [Date_of_Change] Between #06/30/05# and #07/31/05#") AS FORMS, DCount("[Date_of_Change]","all_trucks_table","[PQC #]=True AND [Date_of_Change] Between #06/30/05# and #07/31/05#") AS PQC, DCount("[Date_of_Change]","all_trucks_table","[ECN #]=True AND [Date_of_Change] Between #06/30/05# and #07/31/05#") AS ECN, DCount("[Date_of_Change]","all_trucks_table","[MCN #]=True AND [Date_of_Change] Between #06/30/05# and #07/31/05#") AS MCN
FROM all_trucks_table
GROUP BY DCount("[Date_of_Change]","all_trucks_table","[FORM #]=True AND [Date_of_Change] Between #06/30/05# and #07/31/05#"), DCount("[Date_of_Change]","all_trucks_table","[PQC #]=True AND [Date_of_Change] Between #06/30/05# and #07/31/05#"), DCount("[Date_of_Change]","all_trucks_table","[ECN #]=True AND [Date_of_Change] Between #06/30/05# and #07/31/05#"), DCount("[Date_of_Change]","all_trucks_table","[MCN #]=True AND [Date_of_Change] Between #06/30/05# and #07/31/05#");

Sorry for all of the confusion

 

[netrusher]

I will try what you suggested.

 

[PHV]

As explained above have the textbox in your form, then the query:
PARAMETERS [Forms]![name of form]![txtStartDate] DateTime, [Forms]![name of form]![txtEndDate] DateTime;
SELECT Sum(Abs([FORM #])) AS Forms, Sum(Abs([PQC #])) AS PQC, Sum(Abs([ECN #])) AS ECN, Sum(Abs([MCN #])) AS MCN
FROM all_trucks_table
WHERE ([FORM #]=True OR [PQC #]=True OR [ECN #]=True OR [MCN #]=True)
AND (Date_of_Change Between [Forms]![name of form]![txtStartDate] And [Forms]![name of form]![txtEndDate]);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[netrusher]

Below is what I have put in the Field of my query based on the post above from PHV. I am getting an error saying the expression I have entered is an invalid string. Can anyone tell me where I have gone wrong?


Page Count: DCount("[OMSTodayDate]","all_trucks_table","[OMSDailyPages]=True Date_of_Change Between #' & [Forms]![all_trucks]![FirstDate] & '# and #' & [Forms]![all_trucks]![LastDate] & '#')

 

[PHV]

I gave you a complete SQL instruction, not a field expression.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886