Problems adding "All" to combo box

J

joer

Hi,
I'm trying to filter a report by using a combo box on a form. The
source of the report is a query (qry_ReportData) and the query uses the
combo boxes as criteria. The row source for each combo box is a table
of employee names and a separate table of machine names. I'm trying to
add "All" as an option to each combo box so that I can open a report
of, for example, all the employees who worked on 1 given machine or all
the machines that 1 employee worked on.

I've searched through the countless Group topics on adding "All" to a
combo box and none of the suggestions have worked. I've pasted my code
below, limiting the scope to one of the combo boxes (once I get the one
I can just do the same for the second). Thanks for the help.

qry_ReportData SQL:
SELECT [WO Results].Workorder, [WO Results].Assy_No, [WO
Results].WO_QTY, [WO Results].BoardsRan, [WO Results].Machine, [WO
Results].[PLCM/Board], [WO Results].WO_PLCM, [WO Results].[PLCM/Hr],
[WO Results].Date, [WO Results].Duration, [WO Results].Name, [WO
Results].Shift, [WO Results].Comments

FROM [WO Results]

WHERE ((([WO Results].Machine)=[Forms]![AI Work
Log]![cmbMachine_Report]) AND (([WO Results].Date)>[Forms]![AI Work
Log]![Date_Report]) AND (([WO Results].Name)=[Forms]![AI Work
Log]![cmbName_Report]));


Row Source SQL for Name combo box:
SELECT [tbl_Employee AI].[Full Name] FROM [tbl_Employee AI]
UNION Select "All" FROM [tbl_Employee AI];


VB Code for the On Click procedure of the "Open Report" button:
Dim stDocName As String

If cmbName_Report.Value = "All" Then
DoCmd.ShowAllRecords
stDocName = "AI Thru Hole Work Log_Operator"
DoCmd.OpenReport stDocName, acPreview
Else
qry_ReportData.Requery
stDocName = "AI Thru Hole Work Log_Operator"
DoCmd.OpenReport stDocName, acPreview
End If



Thanks!
 
K

Klatuu

Here is a site that explains it well:
http://www.mvps.org/access/forms/frm0043.htm

However, I would suggest an alternative method in this case. Assume that if
the combo box is left empty, then that means "All".
First, omit any filtering in the Query you are using in your report. We
will do it with the Where argument of the Open Report method. Below is a
modificaiton to the code where your report is opened that will filter it
based on the values in your two combos and allowing for either or both of
them to be blank.

VB Code for the On Click procedure of the "Open Report" button:
Dim strWhere As String

If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = '" & Me.cmbName_Report & "'"
Else
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
End If

DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, , strWhere

A few other pointers. Avoid using Access reserved words when naming
variables or objects. (Date, Name, Time, etc.)

Qualify your object names. For example, refer to a control using either
Me.ControlName or Forms!FormName!ControName

The .Value property is not required, it is the default.

The Requery you are using

qry_ReportData.Requery

is unnecessary.


joer said:
Hi,
I'm trying to filter a report by using a combo box on a form. The
source of the report is a query (qry_ReportData) and the query uses the
combo boxes as criteria. The row source for each combo box is a table
of employee names and a separate table of machine names. I'm trying to
add "All" as an option to each combo box so that I can open a report
of, for example, all the employees who worked on 1 given machine or all
the machines that 1 employee worked on.

I've searched through the countless Group topics on adding "All" to a
combo box and none of the suggestions have worked. I've pasted my code
below, limiting the scope to one of the combo boxes (once I get the one
I can just do the same for the second). Thanks for the help.

qry_ReportData SQL:
SELECT [WO Results].Workorder, [WO Results].Assy_No, [WO
Results].WO_QTY, [WO Results].BoardsRan, [WO Results].Machine, [WO
Results].[PLCM/Board], [WO Results].WO_PLCM, [WO Results].[PLCM/Hr],
[WO Results].Date, [WO Results].Duration, [WO Results].Name, [WO
Results].Shift, [WO Results].Comments

FROM [WO Results]

WHERE ((([WO Results].Machine)=[Forms]![AI Work
Log]![cmbMachine_Report]) AND (([WO Results].Date)>[Forms]![AI Work
Log]![Date_Report]) AND (([WO Results].Name)=[Forms]![AI Work
Log]![cmbName_Report]));


Row Source SQL for Name combo box:
SELECT [tbl_Employee AI].[Full Name] FROM [tbl_Employee AI]
UNION Select "All" FROM [tbl_Employee AI];


VB Code for the On Click procedure of the "Open Report" button:
Dim stDocName As String

If cmbName_Report.Value = "All" Then
DoCmd.ShowAllRecords
stDocName = "AI Thru Hole Work Log_Operator"
DoCmd.OpenReport stDocName, acPreview
Else
qry_ReportData.Requery
stDocName = "AI Thru Hole Work Log_Operator"
DoCmd.OpenReport stDocName, acPreview
End If



Thanks!
Click to expand...
 
J

joer

Thanks for the reply Klatuu.

I copied and pasted your code in place of my On Click procedure and
still had an error saying Access couldn't the "l" field. As a result I
removed any "'" characters in the code and still received the same
error. The new code is below, still not working, with my understanding
of each line commented out. What is the "Len()" command?

Thanks for your help,
Joe

VB Code for the On Click procedure of the "Open Report" button:

Dim strWhere As String


If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = " & Me.cmbName_Report
'If Name combo is not null, query name criteria is value of
name combo
Else
'If Name combo is null, do this:
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
'I'm not sure what the above statement means
strWhere = strWhere & "[Machine] = " & Me.[cmbMachine_Report]
'Query machine criteria is value of machine combo
End If
End If
DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere
'Run report with where clause



Here is a site that explains it well:
http://www.mvps.org/access/forms/frm0043.htm

However, I would suggest an alternative method in this case. Assume that if
the combo box is left empty, then that means "All".
First, omit any filtering in the Query you are using in your report. We
will do it with the Where argument of the Open Report method. Below is a
modificaiton to the code where your report is opened that will filter it
based on the values in your two combos and allowing for either or both of
them to be blank.

VB Code for the On Click procedure of the "Open Report" button:
Dim strWhere As String

If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = '" & Me.cmbName_Report & "'"
Else
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
End If

DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, , strWhere

A few other pointers. Avoid using Access reserved words when naming
variables or objects. (Date, Name, Time, etc.)

Qualify your object names. For example, refer to a control using either
Me.ControlName or Forms!FormName!ControName

The .Value property is not required, it is the default.

The Requery you are using

qry_ReportData.Requery

is unnecessary.


joer said:
Hi,
I'm trying to filter a report by using a combo box on a form. The
source of the report is a query (qry_ReportData) and the query uses the
combo boxes as criteria. The row source for each combo box is a table
of employee names and a separate table of machine names. I'm trying to
add "All" as an option to each combo box so that I can open a report
of, for example, all the employees who worked on 1 given machine or all
the machines that 1 employee worked on.

I've searched through the countless Group topics on adding "All" to a
combo box and none of the suggestions have worked. I've pasted my code
below, limiting the scope to one of the combo boxes (once I get the one
I can just do the same for the second). Thanks for the help.

qry_ReportData SQL:
SELECT [WO Results].Workorder, [WO Results].Assy_No, [WO
Results].WO_QTY, [WO Results].BoardsRan, [WO Results].Machine, [WO
Results].[PLCM/Board], [WO Results].WO_PLCM, [WO Results].[PLCM/Hr],
[WO Results].Date, [WO Results].Duration, [WO Results].Name, [WO
Results].Shift, [WO Results].Comments

FROM [WO Results]

WHERE ((([WO Results].Machine)=[Forms]![AI Work
Log]![cmbMachine_Report]) AND (([WO Results].Date)>[Forms]![AI Work
Log]![Date_Report]) AND (([WO Results].Name)=[Forms]![AI Work
Log]![cmbName_Report]));


Row Source SQL for Name combo box:
SELECT [tbl_Employee AI].[Full Name] FROM [tbl_Employee AI]
UNION Select "All" FROM [tbl_Employee AI];


VB Code for the On Click procedure of the "Open Report" button:
Dim stDocName As String

