Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

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
0
smart2009
Asked:
smart2009
  • 10
  • 9
  • 6
1 Solution
 
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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
 
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 10
  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now