Display records for Date

[Guest]

What I'm looking for is a date expression that will look at the current date,
and then display records for the current month only. (from the 1st of the
month till current day)

 

[Brendan Reynolds]

SELECT * FROM YourTable WHERE Format$([YourDateField],"mmyyyy") =
Format$(Date(),"mmyyyy")

Here's an alternative example which is slightly more complex, but has the
advantage that it will allow the database engine to make use of any index
that might exist on the date field (the above example won't) ...

SELECT * FROM YourTable WHERE YourDateField >= DateSerial(Year(Date()),
Month(Date()),1) AND YourDateField <= DateSerial(Year(Date()),
Month(Date())+1,0)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Guest]

Thank you Brendan!

SELECT * FROM YourTable WHERE Format$([YourDateField],"mmyyyy") =
Format$(Date(),"mmyyyy")

Here's an alternative example which is slightly more complex, but has the
advantage that it will allow the database engine to make use of any index
that might exist on the date field (the above example won't) ...

SELECT * FROM YourTable WHERE YourDateField >= DateSerial(Year(Date()),
Month(Date()),1) AND YourDateField <= DateSerial(Year(Date()),
Month(Date())+1,0)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

What I'm looking for is a date expression that will look at the current
date,
and then display records for the current month only. (from the 1st of the
month till current day)

 

[Douglas J. Steele]

Actually, if YourDateField contains time, you'll probably want the ending
condition to be

YourDateField <= DateSerial(Year(Date()), Month(Date())+1, 1)

Otherwise, you won't pick up any records for the last day of the month.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

SELECT * FROM YourTable WHERE Format$([YourDateField],"mmyyyy") =
Format$(Date(),"mmyyyy")

Here's an alternative example which is slightly more complex, but has the
advantage that it will allow the database engine to make use of any index
that might exist on the date field (the above example won't) ...

SELECT * FROM YourTable WHERE YourDateField >= DateSerial(Year(Date()),
Month(Date()),1) AND YourDateField <= DateSerial(Year(Date()),
Month(Date())+1,0)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

What I'm looking for is a date expression that will look at the current
date,
and then display records for the current month only. (from the 1st of the
month till current day)

 

[Brendan Reynolds]

Oops! Good catch, Doug, thanks!

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Actually, if YourDateField contains time, you'll probably want the ending
condition to be

YourDateField <= DateSerial(Year(Date()), Month(Date())+1, 1)

Otherwise, you won't pick up any records for the last day of the month.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

SELECT * FROM YourTable WHERE Format$([YourDateField],"mmyyyy") =
Format$(Date(),"mmyyyy")

Here's an alternative example which is slightly more complex, but has the
advantage that it will allow the database engine to make use of any index
that might exist on the date field (the above example won't) ...

SELECT * FROM YourTable WHERE YourDateField >= DateSerial(Year(Date()),
Month(Date()),1) AND YourDateField <= DateSerial(Year(Date()),
Month(Date())+1,0)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

What I'm looking for is a date expression that will look at the current
date,
and then display records for the current month only. (from the 1st of
the
month till current day)