If cmbName_Report.Value = "All" Then
DoCmd.ShowAllRecords
stDocName = "AI Thru Hole Work Log_Operator"
DoCmd.OpenReport stDocName, acPreview
Else
qry_ReportData.Requery
stDocName = "AI Thru Hole Work Log_Operator"
DoCmd.OpenReport stDocName, acPreview
End If



Thanks!
Click to expand...
Click to expand...
 
K

Klatuu

Removing the "'" may or may not be correct. I put them in as a guess based
on your code. In SQL, the value you are looking for in a field has to be
delimited based on the data type of the field. Text fields require either
double or single qoutes, Date field require #, and numeric fields should have
no delimiter.
Text:
"[SomeRecordSetField] = '" & Me.SomeFormControl & "'"
Numeric
"[SomeRecordSetField] = " & Me.SomeFormControl
Date
"[SomeRecordSetField] = #" & Me.SomeFormControl & "#"

Now, the issue may be which object you are addressing, It could be my
fault, because I was unsure of what you are addressing, but the way to use
the Where condition is:
"[SomeRecordSetField] = '" & Me.SomeFormControl & "'"
SomeRecordSetField - This is a field in the query or table that is the
record source
of the report or form you want to filter
on. In this case, it
has to be a field in the record source of
the report.

Me.SomeFormControl - In this case, we are using the value contained in a
control
on your form that will be used to filter
the data in the
report. Only records where
[SomeRecordSetField] is equal
to the value in SomeFormControl will be
included in the
report.

The error you are getting means the field name in strWhere is not in the
report's recordset.

The Len() function returns the number of characters in a text object. It
can be a variable, a control, or a recordset field.

The other answers are below

joer said:
Thanks for the reply Klatuu.

I copied and pasted your code in place of my On Click procedure and
still had an error saying Access couldn't the "l" field. As a result I
removed any "'" characters in the code and still received the same
error. The new code is below, still not working, with my understanding
of each line commented out. What is the "Len()" command?

Thanks for your help,
Joe

VB Code for the On Click procedure of the "Open Report" button:

Dim strWhere As String


If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = " & Me.cmbName_Report
'If Name combo is not null, query name criteria is value of
name combo
Click to expand...
If the cmbName_Report combo is null, then [Name] will not be included in
strWhere and will not filter the reports recordset.
Else
'If Name combo is null, do this:
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
'I'm not sure what the above statement means
Click to expand...
When you are searching on multiple fields, there has to be the word And or
Or in the Where condition. This code checks to see if you are filtering on
[Name]. If you did not filter on [Name], strWhere will be a zero length
string. If you are filtering on [Name] and you are also filtering on [AI
Work Log] then you need the word And between the two conditions.
strWhere = strWhere & "[Machine] = " & Me.[cmbMachine_Report]
'Query machine criteria is value of machine combo Yes
End If
End If
DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere
'Run report with where clause Correct.



Here is a site that explains it well:
http://www.mvps.org/access/forms/frm0043.htm

However, I would suggest an alternative method in this case. Assume that if
the combo box is left empty, then that means "All".
First, omit any filtering in the Query you are using in your report. We
will do it with the Where argument of the Open Report method. Below is a
modificaiton to the code where your report is opened that will filter it
based on the values in your two combos and allowing for either or both of
them to be blank.

VB Code for the On Click procedure of the "Open Report" button:
Dim strWhere As String

If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = '" & Me.cmbName_Report & "'"
Else
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
End If

DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, , strWhere

A few other pointers. Avoid using Access reserved words when naming
variables or objects. (Date, Name, Time, etc.)

Qualify your object names. For example, refer to a control using either
Me.ControlName or Forms!FormName!ControName

The .Value property is not required, it is the default.

The Requery you are using

qry_ReportData.Requery

is unnecessary.


joer said:
Hi,
I'm trying to filter a report by using a combo box on a form. The
source of the report is a query (qry_ReportData) and the query uses the
combo boxes as criteria. The row source for each combo box is a table
of employee names and a separate table of machine names. I'm trying to
add "All" as an option to each combo box so that I can open a report
of, for example, all the employees who worked on 1 given machine or all
the machines that 1 employee worked on.

I've searched through the countless Group topics on adding "All" to a
combo box and none of the suggestions have worked. I've pasted my code
below, limiting the scope to one of the combo boxes (once I get the one
I can just do the same for the second). Thanks for the help.

qry_ReportData SQL:
SELECT [WO Results].Workorder, [WO Results].Assy_No, [WO
Results].WO_QTY, [WO Results].BoardsRan, [WO Results].Machine, [WO
Results].[PLCM/Board], [WO Results].WO_PLCM, [WO Results].[PLCM/Hr],
[WO Results].Date, [WO Results].Duration, [WO Results].Name, [WO
Results].Shift, [WO Results].Comments

FROM [WO Results]

WHERE ((([WO Results].Machine)=[Forms]![AI Work
Log]![cmbMachine_Report]) AND (([WO Results].Date)>[Forms]![AI Work
Log]![Date_Report]) AND (([WO Results].Name)=[Forms]![AI Work
Log]![cmbName_Report]));


Row Source SQL for Name combo box:
SELECT [tbl_Employee AI].[Full Name] FROM [tbl_Employee AI]
UNION Select "All" FROM [tbl_Employee AI];


VB Code for the On Click procedure of the "Open Report" button:
Dim stDocName As String

If cmbName_Report.Value = "All" Then
DoCmd.ShowAllRecords
stDocName = "AI Thru Hole Work Log_Operator"
DoCmd.OpenReport stDocName, acPreview
Else
qry_ReportData.Requery
stDocName = "AI Thru Hole Work Log_Operator"
DoCmd.OpenReport stDocName, acPreview
End If



Thanks!
Click to expand...
Click to expand...
Click to expand...
 
J

joer

Thanks for helping understand some of the coding requirements. I'm
still hvaing problems. When I select null from the name combo I still
get the error saying "can't find the "l" referred to in your
expression." Also, if I select a specific name from the list and
select a specific machine, I get the one name but all the machines. As
a result, the report shows all the machines in the combo whether all is
selected or not (it works without an error message when null is
selected). Any ideas? I have my code for the Click procedure and the
SQL for each combo box below. Thanks.

SQL for Name combo:
SELECT [tbl_Employee AI].[Full Name] FROM [tbl_Employee AI] UNION
Select Null as AllChoice FROM [tbl_Employee AI];

SQL for Machine combo:
SELECT [AI Machine Code].Machine FROM [AI Machine Code] UNION Select
Null as AllChoice FROM [AI Machine Code];

Code for Click Procedure:
Dim strWhere As String


If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = '" & Me.cmbName_Report & "'"
Else
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
End If
End If

DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere

Removing the "'" may or may not be correct. I put them in as a guess based
on your code. In SQL, the value you are looking for in a field has to be
delimited based on the data type of the field. Text fields require either
double or single qoutes, Date field require #, and numeric fields should have
no delimiter.
Text:
"[SomeRecordSetField] = '" & Me.SomeFormControl & "'"
Numeric
"[SomeRecordSetField] = " & Me.SomeFormControl
Date
"[SomeRecordSetField] = #" & Me.SomeFormControl & "#"

Now, the issue may be which object you are addressing, It could be my
fault, because I was unsure of what you are addressing, but the way to use
the Where condition is:
"[SomeRecordSetField] = '" & Me.SomeFormControl & "'"
SomeRecordSetField - This is a field in the query or table that is the
record source
of the report or form you want to filter
on. In this case, it
has to be a field in the record source of
the report.

Me.SomeFormControl - In this case, we are using the value contained in a
control
on your form that will be used to filter
the data in the
report. Only records where
[SomeRecordSetField] is equal
to the value in SomeFormControl will be
included in the
report.

The error you are getting means the field name in strWhere is not in the
report's recordset.

The Len() function returns the number of characters in a text object. It
can be a variable, a control, or a recordset field.

The other answers are below

joer said:
Thanks for the reply Klatuu.

I copied and pasted your code in place of my On Click procedure and
still had an error saying Access couldn't the "l" field. As a result I
removed any "'" characters in the code and still received the same
error. The new code is below, still not working, with my understanding
of each line commented out. What is the "Len()" command?

Thanks for your help,
Joe

VB Code for the On Click procedure of the "Open Report" button:

Dim strWhere As String


