Between Dates and Grouping

[DajOne]

I have a query totalling, counting and averaging several fields which produces a report. So far so good. In this query, I need to add a Between [] And [] criteria to a DateIn field and these criterias to populate the title of the report.

The problem I am encountering si that since this is a Total query, the Group functions do not work as each record is displayed by the DateIn field which all dates are different. I could do 2 queries but I would loose the criterias to populate the report title..

Any ideas?

Thanks

 

[RickBerem]

Hi,

If I get you correctly you should use WHERE instead
of GROUP BY in your date field.

Hope it helps!



SG

 

[DajOne]

Thanks for the idea..

I tried with 'Where' on the Total line of the query and I still get too many results..

This is my criteria for my DateIn field...

Between ([Startdate MM/DD/YYYY] & " " & #12:00:00 AM#) And ([EndDate MM/DD/YYYY] & " " & #11:59:00 PM#)

The SQL result is as follow:

WHERE (((EB_Query_Five_VBD.[Input Date]) Between ([Startdate MM/DD/YYYY] & " " & #12/30/1899#) And ([EndDate MM/DD/YYYY] & " " & #12/30/1899 23:59:0#)))

Am I missing something?

 

[RickBerem]

Hi,

When you add the time in your date,
try adding it like it's text instead
of Dates so use ([Startdate MM/DD/YYYY] & " " & "12:00:00 AM")

Not sure but I think it should do the trick.

Hope it helps!

SG

 

[JonFer]

It would help if you posted the full SQL for your query.

 

[PHV]

I guess you have to uncheck the Display (or Show ?) tick in the Input Date column, when in Query Design View.

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

 

[DajOne]

I believe that my SQL was incorrect,... I re did it and is working... I have no clue why though

Thanks for the help