Combine combo boxes with date range

Hi,

I created my first dialog box that includes two combo boxes, one named
cboCategory and another one named cboGroup. I also created a command button
that opens a report. My VBA is as follows:

Private Sub cmdOK_Click()
DoCmd.OpenReport "rptLaborODC", acViewPreview, , , acNormal
End Sub

This worked out for me but then I tried to add a date range. I found some
instructions from Allen Browne’s website for “Limiting a report to a date
range� and followed them which were very good because I almost got it to
work. It’s asking me for parameters related to the above dialog box for
cboCategory and cboGroup.

I’d like to combine the two. Is this possible with what I’ve already
created or should I be doing something else? Can anyone help me with this
please?

Here is Allen’s code:
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "rptLaborODC"
strDateField = "[TransDate]"
lngView = acViewPreview

If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " = " & Format(Me.txtStartDate,
strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If


If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If


Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub

Thank you very much in advance,
Kay

Kay -

To update the code, you first fill in the strWhere with the values from the
forms combo boxes, then add the dates as needed. I have updated this, but
you will need to change my made-up names for your real ones:


Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "rptLaborODC"
strDateField = "[TransDate]"
lngView = acViewPreview

' start here building the WHERE clause - substitute your real names here.
strWhere = "Category = '" & Forms!formname!cboCatName.column(0) & "' AND
[Group] = '" & _
Forms!formname!cboGpName.column(0) & "'"

If IsDate(Me.txtStartDate) Then
If strWhere vbNullString Then 'updated this block
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " = " &
Format(Me.txtStartDate,
strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If


Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub

--
Daryl S


"AccessKay" wrote:

Hi,

I created my first dialog box that includes two combo boxes, one named
cboCategory and another one named cboGroup. I also created a command button
that opens a report. My VBA is as follows:

Private Sub cmdOK_Click()
DoCmd.OpenReport "rptLaborODC", acViewPreview, , , acNormal
End Sub

This worked out for me but then I tried to add a date range. I found some
instructions from Allen Browne’s website for “Limiting a report to a date
range� and followed them which were very good because I almost got it to
work. It’s asking me for parameters related to the above dialog box for
cboCategory and cboGroup.

I’d like to combine the two. Is this possible with what I’ve already
created or should I be doing something else? Can anyone help me with this
please?

Here is Allen’s code:
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "rptLaborODC"
strDateField = "[TransDate]"
lngView = acViewPreview

If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " = " & Format(Me.txtStartDate,
strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If


If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If


Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub

Thank you very much in advance,
Kay

Thanks Daryl. I’m very new to vba. I replaced what I thought was the
made-up names. In future posts, I’ll remember to state these names.
Anyways, I’ve got a lot of red related to the strWhere areas.

' start here building the WHERE clause - substitute your real names here.
strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & "'
AND "
[Group] = '" & _
Forms!DialogBox!cboGroup.column(0) & "'"

Here…I changed my form name to DialogBox and the name of my first combo
which is cboCategory. I did the same for Group. The text turns red at
[Group].
Then in the second block, you put a note to update. My text box is named
txtStartDate so I didn’t change this. I wasn’t sure if there was another
name in there that I needed to change.

If IsDate(Me.txtStartDate) Then
If strWhere vbNullString Then 'updated this block
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " = " &
Format(Me.txtStartDate,
strcJetDate) & ")"

The text goes red at strWhere = strWhere & “….

End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

Again at strWhere it goes red.

Then at the very bottom it’s red for MsgBox "Error " & Err.Number & ": " &
Err.Description,

Just to clarify…
Combo box 1: cboCategory
Combo box 2: cboGroup
Text box 1: txtStartDate
Text box 2: txtEndDate
Command Button: cmdPreview
Form name: DialogBox
Query name: LaborODC

I hope I have provided you with enough information. I really appreciate the
help!!!
Kay



"Daryl S" wrote:

Kay -

To update the code, you first fill in the strWhere with the values from the
forms combo boxes, then add the dates as needed. I have updated this, but
you will need to change my made-up names for your real ones:


Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "rptLaborODC"
strDateField = "[TransDate]"
lngView = acViewPreview

' start here building the WHERE clause - substitute your real names here.
strWhere = "Category = '" & Forms!formname!cboCatName.column(0) & "' AND
[Group] = '" & _
Forms!formname!cboGpName.column(0) & "'"

If IsDate(Me.txtStartDate) Then
If strWhere vbNullString Then 'updated this block
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " = " &
Format(Me.txtStartDate,
strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If


Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub

--
Daryl S


"AccessKay" wrote:

Hi,

I created my first dialog box that includes two combo boxes, one named
cboCategory and another one named cboGroup. I also created a command button
that opens a report. My VBA is as follows:

Private Sub cmdOK_Click()
DoCmd.OpenReport "rptLaborODC", acViewPreview, , , acNormal
End Sub

This worked out for me but then I tried to add a date range. I found some
instructions from Allen Browne’s website for “Limiting a report to a date
range� and followed them which were very good because I almost got it to
work. It’s asking me for parameters related to the above dialog box for
cboCategory and cboGroup.

I’d like to combine the two. Is this possible with what I’ve already
created or should I be doing something else? Can anyone help me with this
please?

Here is Allen’s code:
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "rptLaborODC"
strDateField = "[TransDate]"
lngView = acViewPreview

If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " = " & Format(Me.txtStartDate,
strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If


If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If


Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub

Thank you very much in advance,
Kay

AccessKay -

Yes, the copy/paste does add some line breaks...

In Access code, if your statement will take up more than one line, you will
need to tell access that the code continues on the next line. This is done
with an underscore character, but must be in a logical place (not within
double-quotes for example). So you can change the code by removing the line
feeds or by adding in more line continuation characters, or a combination of
the two. Like this:

strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & _
"' AND "[Group] = '" & _
Forms!DialogBox!cboGroup.column(0) & "'"

Hope that helps!

--
Daryl S


"AccessKay" wrote:

Thanks Daryl. I’m very new to vba. I replaced what I thought was the
made-up names. In future posts, I’ll remember to state these names.
Anyways, I’ve got a lot of red related to the strWhere areas.

' start here building the WHERE clause - substitute your real names here.
strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & "'
AND "
[Group] = '" & _
Forms!DialogBox!cboGroup.column(0) & "'"

Here…I changed my form name to DialogBox and the name of my first combo
which is cboCategory. I did the same for Group. The text turns red at
[Group].
Then in the second block, you put a note to update. My text box is named
txtStartDate so I didn’t change this. I wasn’t sure if there was another
name in there that I needed to change.

If IsDate(Me.txtStartDate) Then
If strWhere vbNullString Then 'updated this block
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " = " &
Format(Me.txtStartDate,
strcJetDate) & ")"

The text goes red at strWhere = strWhere & “….

End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

Again at strWhere it goes red.

Then at the very bottom it’s red for MsgBox "Error " & Err.Number & ": " &
Err.Description,

Just to clarify…
Combo box 1: cboCategory
Combo box 2: cboGroup
Text box 1: txtStartDate
Text box 2: txtEndDate
Command Button: cmdPreview
Form name: DialogBox
Query name: LaborODC

I hope I have provided you with enough information. I really appreciate the
help!!!
Kay



"Daryl S" wrote:

Kay -

To update the code, you first fill in the strWhere with the values from the
forms combo boxes, then add the dates as needed. I have updated this, but
you will need to change my made-up names for your real ones:


Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "rptLaborODC"
strDateField = "[TransDate]"
lngView = acViewPreview

' start here building the WHERE clause - substitute your real names here.
strWhere = "Category = '" & Forms!formname!cboCatName.column(0) & "' AND
[Group] = '" & _
Forms!formname!cboGpName.column(0) & "'"

If IsDate(Me.txtStartDate) Then
If strWhere vbNullString Then 'updated this block
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " = " &
Format(Me.txtStartDate,
strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If


Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub

--
Daryl S


"AccessKay" wrote:

Hi,

I created my first dialog box that includes two combo boxes, one named
cboCategory and another one named cboGroup. I also created a command button
that opens a report. My VBA is as follows:

Private Sub cmdOK_Click()
DoCmd.OpenReport "rptLaborODC", acViewPreview, , , acNormal
End Sub

This worked out for me but then I tried to add a date range. I found some
instructions from Allen Browne’s website for “Limiting a report to a date
range� and followed them which were very good because I almost got it to
work. It’s asking me for parameters related to the above dialog box for
cboCategory and cboGroup.

I’d like to combine the two. Is this possible with what I’ve already
created or should I be doing something else? Can anyone help me with this
please?

Here is Allen’s code:
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "rptLaborODC"
strDateField = "[TransDate]"
lngView = acViewPreview

If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " = " & Format(Me.txtStartDate,
strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If


If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If


Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub

Thank you very much in advance,
Kay

Daryl,
VERY useful to know and the red went away. The vba is running okay until it
gets to the end and then I get this Compile error: End If without block If.
It's this part he
Err_Handler:
If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " &
Err.Description, vbExclamation, "Cannot open report"
End If (Error message)
Resume Exit_Handler
End Sub

Can you help me fix this please?

Thanks again,
Kay



"Daryl S" wrote:

AccessKay -

Yes, the copy/paste does add some line breaks...

In Access code, if your statement will take up more than one line, you will
need to tell access that the code continues on the next line. This is done
with an underscore character, but must be in a logical place (not within
double-quotes for example). So you can change the code by removing the line
feeds or by adding in more line continuation characters, or a combination of
the two. Like this:

strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & _
"' AND "[Group] = '" & _
Forms!DialogBox!cboGroup.column(0) & "'"

Hope that helps!

--
Daryl S


"AccessKay" wrote:

Thanks Daryl. I’m very new to vba. I replaced what I thought was the
made-up names. In future posts, I’ll remember to state these names.
Anyways, I’ve got a lot of red related to the strWhere areas.

' start here building the WHERE clause - substitute your real names here.
strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & "'
AND "
[Group] = '" & _
Forms!DialogBox!cboGroup.column(0) & "'"

Here…I changed my form name to DialogBox and the name of my first combo
which is cboCategory. I did the same for Group. The text turns red at
[Group].
Then in the second block, you put a note to update. My text box is named
txtStartDate so I didn’t change this. I wasn’t sure if there was another
name in there that I needed to change.

If IsDate(Me.txtStartDate) Then
If strWhere vbNullString Then 'updated this block
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " = " &
Format(Me.txtStartDate,
strcJetDate) & ")"

The text goes red at strWhere = strWhere & “….

End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

Again at strWhere it goes red.

Then at the very bottom it’s red for MsgBox "Error " & Err.Number & ": " &
Err.Description,

Just to clarify…
Combo box 1: cboCategory
Combo box 2: cboGroup
Text box 1: txtStartDate
Text box 2: txtEndDate
Command Button: cmdPreview
Form name: DialogBox
Query name: LaborODC

I hope I have provided you with enough information. I really appreciate the
help!!!
Kay



"Daryl S" wrote:

Kay -

To update the code, you first fill in the strWhere with the values from the
forms combo boxes, then add the dates as needed. I have updated this, but
you will need to change my made-up names for your real ones:


Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "rptLaborODC"
strDateField = "[TransDate]"
lngView = acViewPreview

' start here building the WHERE clause - substitute your real names here.
strWhere = "Category = '" & Forms!formname!cboCatName.column(0) & "' AND
[Group] = '" & _
Forms!formname!cboGpName.column(0) & "'"

If IsDate(Me.txtStartDate) Then
If strWhere vbNullString Then 'updated this block
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " = " &
Format(Me.txtStartDate,
strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If


Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub

--
Daryl S


"AccessKay" wrote:

Hi,

I created my first dialog box that includes two combo boxes, one named
cboCategory and another one named cboGroup. I also created a command button
that opens a report. My VBA is as follows:

Private Sub cmdOK_Click()
DoCmd.OpenReport "rptLaborODC", acViewPreview, , , acNormal
End Sub

This worked out for me but then I tried to add a date range. I found some
instructions from Allen Browne’s website for “Limiting a report to a date
range� and followed them which were very good because I almost got it to
work. It’s asking me for parameters related to the above dialog box for
cboCategory and cboGroup.

I’d like to combine the two. Is this possible with what I’ve already
created or should I be doing something else? Can anyone help me with this
please?

Here is Allen’s code:
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "rptLaborODC"
strDateField = "[TransDate]"
lngView = acViewPreview

If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " = " & Format(Me.txtStartDate,
strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If


If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If


Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub

Thank you very much in advance,
Kay

AccessKay -

There are two ways to do the If/Then statements. If there is only one
statement to execute in the 'true' and optional 'else' clauses, then you can
put them on one line, without an End If:

If test Then result Else else result

The more common way allows for multiple statements to be executed in the
'true' case and in the optional 'else' case. This format requires the End If
(so it knows when the block of statements ends).

If test Then
result block
Else
else block
End If

Your statement could be either

If Err.Number 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation,
"Cannot open report"
End If '(Error message)

or (this would be all on one line - the copy/paste may not show it that way)

If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " &
Err.Description, vbExclamation, "Cannot open report"

--
Daryl S


"AccessKay" wrote:

Daryl,
VERY useful to know and the red went away. The vba is running okay until it
gets to the end and then I get this Compile error: End If without block If.
It's this part he
Err_Handler:
If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " &
Err.Description, vbExclamation, "Cannot open report"
End If (Error message)
Resume Exit_Handler
End Sub

Can you help me fix this please?

Thanks again,
Kay



"Daryl S" wrote:

AccessKay -

Yes, the copy/paste does add some line breaks...

In Access code, if your statement will take up more than one line, you will
need to tell access that the code continues on the next line. This is done
with an underscore character, but must be in a logical place (not within
double-quotes for example). So you can change the code by removing the line
feeds or by adding in more line continuation characters, or a combination of
the two. Like this:

strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & _
"' AND "[Group] = '" & _
Forms!DialogBox!cboGroup.column(0) & "'"

Hope that helps!

--
Daryl S


"AccessKay" wrote:

Thanks Daryl. I’m very new to vba. I replaced what I thought was the
made-up names. In future posts, I’ll remember to state these names.
Anyways, I’ve got a lot of red related to the strWhere areas.

' start here building the WHERE clause - substitute your real names here.
strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & "'
AND "
[Group] = '" & _
Forms!DialogBox!cboGroup.column(0) & "'"

Here…I changed my form name to DialogBox and the name of my first combo
which is cboCategory. I did the same for Group. The text turns red at
[Group].
Then in the second block, you put a note to update. My text box is named
txtStartDate so I didn’t change this. I wasn’t sure if there was another
name in there that I needed to change.

If IsDate(Me.txtStartDate) Then
If strWhere vbNullString Then 'updated this block
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " = " &
Format(Me.txtStartDate,
strcJetDate) & ")"

The text goes red at strWhere = strWhere & “….

End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

Again at strWhere it goes red.

Then at the very bottom it’s red for MsgBox "Error " & Err.Number & ": " &
Err.Description,

Just to clarify…
Combo box 1: cboCategory
Combo box 2: cboGroup
Text box 1: txtStartDate
Text box 2: txtEndDate
Command Button: cmdPreview
Form name: DialogBox
Query name: LaborODC

I hope I have provided you with enough information. I really appreciate the
help!!!
Kay



"Daryl S" wrote:

Kay -

To update the code, you first fill in the strWhere with the values from the
forms combo boxes, then add the dates as needed. I have updated this, but
you will need to change my made-up names for your real ones:


Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "rptLaborODC"
strDateField = "[TransDate]"
lngView = acViewPreview

' start here building the WHERE clause - substitute your real names here.
strWhere = "Category = '" & Forms!formname!cboCatName.column(0) & "' AND
[Group] = '" & _
Forms!formname!cboGpName.column(0) & "'"

If IsDate(Me.txtStartDate) Then
If strWhere vbNullString Then 'updated this block
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " = " &
Format(Me.txtStartDate,
strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If


Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub

--
Daryl S


"AccessKay" wrote:

Hi,

I created my first dialog box that includes two combo boxes, one named
cboCategory and another one named cboGroup. I also created a command button
that opens a report. My VBA is as follows:

Private Sub cmdOK_Click()
DoCmd.OpenReport "rptLaborODC", acViewPreview, , , acNormal
End Sub

This worked out for me but then I tried to add a date range. I found some
instructions from Allen Browne’s website for “Limiting a report to a date
range� and followed them which were very good because I almost got it to
work. It’s asking me for parameters related to the above dialog box for
cboCategory and cboGroup.

I’d like to combine the two. Is this possible with what I’ve already
created or should I be doing something else? Can anyone help me with this
please?

Here is Allen’s code:
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "rptLaborODC"
strDateField = "[TransDate]"
lngView = acViewPreview

If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " = " & Format(Me.txtStartDate,
strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If


If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If


Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub

Thank you very much in advance,
Kay

Daryl,

I got rid of the End If and put it all on one line…no more red. I
appreciate the additional information. I think I understand but maybe not
because I now have a new error message but I have no more red in my vba code
and the bugger is not popping up so I’m not sure. The error message caption
is Cannot Open Report and the error reads as: #2465: Microsoft can’t find the
field “l� referred to in your expression. I looked all around for an “l� but
didn’t see one.

I REALLY appreciate your patience. I just have to be close to getting this
to work. Any ideas about how to fix this error?

Many, many thanks,
Kay


"Daryl S" wrote:

AccessKay -

There are two ways to do the If/Then statements. If there is only one
statement to execute in the 'true' and optional 'else' clauses, then you can
put them on one line, without an End If:

If test Then result Else else result

The more common way allows for multiple statements to be executed in the
'true' case and in the optional 'else' case. This format requires the End If
(so it knows when the block of statements ends).

If test Then
result block
Else
else block
End If

Your statement could be either

If Err.Number 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation,
"Cannot open report"
End If '(Error message)

or (this would be all on one line - the copy/paste may not show it that way)

If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " &
Err.Description, vbExclamation, "Cannot open report"

--
Daryl S


"AccessKay" wrote:

Daryl,
VERY useful to know and the red went away. The vba is running okay until it
gets to the end and then I get this Compile error: End If without block If.
It's this part he
Err_Handler:
If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " &
Err.Description, vbExclamation, "Cannot open report"
End If (Error message)
Resume Exit_Handler
End Sub

Can you help me fix this please?

Thanks again,
Kay



"Daryl S" wrote:

AccessKay -

Yes, the copy/paste does add some line breaks...

In Access code, if your statement will take up more than one line, you will
need to tell access that the code continues on the next line. This is done
with an underscore character, but must be in a logical place (not within
double-quotes for example). So you can change the code by removing the line
feeds or by adding in more line continuation characters, or a combination of
the two. Like this:

strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & _
"' AND "[Group] = '" & _
Forms!DialogBox!cboGroup.column(0) & "'"

Hope that helps!

--
Daryl S


"AccessKay" wrote:

Thanks Daryl. I’m very new to vba. I replaced what I thought was the
made-up names. In future posts, I’ll remember to state these names.
Anyways, I’ve got a lot of red related to the strWhere areas.

' start here building the WHERE clause - substitute your real names here.
strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & "'
AND "
[Group] = '" & _
Forms!DialogBox!cboGroup.column(0) & "'"

Here…I changed my form name to DialogBox and the name of my first combo
which is cboCategory. I did the same for Group. The text turns red at
[Group].
Then in the second block, you put a note to update. My text box is named
txtStartDate so I didn’t change this. I wasn’t sure if there was another
name in there that I needed to change.

If IsDate(Me.txtStartDate) Then
If strWhere vbNullString Then 'updated this block
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " = " &
Format(Me.txtStartDate,
strcJetDate) & ")"

The text goes red at strWhere = strWhere & “….

End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

Again at strWhere it goes red.

Then at the very bottom it’s red for MsgBox "Error " & Err.Number & ": " &
Err.Description,

Just to clarify…
Combo box 1: cboCategory
Combo box 2: cboGroup
Text box 1: txtStartDate
Text box 2: txtEndDate
Command Button: cmdPreview
Form name: DialogBox
Query name: LaborODC

I hope I have provided you with enough information. I really appreciate the
help!!!
Kay



"Daryl S" wrote:

Kay -

To update the code, you first fill in the strWhere with the values from the
forms combo boxes, then add the dates as needed. I have updated this, but
you will need to change my made-up names for your real ones:


Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "rptLaborODC"
strDateField = "[TransDate]"
lngView = acViewPreview

' start here building the WHERE clause - substitute your real names here.
strWhere = "Category = '" & Forms!formname!cboCatName.column(0) & "' AND
[Group] = '" & _
Forms!formname!cboGpName.column(0) & "'"

If IsDate(Me.txtStartDate) Then
If strWhere vbNullString Then 'updated this block
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " = " &
Format(Me.txtStartDate,
strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If


Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub

--
Daryl S


"AccessKay" wrote:

Hi,

I created my first dialog box that includes two combo boxes, one named
cboCategory and another one named cboGroup. I also created a command button
that opens a report. My VBA is as follows:

Private Sub cmdOK_Click()
DoCmd.OpenReport "rptLaborODC", acViewPreview, , , acNormal
End Sub

This worked out for me but then I tried to add a date range. I found some
instructions from Allen Browne’s website for “Limiting a report to a date
range� and followed them which were very good because I almost got it to
work. It’s asking me for parameters related to the above dialog box for
cboCategory and cboGroup.

I’d like to combine the two. Is this possible with what I’ve already
created or should I be doing something else? Can anyone help me with this
please?

Here is Allen’s code:
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "rptLaborODC"
strDateField = "[TransDate]"
lngView = acViewPreview

If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " = " & Format(Me.txtStartDate,
strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If


If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If


Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub

Thank you very much in advance,
Kay

AccessKay -

Can you run the report by itself (rptLaborODC)?

If not, then work on the report until it works by itself.

Then work on the strWhere. It should be in your immediate window if you
have the code open while you run the report from the form. There could be a
typo or other issue there.

If you can't figure it out, then post both the strWhere (copy/paste from the
immediate window), and the SQL for your report's record source.

--
Daryl S


"AccessKay" wrote:

Daryl,

I got rid of the End If and put it all on one line…no more red. I
appreciate the additional information. I think I understand but maybe not
because I now have a new error message but I have no more red in my vba code
and the bugger is not popping up so I’m not sure. The error message caption
is Cannot Open Report and the error reads as: #2465: Microsoft can’t find the
field “l� referred to in your expression. I looked all around for an “l� but
didn’t see one.

I REALLY appreciate your patience. I just have to be close to getting this
to work. Any ideas about how to fix this error?

Many, many thanks,
Kay


"Daryl S" wrote:

AccessKay -

There are two ways to do the If/Then statements. If there is only one
statement to execute in the 'true' and optional 'else' clauses, then you can
put them on one line, without an End If:

If test Then result Else else result

The more common way allows for multiple statements to be executed in the
'true' case and in the optional 'else' case. This format requires the End If
(so it knows when the block of statements ends).

If test Then
result block
Else
else block
End If

Your statement could be either

If Err.Number 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation,
"Cannot open report"
End If '(Error message)

or (this would be all on one line - the copy/paste may not show it that way)

If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " &
Err.Description, vbExclamation, "Cannot open report"

--
Daryl S


"AccessKay" wrote:

Daryl,
VERY useful to know and the red went away. The vba is running okay until it
gets to the end and then I get this Compile error: End If without block If.
It's this part he
Err_Handler:
If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " &
Err.Description, vbExclamation, "Cannot open report"
End If (Error message)
Resume Exit_Handler
End Sub

Can you help me fix this please?

Thanks again,
Kay



"Daryl S" wrote:

AccessKay -

Yes, the copy/paste does add some line breaks...

In Access code, if your statement will take up more than one line, you will
need to tell access that the code continues on the next line. This is done
with an underscore character, but must be in a logical place (not within
double-quotes for example). So you can change the code by removing the line
feeds or by adding in more line continuation characters, or a combination of
the two. Like this:

strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & _
"' AND "[Group] = '" & _
Forms!DialogBox!cboGroup.column(0) & "'"

Hope that helps!

--
Daryl S


"AccessKay" wrote:

Thanks Daryl. I’m very new to vba. I replaced what I thought was the
made-up names. In future posts, I’ll remember to state these names.
Anyways, I’ve got a lot of red related to the strWhere areas.

' start here building the WHERE clause - substitute your real names here.
strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & "'
AND "
[Group] = '" & _
Forms!DialogBox!cboGroup.column(0) & "'"

Here…I changed my form name to DialogBox and the name of my first combo
which is cboCategory. I did the same for Group. The text turns red at
[Group].
Then in the second block, you put a note to update. My text box is named
txtStartDate so I didn’t change this. I wasn’t sure if there was another
name in there that I needed to change.

If IsDate(Me.txtStartDate) Then
If strWhere vbNullString Then 'updated this block
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " = " &
Format(Me.txtStartDate,
strcJetDate) & ")"

The text goes red at strWhere = strWhere & “….

End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

Again at strWhere it goes red.

Then at the very bottom it’s red for MsgBox "Error " & Err.Number & ": " &
Err.Description,

Just to clarify…
Combo box 1: cboCategory
Combo box 2: cboGroup
Text box 1: txtStartDate
Text box 2: txtEndDate
Command Button: cmdPreview
Form name: DialogBox
Query name: LaborODC

I hope I have provided you with enough information. I really appreciate the
help!!!
Kay



"Daryl S" wrote:

Kay -

To update the code, you first fill in the strWhere with the values from the
forms combo boxes, then add the dates as needed. I have updated this, but
you will need to change my made-up names for your real ones:


Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "rptLaborODC"
strDateField = "[TransDate]"
lngView = acViewPreview

' start here building the WHERE clause - substitute your real names here.
strWhere = "Category = '" & Forms!formname!cboCatName.column(0) & "' AND
[Group] = '" & _
Forms!formname!cboGpName.column(0) & "'"

If IsDate(Me.txtStartDate) Then
If strWhere vbNullString Then 'updated this block
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " = " &
Format(Me.txtStartDate,
strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If


Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub

--
Daryl S


"AccessKay" wrote:

Hi,

I created my first dialog box that includes two combo boxes, one named
cboCategory and another one named cboGroup. I also created a command button
that opens a report. My VBA is as follows:

Private Sub cmdOK_Click()
DoCmd.OpenReport "rptLaborODC", acViewPreview, , , acNormal
End Sub

This worked out for me but then I tried to add a date range. I found some
instructions from Allen Browne’s website for “Limiting a report to a date
range� and followed them which were very good because I almost got it to
work. It’s asking me for parameters related to the above dialog box for
cboCategory and cboGroup.

I’d like to combine the two. Is this possible with what I’ve already
created or should I be doing something else? Can anyone help me with this
please?

Here is Allen’s code:
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "rptLaborODC"
strDateField = "[TransDate]"
lngView = acViewPreview

If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " = " & Format(Me.txtStartDate,
strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If


If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If


Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub

Thank you very much in advance,
Kay

Daryl,
The report runs okay if I have the form open. If I don’t, then it asks for
parameters. I have this feeling that it has something to do with the first
strWhere.

This is what I have right now:
strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "'
AND "
[Group] = " & Forms!frmDialogBox!cboGroup.column(0) & "'"

I noticed I had a missing ' after [Group] = from the version you sent me.
If I put this in, the text turns red and I get a syntex error. I'm thinking
I'm missing something else, but I can't tell what it is. Also, the last "'"
at the end is green.

I can send what you requested last time but I was hoping you could identify
my error here.

Thanks,
Kay




"Daryl S" wrote:

AccessKay -

Can you run the report by itself (rptLaborODC)?

If not, then work on the report until it works by itself.

Then work on the strWhere. It should be in your immediate window if you
have the code open while you run the report from the form. There could be a
typo or other issue there.

If you can't figure it out, then post both the strWhere (copy/paste from the
immediate window), and the SQL for your report's record source.

--
Daryl S


"AccessKay" wrote:

Daryl,

I got rid of the End If and put it all on one line…no more red. I
appreciate the additional information. I think I understand but maybe not
because I now have a new error message but I have no more red in my vba code
and the bugger is not popping up so I’m not sure. The error message caption
is Cannot Open Report and the error reads as: #2465: Microsoft can’t find the
field “l� referred to in your expression. I looked all around for an “l� but
didn’t see one.

I REALLY appreciate your patience. I just have to be close to getting this
to work. Any ideas about how to fix this error?

Many, many thanks,
Kay


"Daryl S" wrote:

AccessKay -

There are two ways to do the If/Then statements. If there is only one
statement to execute in the 'true' and optional 'else' clauses, then you can
put them on one line, without an End If:

If test Then result Else else result

The more common way allows for multiple statements to be executed in the
'true' case and in the optional 'else' case. This format requires the End If
(so it knows when the block of statements ends).

If test Then
result block
Else
else block
End If

Your statement could be either

If Err.Number 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation,
"Cannot open report"
End If '(Error message)

or (this would be all on one line - the copy/paste may not show it that way)

If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " &
Err.Description, vbExclamation, "Cannot open report"

--
Daryl S


"AccessKay" wrote:

Daryl,
VERY useful to know and the red went away. The vba is running okay until it
gets to the end and then I get this Compile error: End If without block If.
It's this part he
Err_Handler:
If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " &
Err.Description, vbExclamation, "Cannot open report"
End If (Error message)
Resume Exit_Handler
End Sub

Can you help me fix this please?

Thanks again,
Kay



"Daryl S" wrote:

AccessKay -

Yes, the copy/paste does add some line breaks...

In Access code, if your statement will take up more than one line, you will
need to tell access that the code continues on the next line. This is done
with an underscore character, but must be in a logical place (not within
double-quotes for example). So you can change the code by removing the line
feeds or by adding in more line continuation characters, or a combination of
the two. Like this:

strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & _
"' AND "[Group] = '" & _
Forms!DialogBox!cboGroup.column(0) & "'"

Hope that helps!

--
Daryl S


"AccessKay" wrote:

Thanks Daryl. I’m very new to vba. I replaced what I thought was the
made-up names. In future posts, I’ll remember to state these names.
Anyways, I’ve got a lot of red related to the strWhere areas.

' start here building the WHERE clause - substitute your real names here.
strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & "'
AND "
[Group] = '" & _
Forms!DialogBox!cboGroup.column(0) & "'"

Here…I changed my form name to DialogBox and the name of my first combo
which is cboCategory. I did the same for Group. The text turns red at
[Group].
Then in the second block, you put a note to update. My text box is named
txtStartDate so I didn’t change this. I wasn’t sure if there was another
name in there that I needed to change.

If IsDate(Me.txtStartDate) Then
If strWhere vbNullString Then 'updated this block
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " = " &
Format(Me.txtStartDate,
strcJetDate) & ")"

The text goes red at strWhere = strWhere & “….

End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

Again at strWhere it goes red.

Then at the very bottom it’s red for MsgBox "Error " & Err.Number & ": " &
Err.Description,

Just to clarify…
Combo box 1: cboCategory
Combo box 2: cboGroup
Text box 1: txtStartDate
Text box 2: txtEndDate
Command Button: cmdPreview
Form name: DialogBox
Query name: LaborODC

I hope I have provided you with enough information. I really appreciate the
help!!!
Kay



"Daryl S" wrote:

Kay -

To update the code, you first fill in the strWhere with the values from the
forms combo boxes, then add the dates as needed. I have updated this, but
you will need to change my made-up names for your real ones:


Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "rptLaborODC"
strDateField = "[TransDate]"
lngView = acViewPreview

' start here building the WHERE clause - substitute your real names here.
strWhere = "Category = '" & Forms!formname!cboCatName.column(0) & "' AND
[Group] = '" & _
Forms!formname!cboGpName.column(0) & "'"

If IsDate(Me.txtStartDate) Then
If strWhere vbNullString Then 'updated this block
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " = " &
Format(Me.txtStartDate,
strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If


Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub

--
Daryl S


"AccessKay" wrote:

Hi,

I created my first dialog box that includes two combo boxes, one named
cboCategory and another one named cboGroup. I also created a command button
that opens a report. My VBA is as follows:

Private Sub cmdOK_Click()
DoCmd.OpenReport "rptLaborODC", acViewPreview, , , acNormal
End Sub

This worked out for me but then I tried to add a date range. I found some
instructions from Allen Browne’s website for “Limiting a report to a date
range� and followed them which were very good because I almost got it to
work. It’s asking me for parameters related to the above dialog box for
cboCategory and cboGroup.

I’d like to combine the two. Is this possible with what I’ve already
created or should I be doing something else? Can anyone help me with this
please?

Here is Allen’s code:
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "rptLaborODC"
strDateField = "[TransDate]"
lngView = acViewPreview

If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " = " & Format(Me.txtStartDate,
strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If


If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If


Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description,

AccessKay -

If the query is pulling information from the form (as yours is), then the
form must be open for the query to no ask for the values. I put the missing
single-quote in on the second line below - if you get green text in an Access
code window, then it is treated as a comment and ignored by the compiler.
You usually put comments in by putting a single quote and then the comment.
If the single quote is not a comment, but part of the line, then there is
probably something missing (as you said, the matching single quote).

strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "'
AND "
[Group] = '" & Forms!frmDialogBox!cboGroup.column(0) & "'"

Hope this helps!

--
Daryl S


"AccessKay" wrote:

Daryl,
The report runs okay if I have the form open. If I don’t, then it asks for
parameters. I have this feeling that it has something to do with the first
strWhere.

This is what I have right now:
strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "'
AND "
[Group] = " & Forms!frmDialogBox!cboGroup.column(0) & "'"

I noticed I had a missing ' after [Group] = from the version you sent me.
If I put this in, the text turns red and I get a syntex error. I'm thinking
I'm missing something else, but I can't tell what it is. Also, the last "'"
at the end is green.

I can send what you requested last time but I was hoping you could identify
my error here.

Thanks,
Kay




"Daryl S" wrote:

AccessKay -

Can you run the report by itself (rptLaborODC)?

If not, then work on the report until it works by itself.

Then work on the strWhere. It should be in your immediate window if you
have the code open while you run the report from the form. There could be a
typo or other issue there.

If you can't figure it out, then post both the strWhere (copy/paste from the
immediate window), and the SQL for your report's record source.

--
Daryl S


"AccessKay" wrote:

Daryl,

I got rid of the End If and put it all on one line…no more red. I
appreciate the additional information. I think I understand but maybe not
because I now have a new error message but I have no more red in my vba code
and the bugger is not popping up so I’m not sure. The error message caption
is Cannot Open Report and the error reads as: #2465: Microsoft can’t find the
field “l� referred to in your expression. I looked all around for an “l� but
didn’t see one.

I REALLY appreciate your patience. I just have to be close to getting this
to work. Any ideas about how to fix this error?

Many, many thanks,
Kay


"Daryl S" wrote:

AccessKay -

There are two ways to do the If/Then statements. If there is only one
statement to execute in the 'true' and optional 'else' clauses, then you can
put them on one line, without an End If:

If test Then result Else else result

The more common way allows for multiple statements to be executed in the
'true' case and in the optional 'else' case. This format requires the End If
(so it knows when the block of statements ends).

If test Then
result block
Else
else block
End If

Your statement could be either

If Err.Number 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation,
"Cannot open report"
End If '(Error message)

or (this would be all on one line - the copy/paste may not show it that way)

If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " &
Err.Description, vbExclamation, "Cannot open report"

--
Daryl S


"AccessKay" wrote:

Daryl,
VERY useful to know and the red went away. The vba is running okay until it
gets to the end and then I get this Compile error: End If without block If.
It's this part he
Err_Handler:
If Err.Number 2501 Then MsgBox "Error " & Err.Number & ": " &
Err.Description, vbExclamation, "Cannot open report"
End If (Error message)
Resume Exit_Handler
End Sub

Can you help me fix this please?

Thanks again,
Kay



"Daryl S" wrote:

AccessKay -

Yes, the copy/paste does add some line breaks...

In Access code, if your statement will take up more than one line, you will
need to tell access that the code continues on the next line. This is done
with an underscore character, but must be in a logical place (not within
double-quotes for example). So you can change the code by removing the line
feeds or by adding in more line continuation characters, or a combination of
the two. Like this:

strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & _
"' AND "[Group] = '" & _
Forms!DialogBox!cboGroup.column(0) & "'"

Hope that helps!

--
Daryl S


"AccessKay" wrote:

Thanks Daryl. I’m very new to vba. I replaced what I thought was the
made-up names. In future posts, I’ll remember to state these names.
Anyways, I’ve got a lot of red related to the strWhere areas.

' start here building the WHERE clause - substitute your real names here.
strWhere = "Category = '" & Forms!DialogBox!cboCategory.Column(0) & "'
AND "
[Group] = '" & _
Forms!DialogBox!cboGroup.column(0) & "'"

Here…I changed my form name to DialogBox and the name of my first combo
which is cboCategory. I did the same for Group. The text turns red at
[Group].
Then in the second block, you put a note to update. My text box is named
txtStartDate so I didn’t change this. I wasn’t sure if there was another
name in there that I needed to change.

If IsDate(Me.txtStartDate) Then
If strWhere vbNullString Then 'updated this block
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " = " &
Format(Me.txtStartDate,
strcJetDate) & ")"

The text goes red at strWhere = strWhere & “….

End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

Again at strWhere it goes red.

Then at the very bottom it’s red for MsgBox "Error " & Err.Number & ": " &
Err.Description,

Just to clarify…
Combo box 1: cboCategory
Combo box 2: cboGroup
Text box 1: txtStartDate
Text box 2: txtEndDate
Command Button: cmdPreview
Form name: DialogBox
Query name: LaborODC

I hope I have provided you with enough information. I really appreciate the
help!!!
Kay



"Daryl S" wrote:

Kay -

To update the code, you first fill in the strWhere with the values from the
forms combo boxes, then add the dates as needed. I have updated this, but
you will need to change my made-up names for your real ones:


Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "rptLaborODC"
strDateField = "[TransDate]"
lngView = acViewPreview

' start here building the WHERE clause - substitute your real names here.
strWhere = "Category = '" & Forms!formname!cboCatName.column(0) & "' AND
[Group] = '" & _
Forms!formname!cboGpName.column(0) & "'"

If IsDate(Me.txtStartDate) Then
If strWhere vbNullString Then 'updated this block
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " = " &
Format(Me.txtStartDate,
strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " " &
Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If


Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub

--
Daryl S


"AccessKay" wrote:

Hi,

I created my first dialog box that includes two combo boxes, one named
cboCategory and another one named cboGroup. I also created a command button
that opens a report. My VBA is as follows:

Private Sub cmdOK_Click()
DoCmd.OpenReport "rptLaborODC", acViewPreview, , , acNormal
End Sub

This worked out for me but then I tried to add a date range. I found some
instructions from Allen Browne’s website for “Limiting a report to a date
range� and followed them which were very good because I almost got it to
work. It’s asking me for parameters related to the above dialog box for
cboCategory and cboGroup.

I’d like to combine the two. Is this possible with what I’ve already
created or should I be doing something else? Can anyone help me with this
please?

Here is Allen’s code:
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "rptLaborODC"
strDateField = "[TransDate]"
lngView = acViewPreview

If IsDate(Me.txtStartDate) Then