If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = " & Me.cmbName_Report
'If Name combo is not null, query name criteria is value of
name combo
Click to expand...
If the cmbName_Report combo is null, then [Name] will not be included in
strWhere and will not filter the reports recordset.
Else
'If Name combo is null, do this:
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
'I'm not sure what the above statement means
Click to expand...
When you are searching on multiple fields, there has to be the word And or
Or in the Where condition. This code checks to see if you are filtering on
[Name]. If you did not filter on [Name], strWhere will be a zero length
string. If you are filtering on [Name] and you are also filtering on [AI
Work Log] then you need the word And between the two conditions.
strWhere = strWhere & "[Machine] = " & Me.[cmbMachine_Report]
'Query machine criteria is value of machine combo Yes
End If
End If
DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere
'Run report with where clause Correct.



Here is a site that explains it well:
http://www.mvps.org/access/forms/frm0043.htm

However, I would suggest an alternative method in this case. Assume that if
the combo box is left empty, then that means "All".
First, omit any filtering in the Query you are using in your report. We
will do it with the Where argument of the Open Report method. Below is a
modificaiton to the code where your report is opened that will filter it
based on the values in your two combos and allowing for either or both of
them to be blank.

VB Code for the On Click procedure of the "Open Report" button:
Dim strWhere As String

If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = '" & Me.cmbName_Report & "'"
Else
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
End If

DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, , strWhere

A few other pointers. Avoid using Access reserved words when naming
variables or objects. (Date, Name, Time, etc.)

Qualify your object names. For example, refer to a control using either
Me.ControlName or Forms!FormName!ControName

The .Value property is not required, it is the default.

The Requery you are using

qry_ReportData.Requery

is unnecessary.


:

Hi,
I'm trying to filter a report by using a combo box on a form. The
source of the report is a query (qry_ReportData) and the query uses the
combo boxes as criteria. The row source for each combo box is a table
of employee names and a separate table of machine names. I'm trying to
add "All" as an option to each combo box so that I can open a report
of, for example, all the employees who worked on 1 given machine or all
the machines that 1 employee worked on.

I've searched through the countless Group topics on adding "All" to a
combo box and none of the suggestions have worked. I've pasted my code
below, limiting the scope to one of the combo boxes (once I get the one
I can just do the same for the second). Thanks for the help.

qry_ReportData SQL:
SELECT [WO Results].Workorder, [WO Results].Assy_No, [WO
Results].WO_QTY, [WO Results].BoardsRan, [WO Results].Machine, [WO
Results].[PLCM/Board], [WO Results].WO_PLCM, [WO Results].[PLCM/Hr],
[WO Results].Date, [WO Results].Duration, [WO Results].Name, [WO
Results].Shift, [WO Results].Comments

FROM [WO Results]

WHERE ((([WO Results].Machine)=[Forms]![AI Work
Log]![cmbMachine_Report]) AND (([WO Results].Date)>[Forms]![AI Work
Log]![Date_Report]) AND (([WO Results].Name)=[Forms]![AI Work
Log]![cmbName_Report]));


Row Source SQL for Name combo box:
SELECT [tbl_Employee AI].[Full Name] FROM [tbl_Employee AI]
UNION Select "All" FROM [tbl_Employee AI];


VB Code for the On Click procedure of the "Open Report" button:
Dim stDocName As String

If cmbName_Report.Value = "All" Then
DoCmd.ShowAllRecords
stDocName = "AI Thru Hole Work Log_Operator"
DoCmd.OpenReport stDocName, acPreview
Else
qry_ReportData.Requery
stDocName = "AI Thru Hole Work Log_Operator"
DoCmd.OpenReport stDocName, acPreview
End If



Thanks!
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

If you are going to just allow Null to mean no selection, then you don't need
to include the reference to Null.

SQL for Name combo:
SELECT [tbl_Employee AI].[Full Name] FROM [tbl_Employee AI];

The same would be true for the machine combo.

You code to open the report is, I think, a little off. Try this version:

If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = '" & Me.cmbName_Report & "'"
End If

If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report] & "'"
End If

DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere

Now, in the above code, the syntax indicates that [Name] and [Machine] are
both text fields. Note I modified this line:
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
to complete the enclosure in quotes
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report] & "'"


joer said:
Thanks for helping understand some of the coding requirements. I'm
still hvaing problems. When I select null from the name combo I still
get the error saying "can't find the "l" referred to in your
expression." Also, if I select a specific name from the list and
select a specific machine, I get the one name but all the machines. As
a result, the report shows all the machines in the combo whether all is
selected or not (it works without an error message when null is
selected). Any ideas? I have my code for the Click procedure and the
SQL for each combo box below. Thanks.

SQL for Name combo:
SELECT [tbl_Employee AI].[Full Name] FROM [tbl_Employee AI] UNION
Select Null as AllChoice FROM [tbl_Employee AI];

SQL for Machine combo:
SELECT [AI Machine Code].Machine FROM [AI Machine Code] UNION Select
Null as AllChoice FROM [AI Machine Code];

Code for Click Procedure:
Dim strWhere As String


If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = '" & Me.cmbName_Report & "'"
Else
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
End If
End If

DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere

Removing the "'" may or may not be correct. I put them in as a guess based
on your code. In SQL, the value you are looking for in a field has to be
delimited based on the data type of the field. Text fields require either
double or single qoutes, Date field require #, and numeric fields should have
no delimiter.
Text:
"[SomeRecordSetField] = '" & Me.SomeFormControl & "'"
Numeric
"[SomeRecordSetField] = " & Me.SomeFormControl
Date
"[SomeRecordSetField] = #" & Me.SomeFormControl & "#"

Now, the issue may be which object you are addressing, It could be my
fault, because I was unsure of what you are addressing, but the way to use
the Where condition is:
"[SomeRecordSetField] = '" & Me.SomeFormControl & "'"
SomeRecordSetField - This is a field in the query or table that is the
record source
of the report or form you want to filter
on. In this case, it
has to be a field in the record source of
the report.

Me.SomeFormControl - In this case, we are using the value contained in a
control
on your form that will be used to filter
the data in the
report. Only records where
[SomeRecordSetField] is equal
to the value in SomeFormControl will be
included in the
report.

The error you are getting means the field name in strWhere is not in the
report's recordset.

The Len() function returns the number of characters in a text object. It
can be a variable, a control, or a recordset field.

The other answers are below

joer said:
Thanks for the reply Klatuu.

I copied and pasted your code in place of my On Click procedure and
still had an error saying Access couldn't the "l" field. As a result I
removed any "'" characters in the code and still received the same
error. The new code is below, still not working, with my understanding
of each line commented out. What is the "Len()" command?

Thanks for your help,
Joe

VB Code for the On Click procedure of the "Open Report" button:

Dim strWhere As String


If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = " & Me.cmbName_Report
'If Name combo is not null, query name criteria is value of
name combo
Click to expand...
If the cmbName_Report combo is null, then [Name] will not be included in
strWhere and will not filter the reports recordset.
Else
'If Name combo is null, do this:
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
'I'm not sure what the above statement means
Click to expand...
When you are searching on multiple fields, there has to be the word And or
Or in the Where condition. This code checks to see if you are filtering on
[Name]. If you did not filter on [Name], strWhere will be a zero length
string. If you are filtering on [Name] and you are also filtering on [AI
Work Log] then you need the word And between the two conditions.
strWhere = strWhere & "[Machine] = " & Me.[cmbMachine_Report]
'Query machine criteria is value of machine combo Yes
End If
End If
DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere
'Run report with where clause Correct.




Klatuu wrote:
Here is a site that explains it well:
http://www.mvps.org/access/forms/frm0043.htm

However, I would suggest an alternative method in this case. Assume that if
the combo box is left empty, then that means "All".
First, omit any filtering in the Query you are using in your report. We
will do it with the Where argument of the Open Report method. Below is a
modificaiton to the code where your report is opened that will filter it
based on the values in your two combos and allowing for either or both of
them to be blank.

VB Code for the On Click procedure of the "Open Report" button:
Dim strWhere As String

If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = '" & Me.cmbName_Report & "'"
Else
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
End If

DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, , strWhere

A few other pointers. Avoid using Access reserved words when naming
variables or objects. (Date, Name, Time, etc.)

Qualify your object names. For example, refer to a control using either
Me.ControlName or Forms!FormName!ControName

The .Value property is not required, it is the default.

The Requery you are using

