Date Issues - Help!

[Samantha]

I have used one of the methods (via a function) posted on this forum to
calculate a date that is 2 days prior to the start date. I called out this
function "PrevWorkingDay" from a query. The date calculation appears to be
ok.
The problem that I am having is setting a date parameter base on this
PrevDate on the query. The PrevDate does not seem to be a acting like a date
should be. When the date parameter is >=12/25/2007, there is no result.
When the date parameter is >=#12/25/2007#, the result is only for dates

=12/25/2007 but excludes 2008 (there are supposed to have 2008 data).

Any help is very much appreciated, as I've spent days on this already!

Here is the problematic query:

SELECT dbo_WOHeader.WONumber, dbo_WOHeader.ClosedFlag,
dbo_WOHeader.StartDate, dbo_WOHeader.RequiredDate,
IIf(IsNull([dbo_WOHeader].[StartDate]),"",Format(PrevWorkingDay([dbo_WOHeader].[StartDate],5,2),"mm/dd/yyyy")) AS PrevDate
FROM dbo_WOHeader
WHERE (((dbo_WOHeader.ClosedFlag)=0) AND
((IIf(IsNull([dbo_WOHeader].[StartDate]),"",Format(PrevWorkingDay([dbo_WOHeader].[StartDate],5,2),"mm/dd/yyyy")))>=12/25/2007));

Here is function "PrevWorkingDay" :

Public Function PrevWorkingDay(FromThisDate As Date, WorkingDays As Integer,
MinusDays As Integer) As Date

Dim ThisWeekDay As String
On Error GoTo PrevWorkingDay_Error

PrevWorkingDay = FromThisDate 'Default all ok

If WorkingDays < 5 Or WorkingDays > 7 Then
'Out of Range
GoTo PrevWorkingDay_Exit
End If

ThisWeekDay = Format(DateAdd("d", -MinusDays, FromThisDate), "ddd")

If WorkingDays = 5 Then 'start with 5 days per week
If Format(FromThisDate, "ddd") = "Mon" Then
If ThisWeekDay = "Sat" Then
PrevWorkingDay = DateAdd("d", -(MinusDays + 1 + 1), FromThisDate)
ElseIf ThisWeekDay = "Sun" Then
PrevWorkingDay = DateAdd("d", -(MinusDays + 2 + 1), FromThisDate)
Else
PrevWorkingDay = DateAdd("d", -(MinusDays + 1), FromThisDate)
End If
Else 'Not a Friday
If ThisWeekDay = "Sat" Then
PrevWorkingDay = DateAdd("d", -(MinusDays + 1), FromThisDate)
ElseIf ThisWeekDay = "Sun" Then
PrevWorkingDay = DateAdd("d", -(MinusDays + 2), FromThisDate)
Else
PrevWorkingDay = DateAdd("d", -(MinusDays), FromThisDate)
End If
End If
ElseIf WorkingDays = 6 Then '6 days per week
If Format(FromThisDate, "ddd") = "Mon" Then
If ThisWeekDay = "Sun" Then
PrevWorkingDay = DateAdd("d", -(MinusDays + 1 + 1), FromThisDate)
Else
PrevWorkingDay = DateAdd("d", -(MinusDays), FromThisDate)
End If
Else 'Not a Friday
If ThisWeekDay = "Sun" Then
PrevWorkingDay = DateAdd("d", -(MinusDays + 1), FromThisDate)
Else
PrevWorkingDay = DateAdd("d", -(MinusDays), FromThisDate)
End If
End If
End If

PrevWorkingDay_Exit:
Exit Function

PrevWorkingDay_Error:
MsgBox Err.Number & Err.Description, , "PrevWorkingDay"
Resume PrevWorkingDay_Exit

End Function

 

[Marshall Barton]

I have used one of the methods (via a function) posted on this forum to
calculate a date that is 2 days prior to the start date. I called out this
function "PrevWorkingDay" from a query. The date calculation appears to be
ok.
The problem that I am having is setting a date parameter base on this
PrevDate on the query. The PrevDate does not seem to be a acting like a date
should be. When the date parameter is >=12/25/2007, there is no result.
When the date parameter is >=#12/25/2007#, the result is only for dates

=12/25/2007 but excludes 2008 (there are supposed to have 2008 data).

Any help is very much appreciated, as I've spent days on this already!

Here is the problematic query:

