Date Criteria problem

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
 
M

Marshall Barton

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...
 
G

Guest

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Report not printing 3
Report opens and visible 1
Error: Compile Error,ByRef argument type mismatch 0
Access Choosing Printer VBA Access 0
Is Null in a Report 2
Print records within and including range of dates 5
Print code stopped working 3
Data Check not checking 14

Top