qry_ReportData.Requery

is unnecessary.


:

Hi,
I'm trying to filter a report by using a combo box on a form. The
source of the report is a query (qry_ReportData) and the query uses the
combo boxes as criteria. The row source for each combo box is a table
of employee names and a separate table of machine names. I'm trying to
add "All" as an option to each combo box so that I can open a report
of, for example, all the employees who worked on 1 given machine or all
the machines that 1 employee worked on.

I've searched through the countless Group topics on adding "All" to a
combo box and none of the suggestions have worked. I've pasted my code
below, limiting the scope to one of the combo boxes (once I get the one
I can just do the same for the second). Thanks for the help.

qry_ReportData SQL:
SELECT [WO Results].Workorder, [WO Results].Assy_No, [WO
Results].WO_QTY, [WO Results].BoardsRan, [WO Results].Machine, [WO
Results].[PLCM/Board], [WO Results].WO_PLCM, [WO Results].[PLCM/Hr],
[WO Results].Date, [WO Results].Duration, [WO Results].Name, [WO
Results].Shift, [WO Results].Comments

FROM [WO Results]

WHERE ((([WO Results].Machine)=[Forms]![AI Work
Log]![cmbMachine_Report]) AND (([WO Results].Date)>[Forms]![AI Work
Log]![Date_Report]) AND (([WO Results].Name)=[Forms]![AI Work
Log]![cmbName_Report]));


Row Source SQL for Name combo box:
SELECT [tbl_Employee AI].[Full Name] FROM [tbl_Employee AI]
UNION Select "All" FROM [tbl_Employee AI];


VB Code for the On Click procedure of the "Open Report" button:
Dim stDocName As String

If cmbName_Report.Value = "All" Then
DoCmd.ShowAllRecords
stDocName = "AI Thru Hole Work Log_Operator"
DoCmd.OpenReport stDocName, acPreview
Else
qry_ReportData.Requery
stDocName = "AI Thru Hole Work Log_Operator"
DoCmd.OpenReport stDocName, acPreview
End If



Thanks!
Click to expand...
Click to expand...
Click to expand...
 
J

joer

I'm still getting the "can't find the "l" referred to in your
expression" error message. The code checks out as far as I can tell
and I've double-checked that all the field names are accurate and
spelled correctly. Any other ideas?

Thanks,
Joe
If you are going to just allow Null to mean no selection, then you don't need
to include the reference to Null.

SQL for Name combo:
SELECT [tbl_Employee AI].[Full Name] FROM [tbl_Employee AI];

The same would be true for the machine combo.

You code to open the report is, I think, a little off. Try this version:

If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = '" & Me.cmbName_Report & "'"
End If

If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report] & "'"
End If

DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere

Now, in the above code, the syntax indicates that [Name] and [Machine] are
both text fields. Note I modified this line:
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
to complete the enclosure in quotes
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report] & "'"


joer said:
Thanks for helping understand some of the coding requirements. I'm
still hvaing problems. When I select null from the name combo I still
get the error saying "can't find the "l" referred to in your
expression." Also, if I select a specific name from the list and
select a specific machine, I get the one name but all the machines. As
a result, the report shows all the machines in the combo whether all is
selected or not (it works without an error message when null is
selected). Any ideas? I have my code for the Click procedure and the
SQL for each combo box below. Thanks.

SQL for Name combo:
SELECT [tbl_Employee AI].[Full Name] FROM [tbl_Employee AI] UNION
Select Null as AllChoice FROM [tbl_Employee AI];

SQL for Machine combo:
SELECT [AI Machine Code].Machine FROM [AI Machine Code] UNION Select
Null as AllChoice FROM [AI Machine Code];

Code for Click Procedure:
Dim strWhere As String


If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = '" & Me.cmbName_Report & "'"
Else
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
End If
End If

DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere

Removing the "'" may or may not be correct. I put them in as a guess based
on your code. In SQL, the value you are looking for in a field has to be
delimited based on the data type of the field. Text fields require either
double or single qoutes, Date field require #, and numeric fields should have
no delimiter.
Text:
"[SomeRecordSetField] = '" & Me.SomeFormControl & "'"
Numeric
"[SomeRecordSetField] = " & Me.SomeFormControl
Date
"[SomeRecordSetField] = #" & Me.SomeFormControl & "#"

Now, the issue may be which object you are addressing, It could be my
fault, because I was unsure of what you are addressing, but the way to use
the Where condition is:
"[SomeRecordSetField] = '" & Me.SomeFormControl & "'"
SomeRecordSetField - This is a field in the query or table that is the
record source
of the report or form you want to filter
on. In this case, it
has to be a field in the record source of
the report.

Me.SomeFormControl - In this case, we are using the value contained in a
control
on your form that will be used to filter
the data in the
report. Only records where
[SomeRecordSetField] is equal
to the value in SomeFormControl will be
included in the
report.

The error you are getting means the field name in strWhere is not in the
report's recordset.

The Len() function returns the number of characters in a text object. It
can be a variable, a control, or a recordset field.

The other answers are below

:

Thanks for the reply Klatuu.

I copied and pasted your code in place of my On Click procedure and
still had an error saying Access couldn't the "l" field. As a result I
removed any "'" characters in the code and still received the same
error. The new code is below, still not working, with my understanding
of each line commented out. What is the "Len()" command?

Thanks for your help,
Joe

VB Code for the On Click procedure of the "Open Report" button:

Dim strWhere As String


If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = " & Me.cmbName_Report
'If Name combo is not null, query name criteria is value of
name combo
If the cmbName_Report combo is null, then [Name] will not be included in
strWhere and will not filter the reports recordset.
Else
'If Name combo is null, do this:
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
'I'm not sure what the above statement means
When you are searching on multiple fields, there has to be the word And or
Or in the Where condition. This code checks to see if you are filtering on
[Name]. If you did not filter on [Name], strWhere will be a zero length
string. If you are filtering on [Name] and you are also filtering on [AI
Work Log] then you need the word And between the two conditions.
strWhere = strWhere & "[Machine] = " & Me.[cmbMachine_Report]
'Query machine criteria is value of machine combo
Yes
End If
End If
DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere
'Run report with where clause
Correct.




Klatuu wrote:
Here is a site that explains it well:
http://www.mvps.org/access/forms/frm0043.htm

However, I would suggest an alternative method in this case. Assume that if
the combo box is left empty, then that means "All".
First, omit any filtering in the Query you are using in your report. We
will do it with the Where argument of the Open Report method. Below is a
modificaiton to the code where your report is opened that will filter it
based on the values in your two combos and allowing for either or both of
them to be blank.

VB Code for the On Click procedure of the "Open Report" button:
Dim strWhere As String

If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = '" & Me.cmbName_Report & "'"
Else
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
End If

DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, , strWhere

A few other pointers. Avoid using Access reserved words when naming
variables or objects. (Date, Name, Time, etc.)

Qualify your object names. For example, refer to a control using either
Me.ControlName or Forms!FormName!ControName

The .Value property is not required, it is the default.

The Requery you are using

qry_ReportData.Requery

is unnecessary.


:

Hi,
I'm trying to filter a report by using a combo box on a form. The
source of the report is a query (qry_ReportData) and the query uses the
combo boxes as criteria. The row source for each combo box is a table
of employee names and a separate table of machine names. I'm trying to
add "All" as an option to each combo box so that I can open a report
of, for example, all the employees who worked on 1 given machine or all
the machines that 1 employee worked on.

I've searched through the countless Group topics on adding "All" to a
combo box and none of the suggestions have worked. I've pasted my code
below, limiting the scope to one of the combo boxes (once I get the one
I can just do the same for the second). Thanks for the help.

qry_ReportData SQL:
SELECT [WO Results].Workorder, [WO Results].Assy_No, [WO
Results].WO_QTY, [WO Results].BoardsRan, [WO Results].Machine, [WO
Results].[PLCM/Board], [WO Results].WO_PLCM, [WO Results].[PLCM/Hr],
[WO Results].Date, [WO Results].Duration, [WO Results].Name, [WO
Results].Shift, [WO Results].Comments

FROM [WO Results]

WHERE ((([WO Results].Machine)=[Forms]![AI Work
Log]![cmbMachine_Report]) AND (([WO Results].Date)>[Forms]![AI Work
Log]![Date_Report]) AND (([WO Results].Name)=[Forms]![AI Work
Log]![cmbName_Report]));


