Combine combo boxes with date range

AccessKay · Mar 30, 2010

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 S · Mar 31, 2010

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 said:
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 · Mar 31, 2010

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 said:
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 said:

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

Click to expand...

Daryl S · Mar 31, 2010

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 said:
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 said:

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 said:

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

Click to expand...

Click to expand...

AccessKay · Mar 31, 2010

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 here:
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 said:
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 said:

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 said:

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

:

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

Click to expand...

Click to expand...

Daryl S · Apr 1, 2010

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 said:
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 here:
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 said:

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 said:

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

:

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

:

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

Click to expand...

Click to expand...

AccessKay · Apr 1, 2010

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 said:
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 said:

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 here:
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 said:

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

:

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

:

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

:

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

Click to expand...

Click to expand...

Daryl S · Apr 1, 2010

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 said:
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 said:

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 said:

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 here:
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

:

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

:

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

:

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

:

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

Click to expand...

Click to expand...

AccessKay · Apr 1, 2010

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 said:
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 said:

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 said:

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

:

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 here:
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

:

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

:

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

:

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

:

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,

Click to expand...

Click to expand...

Daryl S · Apr 1, 2010

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 said:
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 said:

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 said:

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

:

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

:

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 here:
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

:

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

:

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

:

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

:

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

Click to expand...

Click to expand...

John W. Vinson · Apr 1, 2010

The report runs okay if I have the form open. If I don’t, then it asks for
parameters.

Well... yes. The query is asking for a parameter (the form reference). If the
form isn't open the query won't know what its parameter value is, so it will
ask. This is normal and expected behavior; what would you WANT the query to do
if its parameter is not supplied?

AccessKay · Apr 1, 2010

Daryl, Iâ€™ve learned a lot from you about vba in the course of this dialog. I
thank you for that. Though, Iâ€™m starting to wonder if Iâ€™m in over my head,
but I really donâ€™t want to quit on this. I got an Error #13 Type mismatch
but hey, no red text. Here is what I have. I hope you can figure it out but
I still appreciate your help if you canâ€™t.

My vba:
strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "' "
And "[Group] = '" & Forms!frmDialogBox!cboGroup.Column(0) & "'"

If IsDate(Me.txtStartDate) Then
If strWhere <> vbNullString Then
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

Here is my SQL:
SELECT tblTrans_Mstr.Category, tblTrans_Mstr.TransDate, tblTrans_Mstr.Group,
tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions,
tblTrans_Mstr.Credit_GL_Acct, tblTrans_Mstr.Lbr_Hours,
Sum(tblTrans_Mstr.Labor_Cost) AS SumOfLabor_Cost, tblTrans_Mstr.Lbr_OH_Rate,
Sum(tblTrans_Mstr.Lbr_OH_Cost) AS SumOfLbr_OH_Cost,
Sum([Labor_Cost]+[Lbr_OH_Cost]) AS LbrTotCost,
tblTrans_Mstr.ODC_Cost_Category, Sum(tblTrans_Mstr.ODC_Cost) AS SumOfODC_Cost
FROM tblTrans_Mstr
GROUP BY tblTrans_Mstr.Category, tblTrans_Mstr.TransDate,
tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions,
tblTrans_Mstr.Credit_GL_Acct, tblTrans_Mstr.Lbr_Hours,
tblTrans_Mstr.Lbr_OH_Rate, tblTrans_Mstr.ODC_Cost_Category
HAVING (((tblTrans_Mstr.Category)=[Forms]![frmDialogBox]![cboCategory]) AND
((tblTrans_Mstr.TransDate)=[Forms]![frmDialogBox]![txtStartDate] And
(tblTrans_Mstr.TransDate)=[Forms]![frmDialogBox]![txtEndDate]) AND
((tblTrans_Mstr.Group)=[Forms]![frmDialogBox]![cboGroup]));

Thanks,
Kay

Daryl S said:
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 said:

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 said:

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

:

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

:

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

:

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 here:
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

:

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

:

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

:

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

:

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

Click to expand...

Click to expand...

AccessKay · Apr 1, 2010

Thanks for your reply. I fully understand this. Itâ€™s intended to be a
dialog box but I canâ€™t get my code to work. The problem seems to be with my
vba.

Daryl S · Apr 2, 2010

Kay -

You are pretty close. In the WHERE clause, you want the field name included
within the double-quotes, but the values from the form need to be evaluated
outside of the double-quotes. I have updated the code here (untested).

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

If IsDate(Me.txtStartDate) Then
If strWhere <> vbNullString Then
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

debug.print strWhere

The SQL looks OK based on a quick glance. If you have issues, step through
the code as it runs, and when the debug.print strWhere statement executes,
the results will be in the immediate window. This is what you should look at
for issues, and copy/paste into your next post.

