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

pete234

Active Member
Joined
Feb 10, 2004
Messages
419
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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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.
Code:
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

Active Member
Joined
Feb 10, 2004
Messages
419
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

Well-known Member
Joined
Feb 20, 2002
Messages
1,796

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
I think you'll find that this gives the last day of the current month:

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

Denis
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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
 

Forum statistics

Threads
1,141,626
Messages
5,707,486
Members
421,510
Latest member
haroonstr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top