Row Source SQL for Name combo box:
SELECT [tbl_Employee AI].[Full Name] FROM [tbl_Employee AI]
UNION Select "All" FROM [tbl_Employee AI];


VB Code for the On Click procedure of the "Open Report" button:
Dim stDocName As String

If cmbName_Report.Value = "All" Then
DoCmd.ShowAllRecords
stDocName = "AI Thru Hole Work Log_Operator"
DoCmd.OpenReport stDocName, acPreview
Else
qry_ReportData.Requery
stDocName = "AI Thru Hole Work Log_Operator"
DoCmd.OpenReport stDocName, acPreview
End If



Thanks!
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

Post back with the code as you have it and point out the line on which the
error is occurring.

joer said:
I'm still getting the "can't find the "l" referred to in your
expression" error message. The code checks out as far as I can tell
and I've double-checked that all the field names are accurate and
spelled correctly. Any other ideas?

Thanks,
Joe
If you are going to just allow Null to mean no selection, then you don't need
to include the reference to Null.

SQL for Name combo:
SELECT [tbl_Employee AI].[Full Name] FROM [tbl_Employee AI];

The same would be true for the machine combo.

You code to open the report is, I think, a little off. Try this version:

If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = '" & Me.cmbName_Report & "'"
End If

If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report] & "'"
End If

DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere

Now, in the above code, the syntax indicates that [Name] and [Machine] are
both text fields. Note I modified this line:
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
to complete the enclosure in quotes
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report] & "'"


joer said:
Thanks for helping understand some of the coding requirements. I'm
still hvaing problems. When I select null from the name combo I still
get the error saying "can't find the "l" referred to in your
expression." Also, if I select a specific name from the list and
select a specific machine, I get the one name but all the machines. As
a result, the report shows all the machines in the combo whether all is
selected or not (it works without an error message when null is
selected). Any ideas? I have my code for the Click procedure and the
SQL for each combo box below. Thanks.

SQL for Name combo:
SELECT [tbl_Employee AI].[Full Name] FROM [tbl_Employee AI] UNION
Select Null as AllChoice FROM [tbl_Employee AI];

SQL for Machine combo:
SELECT [AI Machine Code].Machine FROM [AI Machine Code] UNION Select
Null as AllChoice FROM [AI Machine Code];

Code for Click Procedure:
Dim strWhere As String


If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = '" & Me.cmbName_Report & "'"
Else
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
End If
End If

DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere


Klatuu wrote:
Removing the "'" may or may not be correct. I put them in as a guess based
on your code. In SQL, the value you are looking for in a field has to be
delimited based on the data type of the field. Text fields require either
double or single qoutes, Date field require #, and numeric fields should have
no delimiter.
Text:
"[SomeRecordSetField] = '" & Me.SomeFormControl & "'"
Numeric
"[SomeRecordSetField] = " & Me.SomeFormControl
Date
"[SomeRecordSetField] = #" & Me.SomeFormControl & "#"

Now, the issue may be which object you are addressing, It could be my
fault, because I was unsure of what you are addressing, but the way to use
the Where condition is:
"[SomeRecordSetField] = '" & Me.SomeFormControl & "'"
SomeRecordSetField - This is a field in the query or table that is the
record source
of the report or form you want to filter
on. In this case, it
has to be a field in the record source of
the report.

Me.SomeFormControl - In this case, we are using the value contained in a
control
on your form that will be used to filter
the data in the
report. Only records where
[SomeRecordSetField] is equal
to the value in SomeFormControl will be
included in the
report.

The error you are getting means the field name in strWhere is not in the
report's recordset.

The Len() function returns the number of characters in a text object. It
can be a variable, a control, or a recordset field.

The other answers are below

:

Thanks for the reply Klatuu.

I copied and pasted your code in place of my On Click procedure and
still had an error saying Access couldn't the "l" field. As a result I
removed any "'" characters in the code and still received the same
error. The new code is below, still not working, with my understanding
of each line commented out. What is the "Len()" command?

Thanks for your help,
Joe

VB Code for the On Click procedure of the "Open Report" button:

Dim strWhere As String


If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = " & Me.cmbName_Report
'If Name combo is not null, query name criteria is value of
name combo
If the cmbName_Report combo is null, then [Name] will not be included in
strWhere and will not filter the reports recordset.
Else
'If Name combo is null, do this:
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
'I'm not sure what the above statement means
When you are searching on multiple fields, there has to be the word And or
Or in the Where condition. This code checks to see if you are filtering on
[Name]. If you did not filter on [Name], strWhere will be a zero length
string. If you are filtering on [Name] and you are also filtering on [AI
Work Log] then you need the word And between the two conditions.
strWhere = strWhere & "[Machine] = " & Me.[cmbMachine_Report]
'Query machine criteria is value of machine combo
Yes
End If
End If
DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere
'Run report with where clause
Correct.




Klatuu wrote:
Here is a site that explains it well:
http://www.mvps.org/access/forms/frm0043.htm

However, I would suggest an alternative method in this case. Assume that if
the combo box is left empty, then that means "All".
First, omit any filtering in the Query you are using in your report. We
will do it with the Where argument of the Open Report method. Below is a
modificaiton to the code where your report is opened that will filter it
based on the values in your two combos and allowing for either or both of
them to be blank.

VB Code for the On Click procedure of the "Open Report" button:
Dim strWhere As String

If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = '" & Me.cmbName_Report & "'"
Else
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
End If

DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, , strWhere

A few other pointers. Avoid using Access reserved words when naming
variables or objects. (Date, Name, Time, etc.)

Qualify your object names. For example, refer to a control using either
Me.ControlName or Forms!FormName!ControName

The .Value property is not required, it is the default.

The Requery you are using

qry_ReportData.Requery

is unnecessary.


:

Hi,
I'm trying to filter a report by using a combo box on a form. The
source of the report is a query (qry_ReportData) and the query uses the
combo boxes as criteria. The row source for each combo box is a table
of employee names and a separate table of machine names. I'm trying to
add "All" as an option to each combo box so that I can open a report
of, for example, all the employees who worked on 1 given machine or all
the machines that 1 employee worked on.

I've searched through the countless Group topics on adding "All" to a
combo box and none of the suggestions have worked. I've pasted my code
below, limiting the scope to one of the combo boxes (once I get the one
I can just do the same for the second). Thanks for the help.

qry_ReportData SQL:
SELECT [WO Results].Workorder, [WO Results].Assy_No, [WO
Results].WO_QTY, [WO Results].BoardsRan, [WO Results].Machine, [WO
Results].[PLCM/Board], [WO Results].WO_PLCM, [WO Results].[PLCM/Hr],
[WO Results].Date, [WO Results].Duration, [WO Results].Name, [WO
Results].Shift, [WO Results].Comments

FROM [WO Results]

WHERE ((([WO Results].Machine)=[Forms]![AI Work
Log]![cmbMachine_Report]) AND (([WO Results].Date)>[Forms]![AI Work
Log]![Date_Report]) AND (([WO Results].Name)=[Forms]![AI Work
Log]![cmbName_Report]));


Row Source SQL for Name combo box:
SELECT [tbl_Employee AI].[Full Name] FROM [tbl_Employee AI]
UNION Select "All" FROM [tbl_Employee AI];


VB Code for the On Click procedure of the "Open Report" button:
Dim stDocName As String

If cmbName_Report.Value = "All" Then
DoCmd.ShowAllRecords
stDocName = "AI Thru Hole Work Log_Operator"
DoCmd.OpenReport stDocName, acPreview
Else
qry_ReportData.Requery
stDocName = "AI Thru Hole Work Log_Operator"
DoCmd.OpenReport stDocName, acPreview
End If



Thanks!
Click to expand...
Click to expand...
Click to expand...
 
J

joer

Here's the current code:
Private Sub btn_Operator_Report_Click()
On Error GoTo Err_btn_Operator_Report_Click

If Not IsNull(Me.cmbName_Report) Then
strWhere = "[FullName] = '" & Me.cmbName_Report & "'"
End If


If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
& "'"
End If


DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere


Exit_btn_Operator_Report_Click:
Exit Sub

Err_btn_Operator_Report_Click:
MsgBox Err.Description
Resume Exit_btn_Operator_Report_Click

