Access Each Day broken down

Hello everyone,
I have a SQL Query that gives me a complete breakdown of every day in the month:

SELECT DateSerial(Year(Date()),Month(Date()),[dday]) AS Dates
FROM days
WHERE (((DateSerial(Year(Date()),Month(Date()),[dday]))<DateSerial(Year(Date()),Month(Date())+1,1)));

Now what I would like to do is check to see what day it is so if it's the 1st of the month it will give me the whole range for the previous month so I can do a Month end report. Also if it's the first of the year I would need it to do the previous year, previous month so on January 1st it will still look at the whole month of December. Can someone help me with this?

Thanks again,
Jester
smart2009Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try this


SELECT DateSerial(Year(Date()),Month(Date()),[dday]) AS Dates
FROM days
Where iif(day(Date())=1 and Month(Date())<>1, DateSerial(Year(Date()),Month(Date()),[dday]) Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date()),0),iif(day(Date())=1 and Month(Date())=1,DateSerial(Year(Date()),Month(Date()),[dday]) Between DateSerial(Year(Date())-1,1,1) And DateSerial(Year(Date())-1,12,31),DateSerial(Year(Date()),Month(Date()),[dday]))<DateSerial(Year(Date()),Month(Date())+1,1)))
0
Rey Obrero (Capricorn1)Commented:
oops, too many ")" at the end

try this


SELECT DateSerial(Year(Date()),Month(Date()),[dday]) AS Dates
FROM days
Where iif(day(Date())=1 and Month(Date())<>1, DateSerial(Year(Date()),Month(Date()),[dday]) Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date()),0),iif(day(Date())=1 and Month(Date())=1,DateSerial(Year(Date()),Month(Date()),[dday]) Between DateSerial(Year(Date())-1,1,1) And DateSerial(Year(Date())-1,12,31),DateSerial(Year(Date()),Month(Date()),[dday]))<DateSerial(Year(Date()),Month(Date())+1,1))
0
smart2009Author Commented:
Capricorn1 Thanks for the reply. I rolled the date on the server back to 3/1/2011 and all I get when using the above code is 3/1/2011 any ideas?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

als315Commented:
You can use this function to check your date:
 
Function chkDate(D As Date) As Boolean
Dim CD As Date
CD = Date
CD = DateValue("01.02.2011")
chkDate = False
If Day(CD) = 1 Then
  If Month(CD) = 1 Then
     If Year(D) = Year(CD) - 1 Then
        chkDate = True
     End If
  ElseIf Month(D) = Month(CD) - 1 And Year(D) = Year(CD) Then
     chkDate = True
  End If
ElseIf Month(D) = Month(CD) And Year(D) = Year(CD) Then
  chkDate = True
End If
End Function

Open in new window

0
als315Commented:
This string is used for checking:
CD = DateValue("01.02.2011")
Comment it when finished testing
0
Rey Obrero (Capricorn1)Commented:
try this one ( a "(" was misplaced on the previous post


SELECT DateSerial(Year(Date()),Month(Date()),[dday]) AS Dates
FROM days
WHERE IIf(day(Date())=1 And Month(Date())<>1,DateSerial(Year(Date()),Month(Date()),[dday]) Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date()),0),IIf(day(Date())=1 And Month(Date())=1,DateSerial(Year(Date()),Month(Date()),[dday]) Between DateSerial(Year(Date())-1,1,1) And DateSerial(Year(Date())-1,12,31),DateSerial(Year(Date()),Month(Date()),[dday])<DateSerial(Year(Date()),Month(Date())+1,1)))
0
smart2009Author Commented:
Capricorn1, Ok now I get each day of March listed instead of February.

SELECT DateSerial(Year(Date()),Month(Date()),[dday]) AS Dates
FROM days
WHERE IIf(day(Date())=1 And Month(Date())<>1,DateSerial(Year(Date()),Month(Date()),[dday]) Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date()),0),IIf(day(Date())=1 And Month(Date())=1,DateSerial(Year(Date()),Month(Date()),[dday]) Between DateSerial(Year(Date())-1,1,1) And DateSerial(Year(Date())-1,12,31),DateSerial(Year(Date()),Month(Date()),[dday])<DateSerial(Year(Date()),Month(Date())+1,1)));

0
smart2009Author Commented:
If it helps here is an example database. Month-to-Date.mdb
0
Rey Obrero (Capricorn1)Commented:
<Capricorn1, Ok now I get each day of March listed instead of February.>

what exactly are you looking for?
try this one, will return all dates less than the First of the current month