SELECT dbo_WOHeader.WONumber, dbo_WOHeader.ClosedFlag,
dbo_WOHeader.StartDate, dbo_WOHeader.RequiredDate,
IIf(IsNull([dbo_WOHeader].[StartDate]),"",Format(PrevWorkingDay([dbo_WOHeader].[StartDate],5,2),"mm/dd/yyyy")) AS PrevDate
FROM dbo_WOHeader
WHERE (((dbo_WOHeader.ClosedFlag)=0) AND
((IIf(IsNull([dbo_WOHeader].[StartDate]),"",
Format(PrevWorkingDay([dbo_WOHeader].[StartDate],5,2),"mm/dd/yyyy")))>=12/25/2007));

Here is function "PrevWorkingDay" :

Public Function PrevWorkingDay(FromThisDate As Date, WorkingDays As Integer,
MinusDays As Integer) As Date

[snip]

That is comparing a text string that looks like a date to
the expression 12 divided by 25 divided by 2007, which
should either always pass or give a type mismatch error.

Adding the # signs is a step in the right direction, but you
are still comparinf a text string to a date.

You should just get rid of the Format function that is
turning your adjusted start date into a string. While you
are at it, the query should not return a zero length string
when start date is null. The result of the IIf needs to be
either True or False, in this case I think it should return
False.

I think this is how you should write the Where clause:

WHERE ClosedFlag=0 AND IIf(StartDate Is Null, False,
PrevWorkingDay(StartDate,5,2)>=#12/25/2007#)

 

[Samantha]

Works like a charm! Thank you so much, Marshall!
To make it more flexible for the end user, I set the dates with parameters,
by enclosing the parameters with CDate function, since the ## around the
parameter gave an error:

=CDate([Start Date]) And <= CDate([End Date])

Marshall, let me know if there is an easier way to do this. Appreciate so
much for your response and time!

Samantha

I have used one of the methods (via a function) posted on this forum to
calculate a date that is 2 days prior to the start date. I called out this
function "PrevWorkingDay" from a query. The date calculation appears to be
ok.
The problem that I am having is setting a date parameter base on this
PrevDate on the query. The PrevDate does not seem to be a acting like a date
should be. When the date parameter is >=12/25/2007, there is no result.
When the date parameter is >=#12/25/2007#, the result is only for dates

=12/25/2007 but excludes 2008 (there are supposed to have 2008 data).

Any help is very much appreciated, as I've spent days on this already!

Here is the problematic query:

SELECT dbo_WOHeader.WONumber, dbo_WOHeader.ClosedFlag,
dbo_WOHeader.StartDate, dbo_WOHeader.RequiredDate,
IIf(IsNull([dbo_WOHeader].[StartDate]),"",Format(PrevWorkingDay([dbo_WOHeader].[StartDate],5,2),"mm/dd/yyyy")) AS PrevDate
FROM dbo_WOHeader
WHERE (((dbo_WOHeader.ClosedFlag)=0) AND
((IIf(IsNull([dbo_WOHeader].[StartDate]),"",
Format(PrevWorkingDay([dbo_WOHeader].[StartDate],5,2),"mm/dd/yyyy")))>=12/25/2007));

Here is function "PrevWorkingDay" :

Public Function PrevWorkingDay(FromThisDate As Date, WorkingDays As Integer,
MinusDays As Integer) As Date

[snip]

That is comparing a text string that looks like a date to
the expression 12 divided by 25 divided by 2007, which
should either always pass or give a type mismatch error.

Adding the # signs is a step in the right direction, but you
are still comparinf a text string to a date.

You should just get rid of the Format function that is
turning your adjusted start date into a string. While you
are at it, the query should not return a zero length string
when start date is null. The result of the IIf needs to be
either True or False, in this case I think it should return
False.

I think this is how you should write the Where clause:

WHERE ClosedFlag=0 AND IIf(StartDate Is Null, False,
PrevWorkingDay(StartDate,5,2)>=#12/25/2007#)

 

[Marshall Barton]

Works like a charm! Thank you so much, Marshall!
To make it more flexible for the end user, I set the dates with parameters,
by enclosing the parameters with CDate function, since the ## around the
parameter gave an error:

=CDate([Start Date]) And <= CDate([End Date])

Marshall, let me know if there is an easier way to do this. Appreciate so
much for your response and time!

That looks pretty easy to me.

Using CDate in this situation is probably better because
CDate uses the Windows regional settings for date formats.