End Sub
Klatuu said:
Post back with the code as you have it and point out the line on which the
error is occurring.

joer said:
I'm still getting the "can't find the "l" referred to in your
expression" error message. The code checks out as far as I can tell
and I've double-checked that all the field names are accurate and
spelled correctly. Any other ideas?

Thanks,
Joe
If you are going to just allow Null to mean no selection, then you don't need
to include the reference to Null.

SQL for Name combo:
SELECT [tbl_Employee AI].[Full Name] FROM [tbl_Employee AI];

The same would be true for the machine combo.

You code to open the report is, I think, a little off. Try this version:

If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = '" & Me.cmbName_Report & "'"
End If

If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report] & "'"
End If

DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere

Now, in the above code, the syntax indicates that [Name] and [Machine] are
both text fields. Note I modified this line:
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
to complete the enclosure in quotes
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report] & "'"


:

Thanks for helping understand some of the coding requirements. I'm
still hvaing problems. When I select null from the name combo I still
get the error saying "can't find the "l" referred to in your
expression." Also, if I select a specific name from the list and
select a specific machine, I get the one name but all the machines. As
a result, the report shows all the machines in the combo whether all is
selected or not (it works without an error message when null is
selected). Any ideas? I have my code for the Click procedure and the
SQL for each combo box below. Thanks.

SQL for Name combo:
SELECT [tbl_Employee AI].[Full Name] FROM [tbl_Employee AI] UNION
Select Null as AllChoice FROM [tbl_Employee AI];

SQL for Machine combo:
SELECT [AI Machine Code].Machine FROM [AI Machine Code] UNION Select
Null as AllChoice FROM [AI Machine Code];

Code for Click Procedure:
Dim strWhere As String


If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = '" & Me.cmbName_Report & "'"
Else
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
End If
End If

DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere


Klatuu wrote:
Removing the "'" may or may not be correct. I put them in as a guess based
on your code. In SQL, the value you are looking for in a field has to be
delimited based on the data type of the field. Text fields require either
double or single qoutes, Date field require #, and numeric fields should have
no delimiter.
Text:
"[SomeRecordSetField] = '" & Me.SomeFormControl & "'"
Numeric
"[SomeRecordSetField] = " & Me.SomeFormControl
Date
"[SomeRecordSetField] = #" & Me.SomeFormControl & "#"

Now, the issue may be which object you are addressing, It could be my
fault, because I was unsure of what you are addressing, but the way to use
the Where condition is:
"[SomeRecordSetField] = '" & Me.SomeFormControl & "'"
SomeRecordSetField - This is a field in the query or table that is the
record source
of the report or form you want to filter
on. In this case, it
has to be a field in the record source of
the report.

Me.SomeFormControl - In this case, we are using the value contained in a
control
on your form that will be used to filter
the data in the
report. Only records where
[SomeRecordSetField] is equal
to the value in SomeFormControl will be
included in the
report.

The error you are getting means the field name in strWhere is not in the
report's recordset.

The Len() function returns the number of characters in a text object. It
can be a variable, a control, or a recordset field.

The other answers are below

:

Thanks for the reply Klatuu.

I copied and pasted your code in place of my On Click procedure and
still had an error saying Access couldn't the "l" field. As a result I
removed any "'" characters in the code and still received the same
error. The new code is below, still not working, with my understanding
of each line commented out. What is the "Len()" command?

Thanks for your help,
Joe

VB Code for the On Click procedure of the "Open Report" button:

Dim strWhere As String


If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = " & Me.cmbName_Report
'If Name combo is not null, query name criteria is value of
name combo
If the cmbName_Report combo is null, then [Name] will not be included in
strWhere and will not filter the reports recordset.
Else
'If Name combo is null, do this:
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
'I'm not sure what the above statement means
When you are searching on multiple fields, there has to be the word And or
Or in the Where condition. This code checks to see if you are filtering on
[Name]. If you did not filter on [Name], strWhere will be a zero length
string. If you are filtering on [Name] and you are also filtering on [AI
Work Log] then you need the word And between the two conditions.
strWhere = strWhere & "[Machine] = " & Me.[cmbMachine_Report]
'Query machine criteria is value of machine combo
Yes
End If
End If
DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere
'Run report with where clause
Correct.




Klatuu wrote:
Here is a site that explains it well:
http://www.mvps.org/access/forms/frm0043.htm

However, I would suggest an alternative method in this case. Assume that if
the combo box is left empty, then that means "All".
First, omit any filtering in the Query you are using in your report. We
will do it with the Where argument of the Open Report method. Below is a
modificaiton to the code where your report is opened that will filter it
based on the values in your two combos and allowing for either or both of
them to be blank.

VB Code for the On Click procedure of the "Open Report" button:
Dim strWhere As String

If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = '" & Me.cmbName_Report & "'"
Else
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
End If

DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, , strWhere

A few other pointers. Avoid using Access reserved words when naming
variables or objects. (Date, Name, Time, etc.)

Qualify your object names. For example, refer to a control using either
Me.ControlName or Forms!FormName!ControName

The .Value property is not required, it is the default.

The Requery you are using

qry_ReportData.Requery

is unnecessary.


:

Hi,
I'm trying to filter a report by using a combo box on a form. The
source of the report is a query (qry_ReportData) and the query uses the
combo boxes as criteria. The row source for each combo box is a table
of employee names and a separate table of machine names. I'm trying to
add "All" as an option to each combo box so that I can open a report
of, for example, all the employees who worked on 1 given machine or all
the machines that 1 employee worked on.

I've searched through the countless Group topics on adding "All" to a
combo box and none of the suggestions have worked. I've pasted my code
below, limiting the scope to one of the combo boxes (once I get the one
I can just do the same for the second). Thanks for the help.

qry_ReportData SQL:
SELECT [WO Results].Workorder, [WO Results].Assy_No, [WO
Results].WO_QTY, [WO Results].BoardsRan, [WO Results].Machine, [WO
Results].[PLCM/Board], [WO Results].WO_PLCM, [WO Results].[PLCM/Hr],
[WO Results].Date, [WO Results].Duration, [WO Results].Name, [WO
Results].Shift, [WO Results].Comments

FROM [WO Results]

WHERE ((([WO Results].Machine)=[Forms]![AI Work
Log]![cmbMachine_Report]) AND (([WO Results].Date)>[Forms]![AI Work
Log]![Date_Report]) AND (([WO Results].Name)=[Forms]![AI Work
Log]![cmbName_Report]));


Row Source SQL for Name combo box:
SELECT [tbl_Employee AI].[Full Name] FROM [tbl_Employee AI]
UNION Select "All" FROM [tbl_Employee AI];


VB Code for the On Click procedure of the "Open Report" button:
Dim stDocName As String

If cmbName_Report.Value = "All" Then
DoCmd.ShowAllRecords
stDocName = "AI Thru Hole Work Log_Operator"
DoCmd.OpenReport stDocName, acPreview
Else
qry_ReportData.Requery
stDocName = "AI Thru Hole Work Log_Operator"
DoCmd.OpenReport stDocName, acPreview
End If



Thanks!
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

You did not say where the error occurs. The code appears to be okay. Here
are some things to check:
You have a field in the report's record source named [Fullname]
It is a text field
You have a control on the form named cmbName_Report
You can expect to find the value entered in cmdName_Report in the field
FullName

You have a field in the report's record source named [Machine]
It is a text field
You have a control on the form named cmbMachine_Report
You can expect to find the value entered in cmbMachine_Report in the field
Machine

You have a report defined that is named AI Thru Hole Work Log_Operator


joer said:
Here's the current code:
Private Sub btn_Operator_Report_Click()
On Error GoTo Err_btn_Operator_Report_Click

If Not IsNull(Me.cmbName_Report) Then
strWhere = "[FullName] = '" & Me.cmbName_Report & "'"
End If


If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
& "'"
End If


DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere


Exit_btn_Operator_Report_Click:
Exit Sub

Err_btn_Operator_Report_Click:
MsgBox Err.Description
Resume Exit_btn_Operator_Report_Click

End Sub
Klatuu said:
Post back with the code as you have it and point out the line on which the
error is occurring.

joer said:
I'm still getting the "can't find the "l" referred to in your
expression" error message. The code checks out as far as I can tell
and I've double-checked that all the field names are accurate and
spelled correctly. Any other ideas?

