StartDate, EndDate

[AHopper]

On a form "AnalysisDialogBox" I have two unbound text
boxes "StartDate" and "EndDate". They both have no format
but they have an input mask 00/00/00.
In detail section of a report "AnalysisRevised" On Print
event I have put the following

Dim ReportStartDate As Date
Dim ReportEndDate As Date

ReportStartDate = Nz([Forms]![AnalysisDialogBox]!
[StartDate])
ReportEndDate = Nz([Forms]![AnalysisDialogBox]![EndDate])

If ReportStartDate >= 1 And ReportEndDate >= 1 Then
OnePositionPackaged = (Nz(DSum("(Nz
([GoodOnePack]))", "OneGPackData", "[JobNumber]=" &
Me.JobNumber & "And [SkidNumber]=" & Me.SkidNumber & "And
[PackDate]>=" & ReportStartDate & "And [PackDate]<=" &
ReportEndDate)))
End If

"GoodOnePack", "JobNumber", "SkidNumber" and "PackDate"
are fields in a table "OneGPackData".

The PackDate field is set to Date/Time and when stored has
Now information stored in it.

Me. references the report "AnalysisRevised". JobNumber and
SkidNumber are textboxes on the report.

When I print the report I receive no information from
the "OneGPackData" table.

I have tried adding # before the #ReportStartDate# and
#ReportEndDate# but that was not accepted.

Thanks for your help.

Allan

 

[Dirk Goldgar]

On a form "AnalysisDialogBox" I have two unbound text
boxes "StartDate" and "EndDate". They both have no format
but they have an input mask 00/00/00.
In detail section of a report "AnalysisRevised" On Print
event I have put the following

Dim ReportStartDate As Date
Dim ReportEndDate As Date

ReportStartDate = Nz([Forms]![AnalysisDialogBox]!
[StartDate])
ReportEndDate = Nz([Forms]![AnalysisDialogBox]![EndDate])

If ReportStartDate >= 1 And ReportEndDate >= 1 Then
OnePositionPackaged = (Nz(DSum("(Nz
([GoodOnePack]))", "OneGPackData", "[JobNumber]=" &
Me.JobNumber & "And [SkidNumber]=" & Me.SkidNumber & "And
[PackDate]>=" & ReportStartDate & "And [PackDate]<=" &
ReportEndDate)))
End If

"GoodOnePack", "JobNumber", "SkidNumber" and "PackDate"
are fields in a table "OneGPackData".

The PackDate field is set to Date/Time and when stored has
Now information stored in it.

Me. references the report "AnalysisRevised". JobNumber and
SkidNumber are textboxes on the report.

When I print the report I receive no information from
the "OneGPackData" table.

I have tried adding # before the #ReportStartDate# and
#ReportEndDate# but that was not accepted.

Thanks for your help.

Allan

I dislike using input masks in date fields, as it restricts the user's
input unnecessarily without in fact enforcing that a valid date is
entered. I'd rather apply a date *Format* instead, since then Access
will require that the user's entry must at least be able to be
interpreted as a valid date.

You actually have two problems here. One is a matter of forcing Access
to correctly interpret the control values as dates, and the other is
that, since you're storing Now() rather than Date() in the [PackDate]
field, you have to be more careful when comparing that to the
ReportEndDate. That's because, for example, #1/22/04 1:00AM# is *not*
<= #1/22/04#.

I suggest you change your code as follows:

'----- start of revised code -----
Dim strStartDateCriteria As String
Dim strEndDateCriteria As String

' Note: if either start or end date is null, the data
' won't be filtered by that date.

With [Forms]![AnalysisDialogBox]

If IsDate(!StartDate) Then
strStartDateCriteria = " And [PackDate] >= " & _
Format(!StartDate, "\#mm/dd/yyyy\#")
End If

If IsDate(!EndDate) Then
strEndDateCriteria = " And [PackDate] < " & _
Format(DateAdd("d", 1, !EndDate), "\#mm/dd/yyyy\#")
End If

End With