Good luck!

--
Daryl S

AccessKay said:
Daryl, Iâ€™ve learned a lot from you about vba in the course of this dialog. I
thank you for that. Though, Iâ€™m starting to wonder if Iâ€™m in over my head,
but I really donâ€™t want to quit on this. I got an Error #13 Type mismatch
but hey, no red text. Here is what I have. I hope you can figure it out but
I still appreciate your help if you canâ€™t.

My vba:
strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "' "
And "[Group] = '" & Forms!frmDialogBox!cboGroup.Column(0) & "'"

If IsDate(Me.txtStartDate) Then
If strWhere <> vbNullString Then
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

Here is my SQL:
SELECT tblTrans_Mstr.Category, tblTrans_Mstr.TransDate, tblTrans_Mstr.Group,
tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions,
tblTrans_Mstr.Credit_GL_Acct, tblTrans_Mstr.Lbr_Hours,
Sum(tblTrans_Mstr.Labor_Cost) AS SumOfLabor_Cost, tblTrans_Mstr.Lbr_OH_Rate,
Sum(tblTrans_Mstr.Lbr_OH_Cost) AS SumOfLbr_OH_Cost,
Sum([Labor_Cost]+[Lbr_OH_Cost]) AS LbrTotCost,
tblTrans_Mstr.ODC_Cost_Category, Sum(tblTrans_Mstr.ODC_Cost) AS SumOfODC_Cost
FROM tblTrans_Mstr
GROUP BY tblTrans_Mstr.Category, tblTrans_Mstr.TransDate,
tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions,
tblTrans_Mstr.Credit_GL_Acct, tblTrans_Mstr.Lbr_Hours,
tblTrans_Mstr.Lbr_OH_Rate, tblTrans_Mstr.ODC_Cost_Category
HAVING (((tblTrans_Mstr.Category)=[Forms]![frmDialogBox]![cboCategory]) AND
((tblTrans_Mstr.TransDate)=[Forms]![frmDialogBox]![txtStartDate] And
(tblTrans_Mstr.TransDate)=[Forms]![frmDialogBox]![txtEndDate]) AND
((tblTrans_Mstr.Group)=[Forms]![frmDialogBox]![cboGroup]));

Thanks,
Kay

Daryl S said:

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 said:

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

:

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

:

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

:

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

:

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 here:
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

:

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

:

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

:

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

Click to expand...

Click to expand...

AccessKay · Apr 2, 2010

Hi Daryl,

Iâ€™m so excitedâ€¦it works!!! Youâ€™re a good man to stick with me. I really
appreciate your help and patience. It was my first attempt to use vba and I
never could have done this without you. Iâ€™ve learned so much from this and
Iâ€™m thinking that I might go out and get me a VBA for Dummies book.

I canâ€™t thank you enough!

Kay

Daryl S said:
Kay -

You are pretty close. In the WHERE clause, you want the field name included
within the double-quotes, but the values from the form need to be evaluated
outside of the double-quotes. I have updated the code here (untested).

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

If IsDate(Me.txtStartDate) Then
If strWhere <> vbNullString Then
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

debug.print strWhere

The SQL looks OK based on a quick glance. If you have issues, step through
the code as it runs, and when the debug.print strWhere statement executes,
the results will be in the immediate window. This is what you should look at
for issues, and copy/paste into your next post.

Good luck!

--
Daryl S

AccessKay said:

Daryl, Iâ€™ve learned a lot from you about vba in the course of this dialog. I
thank you for that. Though, Iâ€™m starting to wonder if Iâ€™m in over my head,
but I really donâ€™t want to quit on this. I got an Error #13 Type mismatch
but hey, no red text. Here is what I have. I hope you can figure it out but
I still appreciate your help if you canâ€™t.

My vba:
strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "' "
And "[Group] = '" & Forms!frmDialogBox!cboGroup.Column(0) & "'"

If IsDate(Me.txtStartDate) Then
If strWhere <> vbNullString Then
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

Here is my SQL:
SELECT tblTrans_Mstr.Category, tblTrans_Mstr.TransDate, tblTrans_Mstr.Group,
tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions,
tblTrans_Mstr.Credit_GL_Acct, tblTrans_Mstr.Lbr_Hours,
Sum(tblTrans_Mstr.Labor_Cost) AS SumOfLabor_Cost, tblTrans_Mstr.Lbr_OH_Rate,
Sum(tblTrans_Mstr.Lbr_OH_Cost) AS SumOfLbr_OH_Cost,
Sum([Labor_Cost]+[Lbr_OH_Cost]) AS LbrTotCost,
tblTrans_Mstr.ODC_Cost_Category, Sum(tblTrans_Mstr.ODC_Cost) AS SumOfODC_Cost
FROM tblTrans_Mstr
GROUP BY tblTrans_Mstr.Category, tblTrans_Mstr.TransDate,
tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions,
tblTrans_Mstr.Credit_GL_Acct, tblTrans_Mstr.Lbr_Hours,
tblTrans_Mstr.Lbr_OH_Rate, tblTrans_Mstr.ODC_Cost_Category
HAVING (((tblTrans_Mstr.Category)=[Forms]![frmDialogBox]![cboCategory]) AND
((tblTrans_Mstr.TransDate)=[Forms]![frmDialogBox]![txtStartDate] And
(tblTrans_Mstr.TransDate)=[Forms]![frmDialogBox]![txtEndDate]) AND
((tblTrans_Mstr.Group)=[Forms]![frmDialogBox]![cboGroup]));

