Calulate previous day

  • Thread starter Thread starter gls858
  • Start date Start date
G

gls858

I have a query based on date. The criteria in the date filed is Date()-1.
This works OK for Tuesday thru Friday but we're closed Sat and Sun. So on
Monday Date()-1 gives me a blank report. I have the report set to run each
day in the scheduler and a macro set up to e-mail the report. My work
around was to build two reports. One for Monday and one for the other days.
Not really the way I think it should be done.

Is it possible to have code that says if it Monday use Date()-3 and all
other days use Date()-1? Or is this a dumb way to do it? I realize I still
have a problem with holidays and such but I can live with that.

gls858
 
You could, but why not just run it Tuesday, Wednesday... Friday, and
Saturday? Don't run it on Sunday or Monday.
 
Rick said:
You could, but why not just run it Tuesday, Wednesday... Friday, and
Saturday? Don't run it on Sunday or Monday.
Click to expand...
Simple but effective. We shut down all of the clients every night.
So I was focused on running it everyday that we were here and the
clients were up and running.
I'll have to move the scheduled events to one of the servers but that
isn't a problem.

Sometimes when you're focused on a solution it's like
putting on blinders :-) Thanks for putting obvious to me
in such a kindly manner.

gls858
 
As an option, try this in the criteria of your query:

IIf(DatePart("w",Date())=2,Date()-3,Date()-1)

It should produce what you want.
 
Mr said:
As an option, try this in the criteria of your query:

IIf(DatePart("w",Date())=2,Date()-3,Date()-1)

It should produce what you want.
Click to expand...

Thanks Mr B I'll give it a try. Always nice to know what
can be done. Haven't used IIf before. My needs are usually
quite simple. Anything real complicated I call in the pros.

gls858
 
Put this function in a standard module and where you have the Date() -1 in
your query, replace it with this:
AddWorkDays(date(), -1)
Note that you will have to modify this code to exclude looking for holidays,
or modify it to suit a holiday table you already have, or create one to fit
the code below. The table name is "holidays", and you only need one field
named [holdate] that is a date field. My table also has a description field.

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days

Dim intDayCount As Integer
Dim intNotADay As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function
 
Klatuu said:
Put this function in a standard module and where you have the Date() -1 in
your query, replace it with this:
AddWorkDays(date(), -1)
Note that you will have to modify this code to exclude looking for holidays,
or modify it to suit a holiday table you already have, or create one to fit
the code below. The table name is "holidays", and you only need one field
named [holdate] that is a date field. My table also has a description field.

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days

Dim intDayCount As Integer
Dim intNotADay As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function


gls858 said:
I have a query based on date. The criteria in the date filed is Date()-1.
This works OK for Tuesday thru Friday but we're closed Sat and Sun. So on
Monday Date()-1 gives me a blank report. I have the report set to run each
day in the scheduler and a macro set up to e-mail the report. My work
around was to build two reports. One for Monday and one for the other days.
Not really the way I think it should be done.

Is it possible to have code that says if it Monday use Date()-3 and all
other days use Date()-1? Or is this a dumb way to do it? I realize I still
have a problem with holidays and such but I can live with that.

gls858
Click to expand...
Click to expand...
Thanks Klatuu,
This will give me something to chew on for a while. My coding skills
are less than minimal but seeing it I can usually figure out the logic.
Hopefully by dissecting examples like this I'll progress to at least
minimal :-)

gls858
 
That is the way we all learn. We just keep slamming our heads against it
until some of it seeps in from repeated contact.

gls858 said:
Klatuu said:
Put this function in a standard module and where you have the Date() -1 in
your query, replace it with this:
AddWorkDays(date(), -1)
Note that you will have to modify this code to exclude looking for holidays,
or modify it to suit a holiday table you already have, or create one to fit
the code below. The table name is "holidays", and you only need one field
named [holdate] that is a date field. My table also has a description field.

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days

Dim intDayCount As Integer
Dim intNotADay As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function


gls858 said:
I have a query based on date. The criteria in the date filed is Date()-1.
This works OK for Tuesday thru Friday but we're closed Sat and Sun. So on
Monday Date()-1 gives me a blank report. I have the report set to run each
day in the scheduler and a macro set up to e-mail the report. My work
around was to build two reports. One for Monday and one for the other days.
Not really the way I think it should be done.

Is it possible to have code that says if it Monday use Date()-3 and all
other days use Date()-1? Or is this a dumb way to do it? I realize I still
have a problem with holidays and such but I can live with that.

gls858
Click to expand...
Click to expand...
Thanks Klatuu,
This will give me something to chew on for a while. My coding skills
are less than minimal but seeing it I can usually figure out the logic.
Hopefully by dissecting examples like this I'll progress to at least
minimal :-)

gls858
Click to expand...
 
Klatuu said:
That is the way we all learn. We just keep slamming our heads against it
until some of it seeps in from repeated contact.

snip<
Click to expand...
Damn. I was hoping for something a lot less painful...like osmosis :-)

gls858
 
Back
Top