Thanks,
Joe

Klatuu wrote:
If you are going to just allow Null to mean no selection, then you don't need
to include the reference to Null.

SQL for Name combo:
SELECT [tbl_Employee AI].[Full Name] FROM [tbl_Employee AI];

The same would be true for the machine combo.

You code to open the report is, I think, a little off. Try this version:

If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = '" & Me.cmbName_Report & "'"
End If

If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report] & "'"
End If

DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere

Now, in the above code, the syntax indicates that [Name] and [Machine] are
both text fields. Note I modified this line:
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
to complete the enclosure in quotes
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report] & "'"


:

Thanks for helping understand some of the coding requirements. I'm
still hvaing problems. When I select null from the name combo I still
get the error saying "can't find the "l" referred to in your
expression." Also, if I select a specific name from the list and
select a specific machine, I get the one name but all the machines. As
a result, the report shows all the machines in the combo whether all is
selected or not (it works without an error message when null is
selected). Any ideas? I have my code for the Click procedure and the
SQL for each combo box below. Thanks.

SQL for Name combo:
SELECT [tbl_Employee AI].[Full Name] FROM [tbl_Employee AI] UNION
Select Null as AllChoice FROM [tbl_Employee AI];

SQL for Machine combo:
SELECT [AI Machine Code].Machine FROM [AI Machine Code] UNION Select
Null as AllChoice FROM [AI Machine Code];

Code for Click Procedure:
Dim strWhere As String


If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = '" & Me.cmbName_Report & "'"
Else
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
End If
End If

DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere


Klatuu wrote:
Removing the "'" may or may not be correct. I put them in as a guess based
on your code. In SQL, the value you are looking for in a field has to be
delimited based on the data type of the field. Text fields require either
double or single qoutes, Date field require #, and numeric fields should have
no delimiter.
Text:
"[SomeRecordSetField] = '" & Me.SomeFormControl & "'"
Numeric
"[SomeRecordSetField] = " & Me.SomeFormControl
Date
"[SomeRecordSetField] = #" & Me.SomeFormControl & "#"

Now, the issue may be which object you are addressing, It could be my
fault, because I was unsure of what you are addressing, but the way to use
the Where condition is:
"[SomeRecordSetField] = '" & Me.SomeFormControl & "'"
SomeRecordSetField - This is a field in the query or table that is the
record source
of the report or form you want to filter
on. In this case, it
has to be a field in the record source of
the report.

Me.SomeFormControl - In this case, we are using the value contained in a
control
on your form that will be used to filter
the data in the
report. Only records where
[SomeRecordSetField] is equal
to the value in SomeFormControl will be
included in the
report.

The error you are getting means the field name in strWhere is not in the
report's recordset.

The Len() function returns the number of characters in a text object. It
can be a variable, a control, or a recordset field.

The other answers are below

:

Thanks for the reply Klatuu.

I copied and pasted your code in place of my On Click procedure and
still had an error saying Access couldn't the "l" field. As a result I
removed any "'" characters in the code and still received the same
error. The new code is below, still not working, with my understanding
of each line commented out. What is the "Len()" command?

Thanks for your help,
Joe

VB Code for the On Click procedure of the "Open Report" button:

Dim strWhere As String


If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = " & Me.cmbName_Report
'If Name combo is not null, query name criteria is value of
name combo
If the cmbName_Report combo is null, then [Name] will not be included in
strWhere and will not filter the reports recordset.
Else
'If Name combo is null, do this:
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
'I'm not sure what the above statement means
When you are searching on multiple fields, there has to be the word And or
Or in the Where condition. This code checks to see if you are filtering on
[Name]. If you did not filter on [Name], strWhere will be a zero length
string. If you are filtering on [Name] and you are also filtering on [AI
Work Log] then you need the word And between the two conditions.
strWhere = strWhere & "[Machine] = " & Me.[cmbMachine_Report]
'Query machine criteria is value of machine combo
Yes
End If
End If
DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere
'Run report with where clause
Correct.




Klatuu wrote:
Here is a site that explains it well:
http://www.mvps.org/access/forms/frm0043.htm

However, I would suggest an alternative method in this case. Assume that if
the combo box is left empty, then that means "All".
First, omit any filtering in the Query you are using in your report. We
will do it with the Where argument of the Open Report method. Below is a
modificaiton to the code where your report is opened that will filter it
based on the values in your two combos and allowing for either or both of
them to be blank.

VB Code for the On Click procedure of the "Open Report" button:
Dim strWhere As String

If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = '" & Me.cmbName_Report & "'"
Else
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
End If

DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, , strWhere

A few other pointers. Avoid using Access reserved words when naming
variables or objects. (Date, Name, Time, etc.)

Qualify your object names. For example, refer to a control using either
Me.ControlName or Forms!FormName!ControName

The .Value property is not required, it is the default.

The Requery you are using

qry_ReportData.Requery

is unnecessary.


:

Hi,
I'm trying to filter a report by using a combo box on a form. The
source of the report is a query (qry_ReportData) and the query uses the
combo boxes as criteria. The row source for each combo box is a table
of employee names and a separate table of machine names. I'm trying to
add "All" as an option to each combo box so that I can open a report
of, for example, all the employees who worked on 1 given machine or all
the machines that 1 employee worked on.

I've searched through the countless Group topics on adding "All" to a
combo box and none of the suggestions have worked. I've pasted my code
below, limiting the scope to one of the combo boxes (once I get the one
I can just do the same for the second). Thanks for the help.

qry_ReportData SQL:
SELECT [WO Results].Workorder, [WO Results].Assy_No, [WO
Results].WO_QTY, [WO Results].BoardsRan, [WO Results].Machine, [WO
Results].[PLCM/Board], [WO Results].WO_PLCM, [WO Results].[PLCM/Hr],
[WO Results].Date, [WO Results].Duration, [WO Results].Name, [WO
Results].Shift, [WO Results].Comments

FROM [WO Results]

WHERE ((([WO Results].Machine)=[Forms]![AI Work
Log]![cmbMachine_Report]) AND (([WO Results].Date)>[Forms]![AI Work
Log]![Date_Report]) AND (([WO Results].Name)=[Forms]![AI Work
Log]![cmbName_Report]));


Row Source SQL for Name combo box:
SELECT [tbl_Employee AI].[Full Name] FROM [tbl_Employee AI]
UNION Select "All" FROM [tbl_Employee AI];


VB Code for the On Click procedure of the "Open Report" button:
Dim stDocName As String

If cmbName_Report.Value = "All" Then
DoCmd.ShowAllRecords
stDocName = "AI Thru Hole Work Log_Operator"
DoCmd.OpenReport stDocName, acPreview
Else
qry_ReportData.Requery
stDocName = "AI Thru Hole Work Log_Operator"
DoCmd.OpenReport stDocName, acPreview
End If



Thanks!
Click to expand...
Click to expand...
Click to expand...
 
J

joer

As a follow-up I did finally get this to work with the code below (the
SQL for each combo box is in a previous post). As advised, I went
through the entire database and changed table and field names to
eliminate spaces and Access definition names (i.e., changing "Name" to
"FullName"). I also changed part of the code as AI Work Log is a table
and the second If/Then statement should be looking at
cmb_MachineReport, not AI Work Log. Thanks to Klatuu for the patience
and help.

Private Sub btn_OperatorReport_Click()
On Error GoTo Err_btn_Operator_Report_Click

If Not IsNull(Me.cmb_NameReport) Then
strWhere = "[FullName] = '" & Me.cmb_NameReport & "'"
End If


If Not IsNull(Me.cmb_MachineReport) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmb_MachineReport]
& "'"
End If


DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere
DoCmd.Maximize


Exit_btn_Operator_Report_Click:
Exit Sub

Err_btn_Operator_Report_Click:
MsgBox Err.Description
Resume Exit_btn_Operator_Report_Click
End Sub

You did not say where the error occurs. The code appears to be okay. Here
are some things to check:
You have a field in the report's record source named [Fullname]
It is a text field
You have a control on the form named cmbName_Report
You can expect to find the value entered in cmdName_Report in the field
FullName

You have a field in the report's record source named [Machine]
It is a text field
You have a control on the form named cmbMachine_Report
You can expect to find the value entered in cmbMachine_Report in the field
Machine

