Date Range

[Guest]

How do I put in "Date Ranges" in a Query. I want to have the queries pull by
each month. Also want a query to give daily totals.

 

[Rick Brandt]

How do I put in "Date Ranges" in a Query. I want to have the queries
pull by each month. Also want a query to give daily totals.

Something like this works well if your Dates won't all have midnight (0) as
the time...

SELECT *
FROM TableName
WHERE DateField >= SomeDate
AND DateField < DateAdd("D", 1, SomeOtherDate)

If all dates have midnight as the time then the last line can become...

AND DateField <= SomeOtherDate

....or you could use BETWEEN instead.

To get daily totals use a GroupBy on your Date field (if all have midnight
as time) or on an expression that strips out the time (if some values are
not midnight) then use Sum on whatever field you want to total.

 

[Guest]

What format should I use for the date?

Something like this works well if your Dates won't all have midnight (0) as
the time...

SELECT *
FROM TableName
WHERE DateField >= SomeDate
AND DateField < DateAdd("D", 1, SomeOtherDate)

If all dates have midnight as the time then the last line can become...

AND DateField <= SomeOtherDate

....or you could use BETWEEN instead.

To get daily totals use a GroupBy on your Date field (if all have midnight
as time) or on an expression that strips out the time (if some values are
not midnight) then use Sum on whatever field you want to total.

 

[Rick Brandt]

What format should I use for the date?

If you are talking about the format of the field where you are storing dates
then the format does not matter.

If you are entering a date literal you need to use either...

US Formats:
m/d/yy
m/d/yyyy

ISO Format:
yyyy-mm-dd

A format that disambiguates the month from the day by using alpha characters
for the month
mmm-d-yy
d mmmm yyyy
etc..

I prefer ISO myself.

 

[Guest]

I agree ISO is the way I like to go to. It reads better.

You seem to know a great deal on Access.

I am some what new to Access and have taken some courses at the local Comp
USA.

I have been given the task to transfrom an extensive spreadsheet in to a
database. Is there a way that I can create a FORM that gives me the
information from the queries that I created. That way one of my 20 bosses
can open it up and see all 30 query totals.