Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query which has a date field and I would like to extract monthly
data by just entering the month that I am interested in say "January" or
"July". I want to avoid this syntax "Between [Enter First Date] And [Enter
Last Date]", which may return data that is more than a month depending on
what has been entered.

Can someone please help
 
Alylia,

Add a calculated field to your query, like:

Expr1: Month([YourDateField])

This returns the month of the date field, so you can apply a criterion 1
- 12 on it, and get the respective month. Or, you could use:

Expr1: Format([YourDateField],"mmmm")

so you apply a criterion like January or July.

HTH,
Nikos
 
Hi,


Alternatively:


BETWEEN DateSerial( Year(Now), [Starting Month (1-12)], 1) AND
DateSerial( Year(Now), [Ending Month (1-12)] + 1, 0)


Note that this assumed we use the same year for the two dates.


Hoping it may help,
Vanderghast, Access MVP
 
I have this same problem. I have a database with many fields. I want to be
able to run a query on the number of field reports i have completed for the
month. I tried all different kinds of formulas in the criteria under my
date field. i need to be able to pull all field reports for one month, like
from January 1st to January 31st. Any Ideas? i even got my college books
out and didnt find anything about a range of dates in them. Help!!!

(e-mail address removed)
 
Hi,


If there is just one year of data,

WHERE MONTH( FieldName ) = MonthNumber

could do.

If there is more than one year of data,

WHERE FieldName BETWEEN DATESERIAL( Year1, Month1, 1) AND
DATESERIAL( Year2, Month2+1, 0)


would return records between the first of Month1, Year1 and the last day of
Month2, Year2.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top