Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access Each Day broken down

Posted on 2011-03-15
25
Medium Priority
?
279 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 40

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 40

Expert Comment

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

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 40

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 40

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 40

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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35150148
post the name of the table and the name of the date field.
0
 
LVL 40

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

916 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