Date Criteria problem

  • Thread starter Thread starter AHopper
  • Start date Start date
A

AHopper

I have a report "rptRegisteredUniqueWeightSkidReport"
based on a table "tblRegisteredAndUniqueLabels" that has
a "Date/Time" field named "DatePacked".
The "Date/Time" field is in the data source of the report
and it prints in the detail section.
From a command button on a form
named "RegisteredUniqueWeightDialogBox" I want to print
the report. I want to give users options based on what
information they input. I have three text
boxes "JobNumber", "ReportDate", and "Shift". Report date
is a combo box with drop down list that has as it's
source the "DatePacked" field in
the "tblRegisteredAndUniqueLabels" table.
I have the following code in the On Click event of the
command button.

I have given five ways to print but only two print the
report. The reports that include "ReportDate" as a
criteria do not print. I have put in capital letters NOT
PRINT in the before code that does not print a report. I
have added addition information if there are MsgBoxes
etc. that appear.

Dim stDocName As String
Dim ReportDateCriteria As String

stDocName = "rptRegisteredUniqueWeightSkidReport"
With Me
' If no information msgbox and exit'
If IsNull(.JobNumber) And IsNull(.ReportDate) And IsNull
(.Shift) Then
Answer = MsgBox("To continue make a selection.", vbOKOnly)
If Answer = vbOK Then Exit Sub
End If
If .JobNumber = 0 And .ReportDate = 0 And .Shift = 0 Then
Answer = MsgBox("To continue make a selection.", vbOKOnly)
If Answer = vbOK Then Exit Sub
End If