OnePositionPackaged = _
Nz(DSum( _
"(Nz([GoodOnePack]))", _
"OneGPackData", _
"[JobNumber]=" & Me.JobNumber & _
" And [SkidNumber]=" & Me.SkidNumber & _
strStartDateCriteria & strEndDateCriteria))

'----- end of revised code -----

Note that the above does in fact include a functional revision to your
code. As written here, it allows the user to specify either date as an
end-point and leave the other open, or to leave both open and include
all data regardless of date. This is just my suggestion; you can just
as easily set up a test to bypass the lookup if either date is null.

 

[AHopper]

Dirk

I took all of your suggestions. I formated the text boxes
for date and used the code. It worked first time. The code
is so much more efficient than the way I was trying to do
it.

Thank you so very much.

Allan

-----Original Message-----

On a form "AnalysisDialogBox" I have two unbound text
boxes "StartDate" and "EndDate". They both have no format
but they have an input mask 00/00/00.
In detail section of a report "AnalysisRevised" On Print
event I have put the following

Dim ReportStartDate As Date
Dim ReportEndDate As Date

ReportStartDate = Nz([Forms]![AnalysisDialogBox]!
[StartDate])
ReportEndDate = Nz([Forms]![AnalysisDialogBox]! [EndDate])

If ReportStartDate >= 1 And ReportEndDate >= 1 Then
OnePositionPackaged = (Nz(DSum("(Nz
([GoodOnePack]))", "OneGPackData", "[JobNumber]=" &
Me.JobNumber & "And [SkidNumber]=" & Me.SkidNumber & "And
[PackDate]>=" & ReportStartDate & "And [PackDate]<=" &
ReportEndDate)))
End If

"GoodOnePack", "JobNumber", "SkidNumber" and "PackDate"
are fields in a table "OneGPackData".

The PackDate field is set to Date/Time and when stored has
Now information stored in it.

Me. references the report "AnalysisRevised". JobNumber and
SkidNumber are textboxes on the report.

When I print the report I receive no information from
the "OneGPackData" table.

I have tried adding # before the #ReportStartDate# and
#ReportEndDate# but that was not accepted.

Thanks for your help.

Allan

I dislike using input masks in date fields, as it restricts the user's
input unnecessarily without in fact enforcing that a valid date is
entered. I'd rather apply a date *Format* instead, since then Access
will require that the user's entry must at least be able to be
interpreted as a valid date.

You actually have two problems here. One is a matter of forcing Access
to correctly interpret the control values as dates, and the other is
that, since you're storing Now() rather than Date() in the [PackDate]
field, you have to be more careful when comparing that to the
ReportEndDate. That's because, for example, #1/22/04 1:00AM# is *not*
<= #1/22/04#.

I suggest you change your code as follows:

'----- start of revised code -----
Dim strStartDateCriteria As String
Dim strEndDateCriteria As String

' Note: if either start or end date is null, the data
' won't be filtered by that date.

With [Forms]![AnalysisDialogBox]

If IsDate(!StartDate) Then
strStartDateCriteria = " And [PackDate] >= " & _
Format(!StartDate, "\#mm/dd/yyyy\#")
End If

If IsDate(!EndDate) Then
strEndDateCriteria = " And [PackDate] < " & _
Format(DateAdd("d", 1, ! EndDate), "\#mm/dd/yyyy\#")
End If

End With

OnePositionPackaged = _
Nz(DSum( _
"(Nz([GoodOnePack]))", _
"OneGPackData", _
"[JobNumber]=" & Me.JobNumber & _
" And [SkidNumber]=" & Me.SkidNumber & _
strStartDateCriteria & strEndDateCriteria))

'----- end of revised code -----

Note that the above does in fact include a functional revision to your
code. As written here, it allows the user to specify either date as an
end-point and leave the other open, or to leave both open and include
all data regardless of date. This is just my suggestion; you can just
as easily set up a test to bypass the lookup if either date is null.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

 

[Dirk Goldgar]

Dirk

I took all of your suggestions. I formated the text boxes
for date and used the code. It worked first time. The code
is so much more efficient than the way I was trying to do
it.

Thank you so very much.

You're most welcome.