Trying to query on DAY("29") Or DAY("30"

[pete234]

Hello, this is probably really easy but I'm trying to run a query to find only a data from the last 3 days of each month.

The field I'm querying on is Date/Time field and has dates like 01/01/06 etc. Unfortunately, my query returns no data.

I did have to convert this field from an imported field that originally had it as text. Did not have any errors when I converted it, nor did I have an error when I queried it just returns one blank line.

Any ideas? Maybe I'm using the wrong criteria for what I want to do?

Just to clarify this is a simple query in only one table.

 

[giacomo]

maybe there is a better way to do this, but here's what I came up with...

WHERE Day([yourDate])>=Choose(Month([yourDate]),29,IIf(Year([yourDate]) Mod 4=0,27,26),29,28,29,28,29,29,28,29,28,29);

hth,
Giacomo

 

[VicRauch]

What I would suggest is subtracting 3 from the 1st of next month, thereby allowing Access to worry about number of days per month and leap years.

WHERE [YourDate] >= DateAdd("d", -3, DateSerial(Year(DateAdd("m", 1, Date)), Month(DateAdd("m", 1, Date)), "1"))

This should always give you the last 3 days of the current month.
Someone, within the last 3 months or so showed a way that is much more elequent than my method to get the last day of a month. If someone knows that method, then we would only need to substract 2 to get the last 3 days of the current month.
HTH,

 

[pete234]

Thanks everyone. I only needed it this one time so I did it manually by entering this in the query builder box for the date field. (Turns out I only needed last 2 days.) I want to understand Access SQL better so I will try out this code you've given me too.

Between #01/30/2006# And #01/31/2006#

Looking over that I guess I could have used for 2 days.

#01/30/2006# Or #01/31/2006#

 

[giacomo]

I want to understand Access SQL better so I will try out this code you've given me too

when you do, go with Vic's solution it is the better of the two.

glad you got it working!
Giacomo

 

[SydneyGeek]

I think you'll find that this gives the last day of the current month:

=DateSerial(Year(Now()),Month(Now())+1,0)

Denis

 

[giacomo]

That's very nice Denis! I knew there was a better way. Seeing your solution made me realize that you could even use the expression below to get the second to last day of the month...

=DateSerial(Year(Now()),Month(Now())+1,-1)

Giacomo

 

[VicRauch]

Denis,
Thank you for posting it again. I knew I had seen this from you once before, but forgot how to do it. I'm posting it on the wall in front of me as that is the only way I can remember unless I have actually used it about 15 to 20 times. Gettin' old!

Vic