Code below PRINTS.
'Print whole job or exit'
If IsNull(.Shift) And IsNull(.ReportDate) Then
Answer = MsgBox("Do you want to print a report for the
whole job?", vbYesNo + vbQuestion)
If Answer = vbYes Then
DoCmd.Hourglass True
DoCmd.Echo False
DoCmd.OpenReport stDocName, acNormal, , "[JobNumber]=
[Forms]![RegisteredUniqueWeightDialogBox]![JobNumber]"
DoCmd.Hourglass False
DoCmd.Echo True
Exit Sub
End If
If Answer = vbNo Then
Answer = MsgBox("To continue select a ""Date""
and/or ""Shift"".", vbOKOnly)
If Answer = vbOK Then Exit Sub
End If
End If

Code below does NOT PRINT - MsgBox "Enter Parameter
Value" appears requesting "ReportDateCriteria" when I
enter 09/02/2004 "DatePacked" I know is in the table I
receive the following message:
The expression is typed incorrectly, or it is too complex
to be evaluated. For example, a numeric expression may
contain too many complicated elements. Try simplifying
the expression by assigning pars of the expression to
variables. The my application freezes and I have to shut
it down.
If I enter 9022004 into the MsgBox "Enter Parameter
Value" "ReportDateCriteria" the report prints but show no
records.
If I cancel the MsgBox "Enter Parameter
Value" "ReportDateCriteria" a message "OpenReport action
cancelled appears and the application freezes.

'Print report for day, shift and job'
If IsDate(.ReportDate) And .Shift >= 1 And .JobNumber >=
1 Then
ReportDateCriteria = " And [DatePacked] = " & Format
(.ReportDate, "\#mm/dd/yyyy\#")
DoCmd.Hourglass True
DoCmd.Echo False
DoCmd.OpenReport stDocName, acNormal, , "[JobNumber]=
[Forms]![RegisteredUniqueWeightDialogBox]![JobNumber] And
[Shift]=[Forms]![RegisteredUniqueWeightDialogBox]![Shift]
& ReportDateCriteria & "
DoCmd.Hourglass False
DoCmd.Echo True
Exit Sub
End If

Code below does NOT PRINT - seems to be no response
'Print report for day and shift or exit'
If IsDate(.ReportDate) And .Shift >= 1 And .JobNumber < 1
Then
Answer = MsgBox("Do you want to print a shift report for
All Jobs on the selected date?", vbYesNo + vbQuestion)
If Answer = vbYes Then
ReportDateCriteria = " And [DatePacked] = " & Format
(.ReportDate, "\#mm/dd/yyyy\#")
DoCmd.Hourglass True
DoCmd.Echo False
DoCmd.OpenReport stDocName, acNormal, , "[Shift]=
[Forms]![RegisteredUniqueWeightDialogBox]![Shift] &
ReportDateCriteria & "
DoCmd.Hourglass False
DoCmd.Echo True
Exit Sub
End If
If Answer = vbNo Then
Answer = MsgBox("To continue make another
selection.", vbOKOnly)
If Answer = vbOK Then Exit Sub
End If
End If

Code below does NOT PRINT - seems to be no response
"Print report for day or exit'
If IsDate(.ReportDate) And IsNull(.Shift) And IsNull
(.JobNumber) Then
Answer = MsgBox("Do you want to print a day report for
all jobs on the selected date?")
If Answer = vbYes Then
ReportDateCriteria = " [DatePacked] = " & Format
(.ReportDate, "\#mm/dd/yyyy\#")
DoCmd.Hourglass True
DoCmd.Echo False
DoCmd.OpenReport stDocName, acNormal, , " &
ReportDateCriteria "
DoCmd.Hourglass False
DoCmd.Echo True
Exit Sub
End If
If Answer = vbNo Then
Answer = MsgBox("To continue make another
selection.", vbOKOnly)
If Answer = vbOK Then Exit Sub
End If
End If

Code below PRINTS
"Date and/or Job is required - exit'
If IsNull(.ReportDate) And .Shift >= 1 And IsNull
(.JobNumber) Then
Answer = MsgBox("To continue enter ""Date""
and/or ""Job Number"".", vbOKOnly)
If Answer = vbOK Then Exit Sub
End If
'Print report for shift and job or exit'
If IsNull(.ReportDate) And .Shift >= 1 And .JobNumber >=
1 Then
Answer = MsgBox("Do you want to print a report for All
Shifts of the selected job?", vbYesNo + vbQuestion)
If Answer = vbYes Then
DoCmd.Hourglass True
DoCmd.Echo False
DoCmd.OpenReport stDocName, acNormal, , "[JobNumber]=
[Forms]![RegisteredUniqueWeightDialogBox]![JobNumber] And
[Shift]=[Forms]![RegisteredUniqueWeightDialogBox]![Shift]"
DoCmd.Hourglass False
DoCmd.Echo True
Exit Sub
End If
If Answer = vbNo Then
Answer = MsgBox("To continue make another
selection.", vbOKOnly)
If Answer = vbOK Then Exit Sub
End If
End If
End With


Thank you in advance for your help

Allan
 
You put too much inside the quotes for the where condition
argument. See suggestions inline.
--
Marsh
MVP [MS Access]


I have a report "rptRegisteredUniqueWeightSkidReport"
based on a table "tblRegisteredAndUniqueLabels" that has
a "Date/Time" field named "DatePacked".
The "Date/Time" field is in the data source of the report
and it prints in the detail section.
From a command button on a form
named "RegisteredUniqueWeightDialogBox" I want to print
the report. I want to give users options based on what
information they input. I have three text
boxes "JobNumber", "ReportDate", and "Shift". Report date
is a combo box with drop down list that has as it's
source the "DatePacked" field in
the "tblRegisteredAndUniqueLabels" table.
I have the following code in the On Click event of the
command button.

I have given five ways to print but only two print the
report. The reports that include "ReportDate" as a
criteria do not print. I have put in capital letters NOT
PRINT in the before code that does not print a report. I
have added addition information if there are MsgBoxes
etc. that appear.

Dim stDocName As String
Dim ReportDateCriteria As String

stDocName = "rptRegisteredUniqueWeightSkidReport"
With Me
' If no information msgbox and exit'
If IsNull(.JobNumber) And IsNull(.ReportDate) And IsNull
(.Shift) Then
Answer = MsgBox("To continue make a selection.", vbOKOnly)
If Answer = vbOK Then Exit Sub
End If
If .JobNumber = 0 And .ReportDate = 0 And .Shift = 0 Then
Answer = MsgBox("To continue make a selection.", vbOKOnly)
If Answer = vbOK Then Exit Sub
End If

Code below PRINTS.
'Print whole job or exit'
If IsNull(.Shift) And IsNull(.ReportDate) Then
Answer = MsgBox("Do you want to print a report for the
whole job?", vbYesNo + vbQuestion)
If Answer = vbYes Then
DoCmd.Hourglass True
DoCmd.Echo False
DoCmd.OpenReport stDocName, acNormal, , "[JobNumber]=
[Forms]![RegisteredUniqueWeightDialogBox]![JobNumber]"
DoCmd.Hourglass False
DoCmd.Echo True
Exit Sub
End If
If Answer = vbNo Then
Answer = MsgBox("To continue select a ""Date""
and/or ""Shift"".", vbOKOnly)
If Answer = vbOK Then Exit Sub
End If
End If

Code below does NOT PRINT - MsgBox "Enter Parameter
Value" appears requesting "ReportDateCriteria" when I
enter 09/02/2004 "DatePacked" I know is in the table I
receive the following message:
The expression is typed incorrectly, or it is too complex
to be evaluated. For example, a numeric expression may
contain too many complicated elements. Try simplifying
the expression by assigning pars of the expression to
variables. The my application freezes and I have to shut
it down.
If I enter 9022004 into the MsgBox "Enter Parameter
Value" "ReportDateCriteria" the report prints but show no
records.
If I cancel the MsgBox "Enter Parameter
Value" "ReportDateCriteria" a message "OpenReport action
cancelled appears and the application freezes.

'Print report for day, shift and job'
If IsDate(.ReportDate) And .Shift >= 1 And .JobNumber >=
1 Then
ReportDateCriteria = " And [DatePacked] = " & Format
(.ReportDate, "\#mm/dd/yyyy\#")
DoCmd.Hourglass True
DoCmd.Echo False
DoCmd.OpenReport stDocName, acNormal, , "[JobNumber]=
[Forms]![RegisteredUniqueWeightDialogBox]![JobNumber] And
[Shift]=[Forms]![RegisteredUniqueWeightDialogBox]![Shift]
& ReportDateCriteria & "
Click to expand...

---------------------------------------------------------
DoCmd.OpenReport stDocName, acNormal, , "[JobNumber]=
[Forms]![RegisteredUniqueWeightDialogBox]![JobNumber] And
[Shift]=[Forms]![RegisteredUniqueWeightDialogBox]![Shift] "
& ReportDateCriteria
---------------------------------------------------------
DoCmd.Hourglass False
DoCmd.Echo True
Exit Sub
End If

Code below does NOT PRINT - seems to be no response
'Print report for day and shift or exit'
If IsDate(.ReportDate) And .Shift >= 1 And .JobNumber < 1
Then
Answer = MsgBox("Do you want to print a shift report for
All Jobs on the selected date?", vbYesNo + vbQuestion)
If Answer = vbYes Then
ReportDateCriteria = " And [DatePacked] = " & Format
(.ReportDate, "\#mm/dd/yyyy\#")
DoCmd.Hourglass True
DoCmd.Echo False
DoCmd.OpenReport stDocName, acNormal, , "[Shift]=
[Forms]![RegisteredUniqueWeightDialogBox]![Shift] &
ReportDateCriteria & "
Click to expand...

---------------------------------------------------------
DoCmd.OpenReport stDocName, acNormal, , "[Shift]=
[Forms]![RegisteredUniqueWeightDialogBox]![Shift] " &
ReportDateCriteria
---------------------------------------------------------
DoCmd.Hourglass False
DoCmd.Echo True
Exit Sub
End If
If Answer = vbNo Then
Answer = MsgBox("To continue make another
selection.", vbOKOnly)
If Answer = vbOK Then Exit Sub
End If
End If

Code below does NOT PRINT - seems to be no response
"Print report for day or exit'
If IsDate(.ReportDate) And IsNull(.Shift) And IsNull
(.JobNumber) Then
Answer = MsgBox("Do you want to print a day report for
all jobs on the selected date?")
If Answer = vbYes Then
ReportDateCriteria = " [DatePacked] = " & Format
(.ReportDate, "\#mm/dd/yyyy\#")
DoCmd.Hourglass True
DoCmd.Echo False
DoCmd.OpenReport stDocName, acNormal, , " &
ReportDateCriteria "
Click to expand...

---------------------------------------------------------
DoCmd.OpenReport stDocName, acNormal, , ReportDateCriteria
---------------------------------------------------------
DoCmd.Hourglass False
DoCmd.Echo True
Exit Sub
End If
If Answer = vbNo Then
Answer = MsgBox("To continue make another
selection.", vbOKOnly)
If Answer = vbOK Then Exit Sub
End If
End If

Code below PRINTS
"Date and/or Job is required - exit'
If IsNull(.ReportDate) And .Shift >= 1 And IsNull
(.JobNumber) Then
Answer = MsgBox("To continue enter ""Date""
and/or ""Job Number"".", vbOKOnly)
If Answer = vbOK Then Exit Sub
End If
'Print report for shift and job or exit'
If IsNull(.ReportDate) And .Shift >= 1 And .JobNumber >=
1 Then
Answer = MsgBox("Do you want to print a report for All
Shifts of the selected job?", vbYesNo + vbQuestion)
If Answer = vbYes Then
DoCmd.Hourglass True
DoCmd.Echo False
DoCmd.OpenReport stDocName, acNormal, , "[JobNumber]=
[Forms]![RegisteredUniqueWeightDialogBox]![JobNumber] And
[Shift]=[Forms]![RegisteredUniqueWeightDialogBox]![Shift]"
DoCmd.Hourglass False
DoCmd.Echo True
Exit Sub
End If
If Answer = vbNo Then
Answer = MsgBox("To continue make another
selection.", vbOKOnly)
If Answer = vbOK Then Exit Sub
End If
End If
End With


Thank you in advance for your help

Allan
Click to expand...
 
Marsh:
Because of the Labor day weekend I was not able to respond
your reply, I have reposted my original and your reply
under Date Criteria problem again. I did try your
suggetions but still have not been able to get the date
criteria to print a report. The report does not respond
even if I try to print with the "DatePacked" criteria
alone.
-----Original Message-----
You put too much inside the quotes for the where condition
argument. See suggestions inline.
--
Marsh
MVP [MS Access]


I have a report "rptRegisteredUniqueWeightSkidReport"
based on a table "tblRegisteredAndUniqueLabels" that has
a "Date/Time" field named "DatePacked".
The "Date/Time" field is in the data source of the report
and it prints in the detail section.
From a command button on a form
named "RegisteredUniqueWeightDialogBox" I want to print
the report. I want to give users options based on what
information they input. I have three text
boxes "JobNumber", "ReportDate", and "Shift". Report date
is a combo box with drop down list that has as it's
source the "DatePacked" field in
the "tblRegisteredAndUniqueLabels" table.
I have the following code in the On Click event of the
command button.

I have given five ways to print but only two print the
report. The reports that include "ReportDate" as a
criteria do not print. I have put in capital letters NOT
PRINT in the before code that does not print a report. I
have added addition information if there are MsgBoxes
etc. that appear.

Dim stDocName As String
Dim ReportDateCriteria As String

stDocName = "rptRegisteredUniqueWeightSkidReport"
With Me
' If no information msgbox and exit'
If IsNull(.JobNumber) And IsNull(.ReportDate) And IsNull
(.Shift) Then
Answer = MsgBox("To continue make a selection.", vbOKOnly)
If Answer = vbOK Then Exit Sub
End If
If .JobNumber = 0 And .ReportDate = 0 And .Shift = 0 Then
Answer = MsgBox("To continue make a selection.", vbOKOnly)
If Answer = vbOK Then Exit Sub
End If

Code below PRINTS.
'Print whole job or exit'
If IsNull(.Shift) And IsNull(.ReportDate) Then
Answer = MsgBox("Do you want to print a report for the
whole job?", vbYesNo + vbQuestion)
If Answer = vbYes Then
DoCmd.Hourglass True
DoCmd.Echo False
DoCmd.OpenReport stDocName, acNormal, , "[JobNumber]=
[Forms]![RegisteredUniqueWeightDialogBox]![JobNumber]"
DoCmd.Hourglass False
DoCmd.Echo True
Exit Sub
End If
If Answer = vbNo Then
Answer = MsgBox("To continue select a ""Date""
and/or ""Shift"".", vbOKOnly)
If Answer = vbOK Then Exit Sub
End If
End If

Code below does NOT PRINT - MsgBox "Enter Parameter
Value" appears requesting "ReportDateCriteria" when I
enter 09/02/2004 "DatePacked" I know is in the table I
receive the following message:
The expression is typed incorrectly, or it is too complex
to be evaluated. For example, a numeric expression may
contain too many complicated elements. Try simplifying
the expression by assigning pars of the expression to
variables. The my application freezes and I have to shut
it down.
If I enter 9022004 into the MsgBox "Enter Parameter
Value" "ReportDateCriteria" the report prints but show no
records.
If I cancel the MsgBox "Enter Parameter
Value" "ReportDateCriteria" a message "OpenReport action
cancelled appears and the application freezes.

'Print report for day, shift and job'
If IsDate(.ReportDate) And .Shift >= 1 And .JobNumber >=
1 Then
ReportDateCriteria = " And [DatePacked] = " & Format
(.ReportDate, "\#mm/dd/yyyy\#")
DoCmd.Hourglass True
DoCmd.Echo False
DoCmd.OpenReport stDocName, acNormal, , "[JobNumber]=
[Forms]![RegisteredUniqueWeightDialogBox]![JobNumber] And
[Shift]=[Forms]![RegisteredUniqueWeightDialogBox]! [Shift]
& ReportDateCriteria & "
Click to expand...

---------------------------------------------------------
DoCmd.OpenReport stDocName, acNormal, , "[JobNumber]=
[Forms]![RegisteredUniqueWeightDialogBox]![JobNumber] And
[Shift]=[Forms]![RegisteredUniqueWeightDialogBox]! [Shift] "
& ReportDateCriteria
---------------------------------------------------------
DoCmd.Hourglass False
DoCmd.Echo True
Exit Sub
End If

Code below does NOT PRINT - seems to be no response
'Print report for day and shift or exit'
If IsDate(.ReportDate) And .Shift >= 1 And .JobNumber < 1
Then
Answer = MsgBox("Do you want to print a shift report for
All Jobs on the selected date?", vbYesNo + vbQuestion)
If Answer = vbYes Then
ReportDateCriteria = " And [DatePacked] = " & Format
(.ReportDate, "\#mm/dd/yyyy\#")
DoCmd.Hourglass True
DoCmd.Echo False
DoCmd.OpenReport stDocName, acNormal, , "[Shift]=
[Forms]![RegisteredUniqueWeightDialogBox]![Shift] &
ReportDateCriteria & "
Click to expand...

---------------------------------------------------------
DoCmd.OpenReport stDocName, acNormal, , "[Shift]=
[Forms]![RegisteredUniqueWeightDialogBox]![Shift] " &
ReportDateCriteria
---------------------------------------------------------
DoCmd.Hourglass False
DoCmd.Echo True
Exit Sub
End If
If Answer = vbNo Then
Answer = MsgBox("To continue make another
selection.", vbOKOnly)
If Answer = vbOK Then Exit Sub
End If
End If

Code below does NOT PRINT - seems to be no response
"Print report for day or exit'
If IsDate(.ReportDate) And IsNull(.Shift) And IsNull
(.JobNumber) Then
Answer = MsgBox("Do you want to print a day report for
all jobs on the selected date?")
If Answer = vbYes Then
ReportDateCriteria = " [DatePacked] = " & Format
(.ReportDate, "\#mm/dd/yyyy\#")
DoCmd.Hourglass True
DoCmd.Echo False
DoCmd.OpenReport stDocName, acNormal, , " &
ReportDateCriteria "
Click to expand...

---------------------------------------------------------
DoCmd.OpenReport stDocName, acNormal, , ReportDateCriteria
---------------------------------------------------------
DoCmd.Hourglass False
DoCmd.Echo True
Exit Sub
End If
If Answer = vbNo Then
Answer = MsgBox("To continue make another
selection.", vbOKOnly)
If Answer = vbOK Then Exit Sub
End If
End If

Code below PRINTS
"Date and/or Job is required - exit'
If IsNull(.ReportDate) And .Shift >= 1 And IsNull
(.JobNumber) Then
Answer = MsgBox("To continue enter ""Date""
and/or ""Job Number"".", vbOKOnly)
If Answer = vbOK Then Exit Sub
End If
'Print report for shift and job or exit'
If IsNull(.ReportDate) And .Shift >= 1 And .JobNumber >=
1 Then
Answer = MsgBox("Do you want to print a report for All
Shifts of the selected job?", vbYesNo + vbQuestion)
If Answer = vbYes Then
DoCmd.Hourglass True
DoCmd.Echo False
DoCmd.OpenReport stDocName, acNormal, , "[JobNumber]=
[Forms]![RegisteredUniqueWeightDialogBox]![JobNumber] And
[Shift]=[Forms]![RegisteredUniqueWeightDialogBox]! [Shift]"
DoCmd.Hourglass False
DoCmd.Echo True
Exit Sub
End If
If Answer = vbNo Then
Answer = MsgBox("To continue make another
selection.", vbOKOnly)
If Answer = vbOK Then Exit Sub
End If
End If
End With


Thank you in advance for your help

Allan
Click to expand...

.
Click to expand...
 
Back
Top