Date comparison query

[google3luo359]

I have a table with login dates.

I'd like to write a query that will check this table's records, (the
date field) and compare them to today's date.

If today's date is between Sept 1 and Dec 31 then select all records
from the table whose date is between Sept 1 (of current year) and
today.

But if today's date is between Feb1 and June 30, then select all
records whose date is between Feb 1 (of the current year) and today's
date.
This is just a little too tricky for me at this time.

TIA for any help! Ric

 

[Allen Browne]

Use IIf() or Switch() to handle multiple conditions.
Use Month(Date()) to get the current month.

Use DateSerial() to build the dates

This example breaks the year at Sep 1. You will need to extend it to hand
the Feb1 and June 30 cases as well:
IIf(Month(Date()) >= 8,
Between DateSerial(Year(Date()),9,1) And DateSerial(Year(Date()),
12,31),
Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date()), 8,31))

 

[John Spencer]

Just out of curiousity, what do you do if today's date in in January?

Allen, I wonder how your example is supposed to work. Are you saying to
enter your suggestion as the criteria? Will that work?

I would have suggested the following as criteria.
Between DateSerial(Year(Date()),IIF(Month(Date())>8,9,2),1) and Date()

I realize that still doesn't account for the date being in January, but I
don't know what the poster wants to do when January is the current date if
the date field contains a January date.

 

[Allen Browne]

Agreed. I'm not sure this is well thought through, but hopefully the
examples provide enough for the poster to create what they actually want.

 

[google3luo359]

Just out of curiousity, what do you do if today's date in in January?

I didn't mention January (and also July and August) because the query
won't be run at those times.
But to be all inclusive the dates could be:

- If today's date is between Sept 1 and Jan 31, then select all
records
from the table whose date is between Sept 1 and today.
Egs. Today is Dec 1, 2006 select between Sept 1, 2006 and Dec 1,
2006
Today is Jan 28, 2007 select between Sept 1, 2006 and Jan
28, 2007

- But if today's date is between Feb1 and Aug 31, then select all
records whose date is between Feb 1 and today's date.
Egs. Today is May 16, 2006 select between Feb 1, 2006 and May 16,
2006

Allen, I wonder how your example is supposed to work. Are you saying to
enter your suggestion as the criteria? Will that work?

I tried Allen's code but didn't have any luck.
Then I made a few changes to account for a time field in my dates:

SELECT Students.*, DatePass.LogDate
FROM Students INNER JOIN DatePass ON Students.StudNum =
DatePass.StudNum
WHERE (((DatePass.LogDate)=IIf(Month(Date())>=8,([DatePass].[LogDate])
Between DateSerial(Year(Date()),9,1) And
Date()+1,([DatePass].[LogDate]) Between DateSerial(Year(Date()),1,1)
And Date()+1)));

But the above didn't return any results either.

Thanks for your help!

Ric

 

[google3luo359]

I would have suggested the following as criteria.
Between DateSerial(Year(Date()),IIF(Month(Date())>8,9,2),1) and Date()

Hi John,

Thanks for helping out!
I tried out your code and seem to be having good success with it so
far!
All I did was add +1 to the end which I believe allows for the time in
addition to date.

Between DateSerial(Year(Date()),IIf(Month(Date())>8,9,2),1) And
Date()+1


Perhaps I should change 8,9,2 to 8,9,1 to select records from Jan. to
today's date?
That might be better.

Ric