Querying multiple Dates

  • Thread starter Thread starter ryan.fitzpatrick3
  • Start date Start date
R

ryan.fitzpatrick3

Is there away in a query to program dates to look at? i.e.

I would like to pull volumes per Month so I take the field that
contains volume figures and can I put these in the criteria below to
pull only that months volume?

Jan
between 01/01/2007 and 01/31/2007

Feb
between 01/02/2007 and 02/27/2007

mar
between 03/01/2007 and 01/31/2007



Is this right, I imagine not, can anyone help?

Ryan
 
Ryan

Do you really want to have to remember how many days "hath August"?

Consider using the Month() function in a query. You could ask for all the
records where the Month([YourDateField] was, say, the month number of August
(8). Of course, you'll also need to use the Year() function ... otherwise,
you'd get all the records where the month was August, no matter what year!

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Yea that's a lot better. Now is there away to have a field look up
volumes for each month. i.e

Jan: Month([YourDateField])
Feb: Month([YourDateField])
Mar: Month([YourDateField])
Apr: Month([YourDateField])

etc

how do I add in the year() function to Feb: Month([YourDateField])?

Ryan

Ryan

Do you really want to have to remember how many days "hath August"?

Consider using the Month() function in a query. You could ask for all the
records where the Month([YourDateField] was, say, the month number of August
(8). Of course, you'll also need to use the Year() function ... otherwise,
you'd get all the records where the month was August, no matter what year!

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP


Is there away in a query to program dates to look at? i.e.
Click to expand...
I would like to pull volumes per Month so I take the field that
contains volume figures and can I put these in the criteria below to
pull only that months volume?
Click to expand...
Jan
between 01/01/2007 and 01/31/2007
Click to expand...
Feb
between 01/02/2007 and 02/27/2007
Click to expand...
mar
between 03/01/2007 and 01/31/2007
Click to expand...
Is this right, I imagine not, can anyone help?
Click to expand...
Ryan
Click to expand...
Click to expand...
 
I would use the Format function. The query would look something like:

SELECT Format([My_Date],"mm-yyyy") AS Expr1, Sum(My_Table.My_Field) AS
Monthly_Total
FROM MY_Table
GROUP BY Format([My_Date],"mm-yyyy");


You could add a WHERE clause to fileter by dates. If you doing more than
one year I would use "yyyy-mm" instead.

[email protected] said:
Yea that's a lot better. Now is there away to have a field look up
volumes for each month. i.e

Jan: Month([YourDateField])
Feb: Month([YourDateField])
Mar: Month([YourDateField])
Apr: Month([YourDateField])

etc

how do I add in the year() function to Feb: Month([YourDateField])?

Ryan

Ryan

Do you really want to have to remember how many days "hath August"?

Consider using the Month() function in a query. You could ask for all the
records where the Month([YourDateField] was, say, the month number of August
(8). Of course, you'll also need to use the Year() function ... otherwise,
you'd get all the records where the month was August, no matter what year!

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP


Is there away in a query to program dates to look at? i.e.
Click to expand...
I would like to pull volumes per Month so I take the field that
contains volume figures and can I put these in the criteria below to
pull only that months volume?
Click to expand...
Jan
between 01/01/2007 and 01/31/2007
Click to expand...
Feb
between 01/02/2007 and 02/27/2007
Click to expand...
mar
between 03/01/2007 and 01/31/2007
Click to expand...
Is this right, I imagine not, can anyone help?
Click to expand...
Ryan
Click to expand...
Click to expand...
Click to expand...
 
Back
Top