SELECT DateSerial(Year(Date()),Month(Date()),[dday]) AS Dates
FROM days
WHERE IIf(day(Date())=1 And Month(Date())<>1,DateSerial(Year(Date()),Month(Date()),[dday]) Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date()),0),IIf(day(Date())=1 And Month(Date())=1,DateSerial(Year(Date()),Month(Date()),[dday]) Between DateSerial(Year(Date())-1,1,1) And DateSerial(Year(Date())-1,12,31),DateSerial(Year(Date()),Month(Date()),[dday])<DateSerial(Year(Date()),Month(Date()),1)));
0
smart2009Author Commented:
If I have a report that is on the 1st of the month that I get a breakdown of the days for the previous month and if it's January 1st I get a breakdown of days in December or the previous year. If it's the middle of any given month I get just the current months days. Does that make sense?
0
Rey Obrero (Capricorn1)Commented:
sorry, don't use the last post, use this


SELECT DateSerial(Year(Date()),Month(Date())-1,[dday]) AS Dates
FROM days
WHERE IIf(day(Date())=1 And Month(Date())<>1,DateSerial(Year(Date()),Month(Date()),[dday]) Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date()),0),IIf(day(Date())=1 And Month(Date())=1,DateSerial(Year(Date()),Month(Date()),[dday]) Between DateSerial(Year(Date())-1,1,1) And DateSerial(Year(Date())-1,12,31),DateSerial(Year(Date()),Month(Date())-1,[dday])<DateSerial(Year(Date()),Month(Date()),1)));
0
smart2009Author Commented:
Ok well this give me a previous month but if I switch the date back to todays date I still get a previous date and if I switch it to the 1st I get nothing.
0
Rey Obrero (Capricorn1)Commented:

try this one



SELECT DateSerial(Year(Date()),Month(Date()),[dday]) AS Dates
FROM days
WHERE IIf(day(Date())=1 And Month(Date())<>1,DateSerial(Year(Date()),Month(Date()),[dday]) Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date()),0),IIf(day(Date())=1 And Month(Date())=1,DateSerial(Year(Date()),Month(Date()),[dday]) Between DateSerial(Year(Date())-1,1,1) And DateSerial(Year(Date())-1,12,31),DateSerial(Year(Date()),Month(Date()),[dday])<DateSerial(Year(Date()),Month(Date()),Day(Date()))))
0
smart2009Author Commented:
Ok now a current month then when I switch to the 1st I only get 3/1/2011
0
Rey Obrero (Capricorn1)Commented:
one more time


SELECT DateSerial(Year(Date()),Month(Date()),[dday]) AS Dates
FROM days
WHERE IIf(day(Date())=1 And Month(Date())<>1,DateSerial(Year(Date()),Month(Date()),[dday]) Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0),IIf(day(Date())=1 And Month(Date())=1,DateSerial(Year(Date()),Month(Date()),[dday]) Between DateSerial(Year(Date())-1,1,1) And DateSerial(Year(Date())-1,12,31),DateSerial(Year(Date()),Month(Date()),[dday])<DateSerial(Year(Date()),Month(Date()),Day(Date()))))
0
smart2009Author Commented:
For today's date I get the 1st - 14th and for the 1st I get nothing.  1st of January I get nothing too.
0
Rey Obrero (Capricorn1)Commented:
ok.. be back, have to be in a meeting..
meantime check the rules that you posted above and give a more clear conditions..
0
als315Commented:
My function can be used without generating a set of dates, you can directly compare your date in record with it
0
smart2009Author Commented:
Can you explain how I would use the function? I tried to use it but I got some type mismatch errors. Above I enclosed a databse that has the table info in it if it helps.
0
als315Commented:
How do you like to use results of your query? If you are joining it with existing data (date value), you can directly select dates, without generating additional data set.
Function returns true if data is in expected range and false, if not.
0
als315Commented:
I've also prepared other function for generating dates. See query TestDays1.
Month-to-Date.zip
0
smart2009Author Commented:
Ok here is what I am trying to achieve in the end. I have a report that runs every day. It's a Month to Date Report. So it will give me data for the current month. All of our data is pulled from a mirror that runs every night at midnight. Each morning the report runs at like 6am and pulls all the data for the month. The problem comes when it's the 1st of the month and there is no data for that day because the mirror only has data up to the previous day. This is where I would like to do a Month end report so the query would need to be smart enough to know it's the 1st and roll back the dates to the previous month. I have the same problem when it's the first of the year so I would need it to roll back both the month and year. Essentially I don't need every day of the month just the 1st day and the last day so I have a date to query off of.


Does that make sense?

Thanks again,
Jester
0
Rey Obrero (Capricorn1)Commented:
why not use the table where you based your report in the query, instead of the table you are currently using?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
post the name of the table and the name of the date field.
0
als315Commented:
It's a Month to Date Report. So it will give me data for the current month
You should have some criteria in report source query. Can you show this query?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.