Combine combo boxes with date range

On Thu, 1 Apr 2010 12:10:01 -0700, AccessKay
wrote:

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

John W. Vinson [MVP]

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" wrote:

AccessKay -

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

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

Hope this helps!

--
Daryl S


"AccessKay" wrote:

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

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

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

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

Thanks,
Kay




"Daryl S" wrote:

AccessKay -

Can you run the report by itself (rptLaborODC)?

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

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

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

--
Daryl S


"AccessKay" wrote:

Daryl,

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

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

Many, many thanks,
Kay


"Daryl S" wrote:

AccessKay -

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

If test Then result Else else result

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

If test Then
result block
Else
else block
End If

Your statement could be either

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

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

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

--
Daryl S


"AccessKay" wrote:

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

Can you help me fix this please?

Thanks again,
Kay



"Daryl S" wrote:

AccessKay -

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

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

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

Hope that helps!

--
Daryl S


"AccessKay" wrote:

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

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

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

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

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

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

Again at strWhere it goes red.

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

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

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



"Daryl S" wrote:

Kay -

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


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

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

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

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

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


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

Exit_Handler:
Exit Sub

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

--
Daryl S


"AccessKay" wrote:

Hi,

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

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

This worked out for me but then I tried to add a date range. I found some
instructions from Allen Browne’s website for “Limiting a report to a date

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.

"John W. Vinson" wrote:

On Thu, 1 Apr 2010 12:10:01 -0700, AccessKay
wrote:

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

John W. Vinson [MVP]
.

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" wrote:

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" wrote:

AccessKay -

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

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

Hope this helps!

--
Daryl S


"AccessKay" wrote:

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

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

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

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

Thanks,
Kay




"Daryl S" wrote:

AccessKay -

Can you run the report by itself (rptLaborODC)?

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

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

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

--
Daryl S


"AccessKay" wrote:

Daryl,

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

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

Many, many thanks,
Kay


"Daryl S" wrote:

AccessKay -

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

If test Then result Else else result

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

If test Then
result block
Else
else block
End If

Your statement could be either

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

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

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

--
Daryl S


"AccessKay" wrote:

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

Can you help me fix this please?

Thanks again,
Kay



"Daryl S" wrote:

AccessKay -

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

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

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

Hope that helps!

--
Daryl S


"AccessKay" wrote:

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

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

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

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

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

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

Again at strWhere it goes red.

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

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

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



"Daryl S" wrote:

Kay -

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


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

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

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

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

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" wrote:

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" wrote:

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" wrote:

AccessKay -

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

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

Hope this helps!

--
Daryl S


"AccessKay" wrote:

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

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

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

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

Thanks,
Kay




"Daryl S" wrote:

AccessKay -

Can you run the report by itself (rptLaborODC)?

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

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

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

--
Daryl S


"AccessKay" wrote:

Daryl,

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

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

Many, many thanks,
Kay


"Daryl S" wrote:

AccessKay -

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

If test Then result Else else result

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

If test Then
result block
Else
else block
End If

Your statement could be either

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

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

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

--
Daryl S


"AccessKay" wrote:

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

Can you help me fix this please?

Thanks again,
Kay



"Daryl S" wrote:

AccessKay -

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

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

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

Hope that helps!

--
Daryl S


"AccessKay" wrote:

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

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

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

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

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

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

Again at strWhere it goes red.

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

Just to clarify…
Combo box 1: cboCategory
Combo box 2: cboGroup
Text box 1: txtStartDate
Text box 2: txtEndDate

Great! Thanks...
--
Daryl S


"AccessKay" wrote:

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" wrote:

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" wrote:

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" wrote:

AccessKay -

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

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

Hope this helps!

--
Daryl S


"AccessKay" wrote:

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

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

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

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

Thanks,
Kay




"Daryl S" wrote:

AccessKay -

Can you run the report by itself (rptLaborODC)?

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

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

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

--
Daryl S


"AccessKay" wrote:

Daryl,

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

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

Many, many thanks,
Kay


"Daryl S" wrote:

AccessKay -

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

If test Then result Else else result

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

If test Then
result block
Else
else block
End If

Your statement could be either

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

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

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

--
Daryl S


"AccessKay" wrote:

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

Can you help me fix this please?

Thanks again,
Kay



"Daryl S" wrote:

AccessKay -

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

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

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

Hope that helps!

--
Daryl S


"AccessKay" wrote:

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

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

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

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

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

End If
If IsDate(Me.txtEndDate) Then
If strWhere vbNullString Then
strWhere = strWhere & " AND "
End If