Solved

Access Each Day broken down

Posted on 2011-03-15
25
234 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:smart2009
  • 10
  • 9
  • 6
25 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 35141048
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 35141108
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
 

Author Comment

by:smart2009
ID: 35141177
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
 
LVL 39

Expert Comment

by:als315
ID: 35141197
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
 
LVL 39

Expert Comment

by:als315
ID: 35141207
This string is used for checking:
CD = DateValue("01.02.2011")
Comment it when finished testing
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 35141224
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
 

Author Comment

by:smart2009
ID: 35141254
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
 

Author Comment

by:smart2009
ID: 35141373
If it helps here is an example database. Month-to-Date.mdb
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 35141395
<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
 

Author Comment

by:smart2009
ID: 35141418
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 35141429
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
 

Author Comment

by:smart2009
ID: 35141506
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 35141513

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
 

Author Comment

by:smart2009
ID: 35141534
Ok now a current month then when I switch to the 1st I only get 3/1/2011
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 35141565
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
 

Author Comment

by:smart2009
ID: 35141587
For today's date I get the 1st - 14th and for the 1st I get nothing.  1st of January I get nothing too.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 35141675
ok.. be back, have to be in a meeting..
meantime check the rules that you posted above and give a more clear conditions..
0
 
LVL 39

Expert Comment

by:als315
ID: 35141682
My function can be used without generating a set of dates, you can directly compare your date in record with it
0
 

Author Comment

by:smart2009
ID: 35141703
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
 
LVL 39

Expert Comment

by:als315
ID: 35144889
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
 
LVL 39

Expert Comment

by:als315
ID: 35145750
I've also prepared other function for generating dates. See query TestDays1.
Month-to-Date.zip
0
 

Author Comment

by:smart2009
ID: 35149657
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 35149720
why not use the table where you based your report in the query, instead of the table you are currently using?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 35150148
post the name of the table and the name of the date field.
0
 
LVL 39

Expert Comment

by:als315
ID: 35153961
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now