Date Issues

[Chris]

Hello everyone,
I'm trying to grab data from the past 6 full months, meaning since we are in
January I just want to see Dec 06 - Jun 06. I'm currently using the
following function in the criteria line of my query.

=DateAdd("m",-7,Now()) And DateAdd("m",-1,Now())

This is okay but it doesn't really grab my intended dates. What I get with
this function is Dec 15, 2006 thru Jun 17, 2006. Can someone help just get
the past full six months and exclude the current month? Is there a month
function I could use? Help!

Thanks,
Chris

 

[Chris]

Thanks Jeff. I would typically use that type of format but I want to
automate this query so dates don't always have to be entered. Any other
suggestions? Anyone?

 

[Chris]

thanks Robert I will try this. Can you give me a little more detail and an
example on the iif statement to test for the first day? I don't want to
make any mistakes and I want to have a clear understanding. Thanks again.

 

[John Vinson]

Hello everyone,
I'm trying to grab data from the past 6 full months, meaning since we are in
January I just want to see Dec 06 - Jun 06. I'm currently using the
following function in the criteria line of my query.


This is okay but it doesn't really grab my intended dates. What I get with
this function is Dec 15, 2006 thru Jun 17, 2006. Can someone help just get
the past full six months and exclude the current month? Is there a month
function I could use? Help!
Try

= DateSerial(Year(Date()), Month(Date()) - 7, 1) AND < DateSerial(Year(Date()), Month(Date()), 1)

The DateSErial function takes a year, month, and day argument (and
correctly wraps month numbers less than 1 or greater than 12) and
returns a date; I'd suggest using it rather than CDate() which makes
assumptions based on the system date format settings.

John W. Vinson[MVP]

 

[Rick Brandt]

Hello everyone,
I'm trying to grab data from the past 6 full months, meaning since we
are in January I just want to see Dec 06 - Jun 06. I'm currently
using the following function in the criteria line of my query.


This is okay but it doesn't really grab my intended dates. What I
get with this function is Dec 15, 2006 thru Jun 17, 2006. Can
someone help just get the past full six months and exclude the
current month? Is there a month function I could use? Help!

John Vinson's solution is the way to go. I always start out assuming that any
date handling situation is best handled with DateSerial() and only move to other
functions on those rare occassions where DateSerial() doesn't pan out. It
really is about the slickest date function I have ever used.

I actually have created equivalents of DateSerial in T-SQL and javascript
because I found that I just couldn't live without it when I'm not working in
Access.VBA.

 

[Chris]

Thanks so much John.

The DateSErial function takes a year, month, and day argument (and
correctly wraps month numbers less than 1 or greater than 12) and
returns a date; I'd suggest using it rather than CDate() which makes
assumptions based on the system date format settings.

John W. Vinson[MVP]