John W. Vinson...continued help please

[DevilDog1978]

SELECT DISTINCTROW OUTLOOK_APPROVEt1.To, OUTLOOK_APPROVEt1.Subject,

OUTLOOK_APPROVEt1.Received, OUTLOOK_APPROVEt1.Contents,
OUTLOOK_APPROVEt1.CountOfImportance INTO OUTLOOK_APPROVEt2
FROM OUTLOOK_APPROVEt1
WHERE Received >= date_sub(curdate(), interval 1 month) and Received <=
date_sub(curdate(), interval 1 day)
WITH OWNERACCESS OPTION;

I am having trouble with the Where portion. I would like for this query to
reveal all the emails over the past physical month. Example February 1st I
would like to be able to collect the data for all of January. Any help would
be greatly appreciated.

This looks like a mix of T-SQL and Access SQL...

If it's in Access, I'd use the DateSerial function (I'm not familiar with
date_sub if indeed that isn't just your placeholder).

WHERE Received >= DateSerial(Year(Date()), Month(Date()) - 1, 1) AND Received
< DateSerial(Year(Date()), Month(Date()), 1)

--

John W. Vinson [MVP]


The above worked yesterday. Today for some reason this query is not showing
emails from the past month. Does the format of the date matter?

 

[John W. Vinson]

This looks like a mix of T-SQL and Access SQL...

If it's in Access, I'd use the DateSerial function (I'm not familiar with
date_sub if indeed that isn't just your placeholder).

WHERE Received >= DateSerial(Year(Date()), Month(Date()) - 1, 1) AND Received
< DateSerial(Year(Date()), Month(Date()), 1)

The format is irrelevant. As written, it should return all records where the
Date/Time field named Received is in December 2009. Is this not what you're
getting? If so what is in [Received] and what ARE you getting? If it's not
what you want, what do you want?

 

[Dorian]

make sure 'Received' is a date variable.
Otherwise it looks like it should work.
Try displaying these two values and see what they contain:-
DateSerial(Year(Date()), Month(Date()) - 1, 1)
DateSerial(Year(Date()), Month(Date()), 1)
The first should be the first day of the previous month and the second
should be the first day of the current month.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[DevilDog1978]

I need a cup of coffee. It works fine. I forgot that I cleaned out my inbox
of 2009 email...so everything in the database is 2010. Thanks for your help.
Like I said what you gave me yesterday worked beautifully.

This looks like a mix of T-SQL and Access SQL...

If it's in Access, I'd use the DateSerial function (I'm not familiar with
date_sub if indeed that isn't just your placeholder).

WHERE Received >= DateSerial(Year(Date()), Month(Date()) - 1, 1) AND Received
< DateSerial(Year(Date()), Month(Date()), 1)

The format is irrelevant. As written, it should return all records where the
Date/Time field named Received is in December 2009. Is this not what you're
getting? If so what is in [Received] and what ARE you getting? If it's not
what you want, what do you want?

 

[John W. Vinson]

I need a cup of coffee. It works fine. I forgot that I cleaned out my inbox
of 2009 email...so everything in the database is 2010. Thanks for your help.
Like I said what you gave me yesterday worked beautifully.

<g> Hey, it found everything that was there!

Now where did I leave my wits... oh, here's half of them...