You have a report defined that is named AI Thru Hole Work Log_Operator


joer said:
Here's the current code:
Private Sub btn_Operator_Report_Click()
On Error GoTo Err_btn_Operator_Report_Click

If Not IsNull(Me.cmbName_Report) Then
strWhere = "[FullName] = '" & Me.cmbName_Report & "'"
End If


If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
& "'"
End If


DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere


Exit_btn_Operator_Report_Click:
Exit Sub

Err_btn_Operator_Report_Click:
MsgBox Err.Description
Resume Exit_btn_Operator_Report_Click

End Sub
Klatuu said:
Post back with the code as you have it and point out the line on which the
error is occurring.

:

I'm still getting the "can't find the "l" referred to in your
expression" error message. The code checks out as far as I can tell
and I've double-checked that all the field names are accurate and
spelled correctly. Any other ideas?

Thanks,
Joe

Klatuu wrote:
If you are going to just allow Null to mean no selection, then you don't need
to include the reference to Null.

SQL for Name combo:
SELECT [tbl_Employee AI].[Full Name] FROM [tbl_Employee AI];

The same would be true for the machine combo.

You code to open the report is, I think, a little off. Try this version:

If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = '" & Me.cmbName_Report & "'"
End If

If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report] & "'"
End If

DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere

Now, in the above code, the syntax indicates that [Name] and [Machine] are
both text fields. Note I modified this line:
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
to complete the enclosure in quotes
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report] & "'"


:

Thanks for helping understand some of the coding requirements. I'm
still hvaing problems. When I select null from the name combo I still
get the error saying "can't find the "l" referred to in your
expression." Also, if I select a specific name from the list and
select a specific machine, I get the one name but all the machines. As
a result, the report shows all the machines in the combo whether all is
selected or not (it works without an error message when null is
selected). Any ideas? I have my code for the Click procedure and the
SQL for each combo box below. Thanks.

SQL for Name combo:
SELECT [tbl_Employee AI].[Full Name] FROM [tbl_Employee AI] UNION
Select Null as AllChoice FROM [tbl_Employee AI];

SQL for Machine combo:
SELECT [AI Machine Code].Machine FROM [AI Machine Code] UNION Select
Null as AllChoice FROM [AI Machine Code];

Code for Click Procedure:
Dim strWhere As String


If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = '" & Me.cmbName_Report & "'"
Else
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
End If
End If

DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere


Klatuu wrote:
Removing the "'" may or may not be correct. I put them in as a guess based
on your code. In SQL, the value you are looking for in a field has to be
delimited based on the data type of the field. Text fields require either
double or single qoutes, Date field require #, and numeric fields should have
no delimiter.
Text:
"[SomeRecordSetField] = '" & Me.SomeFormControl & "'"
Numeric
"[SomeRecordSetField] = " & Me.SomeFormControl
Date
"[SomeRecordSetField] = #" & Me.SomeFormControl & "#"

Now, the issue may be which object you are addressing, It could be my
fault, because I was unsure of what you are addressing, but the way to use
the Where condition is:
"[SomeRecordSetField] = '" & Me.SomeFormControl & "'"
SomeRecordSetField - This is a field in the query or table that is the
record source
of the report or form you want to filter
on. In this case, it
has to be a field in the record source of
the report.

Me.SomeFormControl - In this case, we are using the value contained in a
control
on your form that will be used to filter
the data in the
report. Only records where
[SomeRecordSetField] is equal
to the value in SomeFormControl will be
included in the
report.

The error you are getting means the field name in strWhere is not in the
report's recordset.

The Len() function returns the number of characters in a text object. It
can be a variable, a control, or a recordset field.

The other answers are below

:

Thanks for the reply Klatuu.

I copied and pasted your code in place of my On Click procedure and
still had an error saying Access couldn't the "l" field. As a result I
removed any "'" characters in the code and still received the same
error. The new code is below, still not working, with my understanding
of each line commented out. What is the "Len()" command?

Thanks for your help,
Joe

VB Code for the On Click procedure of the "Open Report" button:

Dim strWhere As String


If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = " & Me.cmbName_Report
'If Name combo is not null, query name criteria is value of
name combo
If the cmbName_Report combo is null, then [Name] will not be included in
strWhere and will not filter the reports recordset.
Else
'If Name combo is null, do this:
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
'I'm not sure what the above statement means
When you are searching on multiple fields, there has to be the word And or
Or in the Where condition. This code checks to see if you are filtering on
[Name]. If you did not filter on [Name], strWhere will be a zero length
string. If you are filtering on [Name] and you are also filtering on [AI
Work Log] then you need the word And between the two conditions.
strWhere = strWhere & "[Machine] = " & Me.[cmbMachine_Report]
'Query machine criteria is value of machine combo
Yes
End If
End If
DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, ,
strWhere
'Run report with where clause
Correct.




Klatuu wrote:
Here is a site that explains it well:
http://www.mvps.org/access/forms/frm0043.htm

However, I would suggest an alternative method in this case. Assume that if
the combo box is left empty, then that means "All".
First, omit any filtering in the Query you are using in your report. We
will do it with the Where argument of the Open Report method. Below is a
modificaiton to the code where your report is opened that will filter it
based on the values in your two combos and allowing for either or both of
them to be blank.

VB Code for the On Click procedure of the "Open Report" button:
Dim strWhere As String

If Not IsNull(Me.cmbName_Report) Then
strWhere = "[Name] = '" & Me.cmbName_Report & "'"
Else
If Not IsNull(Me.[AI Work Log]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Machine] = '" & Me.[cmbMachine_Report]
End If

DoCmd.OpenReport "AI Thru Hole Work Log_Operator", acPreview, , strWhere

A few other pointers. Avoid using Access reserved words when naming
variables or objects. (Date, Name, Time, etc.)

Qualify your object names. For example, refer to a control using either
Me.ControlName or Forms!FormName!ControName

The .Value property is not required, it is the default.

The Requery you are using

qry_ReportData.Requery

is unnecessary.


:

Hi,
I'm trying to filter a report by using a combo box on a form. The
source of the report is a query (qry_ReportData) and the query uses the
combo boxes as criteria. The row source for each combo box is a table
of employee names and a separate table of machine names. I'm trying to
add "All" as an option to each combo box so that I can open a report
of, for example, all the employees who worked on 1 given machine or all
the machines that 1 employee worked on.

I've searched through the countless Group topics on adding "All" to a
combo box and none of the suggestions have worked. I've pasted my code
below, limiting the scope to one of the combo boxes (once I get the one
I can just do the same for the second). Thanks for the help.

qry_ReportData SQL:
SELECT [WO Results].Workorder, [WO Results].Assy_No, [WO
Results].WO_QTY, [WO Results].BoardsRan, [WO Results].Machine, [WO
Results].[PLCM/Board], [WO Results].WO_PLCM, [WO Results].[PLCM/Hr],
[WO Results].Date, [WO Results].Duration, [WO Results].Name, [WO
Results].Shift, [WO Results].Comments

FROM [WO Results]

WHERE ((([WO Results].Machine)=[Forms]![AI Work
Log]![cmbMachine_Report]) AND (([WO Results].Date)>[Forms]![AI Work
Log]![Date_Report]) AND (([WO Results].Name)=[Forms]![AI Work
Log]![cmbName_Report]));


Row Source SQL for Name combo box:
SELECT [tbl_Employee AI].[Full Name] FROM [tbl_Employee AI]
UNION Select "All" FROM [tbl_Employee AI];


VB Code for the On Click procedure of the "Open Report" button:
Dim stDocName As String

If cmbName_Report.Value = "All" Then
DoCmd.ShowAllRecords
stDocName = "AI Thru Hole Work Log_Operator"
DoCmd.OpenReport stDocName, acPreview
Else
qry_ReportData.Requery
stDocName = "AI Thru Hole Work Log_Operator"
DoCmd.OpenReport stDocName, acPreview
End If



Thanks!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

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

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

Ask a Question

Similar Threads

Combo Box & Report Questions 22
Filter reports by combo box 5
Combo Box selection to populate subform/subreport 0
Adding date string to exported report files names 2
reference to opened form 2
Retaining "All" in combo box 2
Select from 2 Combo boxes on a form and print the report 2
Adding and Selecting "All" to Combo Box??? 6

Top