A
AHopper
Two Parts to this post.
Part One:
I have table with a "PackDate" date/time field. I want to use the date part
only of the field to select records for a report. "PackDate" is also a text
box on my report. I have been able to verify through the use of a MsgBox that
the code below is changing the text box "PackDate" to the correct
"ReportDate". The report is being grouped and sorted first on the
"ReportDate" and then on "Shift" (Shift has a group footer). I am trying to
get the total BatchCount by Shift. The following code is in On Print event of
the "Shift" footer.
Dim BatchCount As Double
Dim ReportDate As Date
ReportDate = DateValue(Me.PackDate)
BatchCount = (Nz(DSum("(Nz([BatchCount]))", "USPSOmegaTenBatch",
"[PackDate]=" & ReportDate & "And [Shift]=" & Me.[Shift])))
Me.ShiftBatchTotal = BatchCount
I get a total of 0 and I believe the problem is with the [PackDate] in the
table which is a date/time field. How can I use only the Date Part of this
field? I tried the following:
BatchCount = (Nz(DSum("(Nz([BatchCount]))", "USPSOmegaTenBatch",
"DateValue([PackDate])=" & ReportDate & "And [Shift]=" & Me.[Shift])))
Part Two:
I am at a point where getting information for reports is more demanding. I
have been told that a good way to simplify writing a complicated report (data
from fields in several tables) is by creating a temporary table that becomes
the source of information for the report and then deleting the table each
time the report is finished.
Is this true?
If so I would like to learn how to do this.
In any case I would like to learn how to create, use and delete temporary
tables.
Thank you in advance for your help
Allan
Part One:
I have table with a "PackDate" date/time field. I want to use the date part
only of the field to select records for a report. "PackDate" is also a text
box on my report. I have been able to verify through the use of a MsgBox that
the code below is changing the text box "PackDate" to the correct
"ReportDate". The report is being grouped and sorted first on the
"ReportDate" and then on "Shift" (Shift has a group footer). I am trying to
get the total BatchCount by Shift. The following code is in On Print event of
the "Shift" footer.
Dim BatchCount As Double
Dim ReportDate As Date
ReportDate = DateValue(Me.PackDate)
BatchCount = (Nz(DSum("(Nz([BatchCount]))", "USPSOmegaTenBatch",
"[PackDate]=" & ReportDate & "And [Shift]=" & Me.[Shift])))
Me.ShiftBatchTotal = BatchCount
I get a total of 0 and I believe the problem is with the [PackDate] in the
table which is a date/time field. How can I use only the Date Part of this
field? I tried the following:
BatchCount = (Nz(DSum("(Nz([BatchCount]))", "USPSOmegaTenBatch",
"DateValue([PackDate])=" & ReportDate & "And [Shift]=" & Me.[Shift])))
Part Two:
I am at a point where getting information for reports is more demanding. I
have been told that a good way to simplify writing a complicated report (data
from fields in several tables) is by creating a temporary table that becomes
the source of information for the report and then deleting the table each
time the report is finished.
Is this true?
If so I would like to learn how to do this.
In any case I would like to learn how to create, use and delete temporary
tables.
Thank you in advance for your help
Allan