Access and date ranges
I am trying to create a query which will return records for the current month only. The trick to this is the date which starts this application's month is the 27th. This is what I was trying, it didn't work.
If today is before the 27th of the month then the starting date should be the 27th of last month. Otherwise it should just run from the 27th of this month until today. Confused? Me too!
iif(Day(now()) < 27, < DateAdd("m", -1, Month(now()) & "/27/" & Year(now()), < Month(now()) & "/27/" & Year(now()))Any help would be greatly appreciated.
Thanks.
Peter J. Boettcher posted this at 17:03 — 19th March 2002.
They have: 812 posts
Joined: Feb 2000
I'm not sure exactly what you need but this will give you the right starting date, if you need the end date then just add one more variable to hold it.
strCurrentMonth = Now()
strPreviousMonth = DateAdd("m",-1,strCurrentMonth)
If Day(strCurrentMonth) > 27 Then
strUseMonth = Month(strCurrentMonth) & "/27/" & Year(strCurrentMonth)
Else
strUseMonth = Month(strPreviousMonth) & "/27/" & Year(strPreviousMonth)
End If
PJ | Are we there yet?
pjboettcher.com
artsapimp posted this at 21:24 — 19th March 2002.
They have: 330 posts
Joined: Apr 2000
Thank you.
I know that is the correct way to do it in ASP but I have to have this date manipulation done in access before bringing into the ASP application.
Do you know how I would write this in Access? I can't set variables in Access or I would follow the same basic idea you suggested.
Thanks for any help.
Free Math Test
Fun Math Games
Peter J. Boettcher posted this at 16:42 — 20th March 2002.
They have: 812 posts
Joined: Feb 2000
Sorry about that, try this:
IIf(Day(Now()) > 27, Month(Now()) & "/27/" & Year(Now()), Month(DateAdd("m",-1,Now())) & "/27/" & Year(Now()))
I don't have a lot of experience using functions in Access but that should work.
PJ | Are we there yet?
pjboettcher.com
artsapimp posted this at 18:02 — 20th March 2002.
They have: 330 posts
Joined: Apr 2000
I think that's going to do it. I have to change the formatting a little so it will do a between statement for each result instead of one day but I think I can handle that. Thank you.
Free Math Test
Fun Math Games
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.