Solved

Access Each Day broken down

Posted on 2011-03-15
25
227 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

760 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

18 Experts available now in Live!

Get 1:1 Help Now