Thanks,
Kay

Daryl S said:

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

:

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

:

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

:

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

:

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

:

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 here:
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

:

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

:

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

Click to expand...

Click to expand...

Daryl S · Apr 2, 2010

Great! Thanks...
--
Daryl S

AccessKay said:
Hi Daryl,

Iâ€™m so excitedâ€¦it works!!! Youâ€™re a good man to stick with me. I really
appreciate your help and patience. It was my first attempt to use vba and I
never could have done this without you. Iâ€™ve learned so much from this and
Iâ€™m thinking that I might go out and get me a VBA for Dummies book.

I canâ€™t thank you enough!

Kay

Daryl S said:

Kay -

You are pretty close. In the WHERE clause, you want the field name included
within the double-quotes, but the values from the form need to be evaluated
outside of the double-quotes. I have updated the code here (untested).

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

If IsDate(Me.txtStartDate) Then
If strWhere <> vbNullString Then
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

debug.print strWhere

The SQL looks OK based on a quick glance. If you have issues, step through
the code as it runs, and when the debug.print strWhere statement executes,
the results will be in the immediate window. This is what you should look at
for issues, and copy/paste into your next post.

Good luck!

--
Daryl S

AccessKay said:

Daryl, Iâ€™ve learned a lot from you about vba in the course of this dialog. I
thank you for that. Though, Iâ€™m starting to wonder if Iâ€™m in over my head,
but I really donâ€™t want to quit on this. I got an Error #13 Type mismatch
but hey, no red text. Here is what I have. I hope you can figure it out but
I still appreciate your help if you canâ€™t.

My vba:
strWhere = "Category = '" & Forms!frmDialogBox!cboCategory.Column(0) & "' "
And "[Group] = '" & Forms!frmDialogBox!cboGroup.Column(0) & "'"

If IsDate(Me.txtStartDate) Then
If strWhere <> vbNullString Then
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

Here is my SQL:
SELECT tblTrans_Mstr.Category, tblTrans_Mstr.TransDate, tblTrans_Mstr.Group,
tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions,
tblTrans_Mstr.Credit_GL_Acct, tblTrans_Mstr.Lbr_Hours,
Sum(tblTrans_Mstr.Labor_Cost) AS SumOfLabor_Cost, tblTrans_Mstr.Lbr_OH_Rate,
Sum(tblTrans_Mstr.Lbr_OH_Cost) AS SumOfLbr_OH_Cost,
Sum([Labor_Cost]+[Lbr_OH_Cost]) AS LbrTotCost,
tblTrans_Mstr.ODC_Cost_Category, Sum(tblTrans_Mstr.ODC_Cost) AS SumOfODC_Cost
FROM tblTrans_Mstr
GROUP BY tblTrans_Mstr.Category, tblTrans_Mstr.TransDate,
tblTrans_Mstr.Group, tblTrans_Mstr.Product, tblTrans_Mstr.PA_Descriptions,
tblTrans_Mstr.Credit_GL_Acct, tblTrans_Mstr.Lbr_Hours,
tblTrans_Mstr.Lbr_OH_Rate, tblTrans_Mstr.ODC_Cost_Category
HAVING (((tblTrans_Mstr.Category)=[Forms]![frmDialogBox]![cboCategory]) AND
((tblTrans_Mstr.TransDate)=[Forms]![frmDialogBox]![txtStartDate] And
(tblTrans_Mstr.TransDate)=[Forms]![frmDialogBox]![txtEndDate]) AND
((tblTrans_Mstr.Group)=[Forms]![frmDialogBox]![cboGroup]));

Thanks,
Kay

:

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

:

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

:

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

:

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

:

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

:

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 here:
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

:

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

:

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

Click to expand...

Click to expand...

Combine combo boxes with date range

AccessKay

Daryl S

AccessKay

Daryl S

AccessKay

Daryl S

AccessKay

Daryl S

AccessKay

Daryl S

John W. Vinson

AccessKay

AccessKay

Daryl S

AccessKay

Daryl S