Multiple Criteria in One Field

[Caroline]

I have a report based on a query right now that takes one Line of Business
and gives all the data associated with that. It gets the one Line of Business
from a prompt that I put under criteria, and it works fine.

Now I am trying to add something so users can add more than one Line of
Business. I thought about putting [LOB 1:], [LOB2:], etc under criteria but I
don't want the user to have to click through all of the LOBs if they only had
to do input one. In fact, I tried it, and once I clicked through all of them
I got all of the data back unless I wrote the same Line of Business in every
prompt.

Then I thought about making a form with tick boxes, and when the tick boxes
are ticked, it sets the criteria for the query. However, after much googling,
I am unsure of where to start. I can't even figure out how to make tick boxes
that check the details inside the field. For example, the field name is LOB,
but that includes Chile, Australia, etc. I want Chile, Australia to be the
names of the tick boxes and what it references. There are thousands of
records but only about 6 Line of Businesses.

Let me know if this is possible or if I am in way over my head. I am only a
beginner Access user...thanks!

 

[Caroline]

Wow! Thank you so much for your help! It worked beautifully.

Don't use check boxes; use a multi-select list box so that the user can
select as few or as many items as desired from the list. The list box will
always be up to date if you add a new Line of Business, whereas with check
boxes you'd have to redesign the form to add a new one. Also its exactly
this sort of thing which a multi-select list box is designed for. You can
set the list box's MultiSelect property to Simple or Extended; the first
allows the user to select by clicking one item at a time, the second by Crtl-
clicking or Shift-clicking to select ranges. With a small number of items
Simple is probably the best choice.

You don't need to include any parameters in the 'criteria' row of the query.

The RowSource property for the list box will depend on whether you have a
Lines of Business 'look-up' table with one row per line of business or not
(you should really). If you do it would be like this:

SELECT [Line of Business]
FROM [Lines of Business]
ORDER BY [Line of Business];

If not, but the lines of business can be repeated in more than one row in
another table, then it would be:

SELECT DISTINCT [Line of Business]
FROM [Some Other Table]
ORDER BY [Line of Business];

As well as the list box you need to add a button to the form to open the
report filtered to the selected lines of business, so assuming the list box
is named lstLinesOfBusiness you'd then put something like the following code
in the Click event procedure of the button:

Const conREPORT = "YourReportNameGoesHere"
Dim varItem As Variant
Dim strLOBList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.lstLinesOfBusiness

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strLOBList = strLOBList & ",""" & ctrl.ItemData(varItem) & """"
Next varItem

' remove leading comma
strLOBList = Mid(strLOBList, 2)

strCriteria = "[Line of Business] In(" & strLOBList & ")"

' open report filtered to selected LOBs
DoCmd.OpenReport conREPORT, _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
' open report unfiltered:
DoCmd.OpenReport conREPORT, _
View:=acViewPreview
End If

If you want to be able to print as well as preview the report then add
another button for this, with the same code but change View:=acViewPreview to
View:=acViewNormal.

You'll probably also want a button to clear all the selections in the list
box with one click. The code for its Click event procedure would be:

Dim n As Integer
Dim ctrl As Control

Set ctrl = Me.lstLinesOfBusiness

For n = 0 To ctrl.ListCount - 1
ctrl.Selected(n) = False
Next n

If you are not familiar with entering code in event procedures, you select
the control in form design view and open its properties sheet if its not
already open. Then select the On Click event property in the properties
sheet. Click on the 'build' button; that's the one on the right with 3 dots.
Select 'Code Builder' in the dialogue, and click OK. The VBA window will
open at the event procedure with the first and last lines already in place.
Enter the lines of code between these two existing lines.

To open a report the user would open the form, make the selections (or none
to include all data) and then click the button.

Ken Sheridan
Stafford, England

I have a report based on a query right now that takes one Line of Business
and gives all the data associated with that. It gets the one Line of Business
from a prompt that I put under criteria, and it works fine.

Now I am trying to add something so users can add more than one Line of
Business. I thought about putting [LOB 1:], [LOB2:], etc under criteria but I
don't want the user to have to click through all of the LOBs if they only had
to do input one. In fact, I tried it, and once I clicked through all of them
I got all of the data back unless I wrote the same Line of Business in every
prompt.

Then I thought about making a form with tick boxes, and when the tick boxes
are ticked, it sets the criteria for the query. However, after much googling,
I am unsure of where to start. I can't even figure out how to make tick boxes
that check the details inside the field. For example, the field name is LOB,
but that includes Chile, Australia, etc. I want Chile, Australia to be the
names of the tick boxes and what it references. There are thousands of
records but only about 6 Line of Businesses.

Let me know if this is possible or if I am in way over my head. I am only a
beginner Access user...thanks!

 

[Caroline]

Ok well it worked beautifully until I tried to add a Line of Business header
and footer in the report. Then it started showing every Line of Business
instead of only the one I chose from the list on the form. Any ideas?

Don't use check boxes; use a multi-select list box so that the user can
select as few or as many items as desired from the list. The list box will
always be up to date if you add a new Line of Business, whereas with check
boxes you'd have to redesign the form to add a new one. Also its exactly
this sort of thing which a multi-select list box is designed for. You can
set the list box's MultiSelect property to Simple or Extended; the first
allows the user to select by clicking one item at a time, the second by Crtl-
clicking or Shift-clicking to select ranges. With a small number of items
Simple is probably the best choice.

You don't need to include any parameters in the 'criteria' row of the query.

The RowSource property for the list box will depend on whether you have a
Lines of Business 'look-up' table with one row per line of business or not
(you should really). If you do it would be like this:

SELECT [Line of Business]
FROM [Lines of Business]
ORDER BY [Line of Business];

If not, but the lines of business can be repeated in more than one row in
another table, then it would be:

SELECT DISTINCT [Line of Business]
FROM [Some Other Table]
ORDER BY [Line of Business];

As well as the list box you need to add a button to the form to open the
report filtered to the selected lines of business, so assuming the list box
is named lstLinesOfBusiness you'd then put something like the following code
in the Click event procedure of the button:

Const conREPORT = "YourReportNameGoesHere"
Dim varItem As Variant
Dim strLOBList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.lstLinesOfBusiness

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strLOBList = strLOBList & ",""" & ctrl.ItemData(varItem) & """"
Next varItem

' remove leading comma
strLOBList = Mid(strLOBList, 2)

strCriteria = "[Line of Business] In(" & strLOBList & ")"

' open report filtered to selected LOBs
DoCmd.OpenReport conREPORT, _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
' open report unfiltered:
DoCmd.OpenReport conREPORT, _
View:=acViewPreview
End If

If you want to be able to print as well as preview the report then add
another button for this, with the same code but change View:=acViewPreview to
View:=acViewNormal.

You'll probably also want a button to clear all the selections in the list
box with one click. The code for its Click event procedure would be:

Dim n As Integer
Dim ctrl As Control

Set ctrl = Me.lstLinesOfBusiness

For n = 0 To ctrl.ListCount - 1
ctrl.Selected(n) = False
Next n

If you are not familiar with entering code in event procedures, you select
the control in form design view and open its properties sheet if its not
already open. Then select the On Click event property in the properties
sheet. Click on the 'build' button; that's the one on the right with 3 dots.
Select 'Code Builder' in the dialogue, and click OK. The VBA window will
open at the event procedure with the first and last lines already in place.
Enter the lines of code between these two existing lines.

To open a report the user would open the form, make the selections (or none
to include all data) and then click the button.

Ken Sheridan
Stafford, England

I have a report based on a query right now that takes one Line of Business
and gives all the data associated with that. It gets the one Line of Business
from a prompt that I put under criteria, and it works fine.

Now I am trying to add something so users can add more than one Line of
Business. I thought about putting [LOB 1:], [LOB2:], etc under criteria but I
don't want the user to have to click through all of the LOBs if they only had
to do input one. In fact, I tried it, and once I clicked through all of them
I got all of the data back unless I wrote the same Line of Business in every
prompt.

Then I thought about making a form with tick boxes, and when the tick boxes
are ticked, it sets the criteria for the query. However, after much googling,
I am unsure of where to start. I can't even figure out how to make tick boxes
that check the details inside the field. For example, the field name is LOB,
but that includes Chile, Australia, etc. I want Chile, Australia to be the
names of the tick boxes and what it references. There are thousands of
records but only about 6 Line of Businesses.

Let me know if this is possible or if I am in way over my head. I am only a
beginner Access user...thanks!

 

[Caroline]

Sorry, disregard that. I don't know what I did but it's magically fixed.

Ok well it worked beautifully until I tried to add a Line of Business header
and footer in the report. Then it started showing every Line of Business
instead of only the one I chose from the list on the form. Any ideas?

Don't use check boxes; use a multi-select list box so that the user can
select as few or as many items as desired from the list. The list box will
always be up to date if you add a new Line of Business, whereas with check
boxes you'd have to redesign the form to add a new one. Also its exactly
this sort of thing which a multi-select list box is designed for. You can
set the list box's MultiSelect property to Simple or Extended; the first
allows the user to select by clicking one item at a time, the second by Crtl-
clicking or Shift-clicking to select ranges. With a small number of items
Simple is probably the best choice.

You don't need to include any parameters in the 'criteria' row of the query.

The RowSource property for the list box will depend on whether you have a
Lines of Business 'look-up' table with one row per line of business or not
(you should really). If you do it would be like this:

SELECT [Line of Business]
FROM [Lines of Business]
ORDER BY [Line of Business];

If not, but the lines of business can be repeated in more than one row in
another table, then it would be:

SELECT DISTINCT [Line of Business]
FROM [Some Other Table]
ORDER BY [Line of Business];

As well as the list box you need to add a button to the form to open the
report filtered to the selected lines of business, so assuming the list box
is named lstLinesOfBusiness you'd then put something like the following code
in the Click event procedure of the button:

Const conREPORT = "YourReportNameGoesHere"
Dim varItem As Variant
Dim strLOBList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.lstLinesOfBusiness

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strLOBList = strLOBList & ",""" & ctrl.ItemData(varItem) & """"
Next varItem

' remove leading comma
strLOBList = Mid(strLOBList, 2)

strCriteria = "[Line of Business] In(" & strLOBList & ")"

' open report filtered to selected LOBs
DoCmd.OpenReport conREPORT, _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
' open report unfiltered:
DoCmd.OpenReport conREPORT, _
View:=acViewPreview
End If

If you want to be able to print as well as preview the report then add
another button for this, with the same code but change View:=acViewPreview to
View:=acViewNormal.

You'll probably also want a button to clear all the selections in the list
box with one click. The code for its Click event procedure would be:

Dim n As Integer
Dim ctrl As Control

Set ctrl = Me.lstLinesOfBusiness

For n = 0 To ctrl.ListCount - 1
ctrl.Selected(n) = False
Next n

If you are not familiar with entering code in event procedures, you select
the control in form design view and open its properties sheet if its not
already open. Then select the On Click event property in the properties
sheet. Click on the 'build' button; that's the one on the right with 3 dots.
Select 'Code Builder' in the dialogue, and click OK. The VBA window will
open at the event procedure with the first and last lines already in place.
Enter the lines of code between these two existing lines.

To open a report the user would open the form, make the selections (or none
to include all data) and then click the button.

Ken Sheridan
Stafford, England

I have a report based on a query right now that takes one Line of Business
and gives all the data associated with that. It gets the one Line of Business
from a prompt that I put under criteria, and it works fine.

Now I am trying to add something so users can add more than one Line of
Business. I thought about putting [LOB 1:], [LOB2:], etc under criteria but I
don't want the user to have to click through all of the LOBs if they only had
to do input one. In fact, I tried it, and once I clicked through all of them
I got all of the data back unless I wrote the same Line of Business in every
prompt.

Then I thought about making a form with tick boxes, and when the tick boxes
are ticked, it sets the criteria for the query. However, after much googling,
I am unsure of where to start. I can't even figure out how to make tick boxes
that check the details inside the field. For example, the field name is LOB,
but that includes Chile, Australia, etc. I want Chile, Australia to be the
names of the tick boxes and what it references. There are thousands of
records but only about 6 Line of Businesses.

Let me know if this is possible or if I am in way over my head. I am only a
beginner Access user...thanks!

 

[Caroline]

Well, I am thoroughly confused again. The Line of Business is working, and I
decided the follow the same format for Years. I want the user to be able to
choose which year/years they would like to view the data for. I made a table
with the years (all of the dates were mm/dd/yyyy so I just made a quick table
just with the years--not based on the dates, I just manually wrote them in).

Then I added the list box, and when I ran it could see all the years there
correctly. But now I'm a little confused as to how to add it correctly:

This is what I have so far:
----------------------------------------------
Private Sub Command2_Click()
Const conREPORT = "PMO"
Dim varItem As Variant
Dim strLOBList As String
Dim strYearList As String
Dim strCriteria As String
Dim strCriteria2 As String
Dim ctrl As Control

Set ctrl = Me.List0

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strLOBList = strLOBList & ",""" & ctrl.ItemData(varItem) & """"
strYearList = strYearList & ",""" & ctrl.ItemData(varItem) & """"
Next varItem

' remove leading comma
strLOBList = Mid(strLOBList, 2)
strYearList = Mid(strYearList, 2)

strCriteria = "[LOB Program] In(" & strLOBList & ")"
strCriteria2 = "[Completed Year] In(" & strYearList & ")"
' open report filtered to selected LOBs
DoCmd.OpenReport conREPORT, _
View:=acViewPreview, _
WhereCondition:=strCriteria And strCriteria2
Else
' open report unfiltered:
DoCmd.OpenReport conREPORT, _
View:=acViewPreview
End If

End Sub
--------------------------------------------
The biggest problem I'm having is with the WhereCondition part (I think).
I'm not sure how to get the report to run with both of those conditions. I'm
not sure if it's important to note but some entries don't have completed
years, so they return null values. I'd like the null years to always return
along with the completed years that the user has chosen.

Thanks!

Don't use check boxes; use a multi-select list box so that the user can
select as few or as many items as desired from the list. The list box will
always be up to date if you add a new Line of Business, whereas with check
boxes you'd have to redesign the form to add a new one. Also its exactly
this sort of thing which a multi-select list box is designed for. You can
set the list box's MultiSelect property to Simple or Extended; the first
allows the user to select by clicking one item at a time, the second by Crtl-
clicking or Shift-clicking to select ranges. With a small number of items
Simple is probably the best choice.

You don't need to include any parameters in the 'criteria' row of the query.

The RowSource property for the list box will depend on whether you have a
Lines of Business 'look-up' table with one row per line of business or not
(you should really). If you do it would be like this:

SELECT [Line of Business]
FROM [Lines of Business]
ORDER BY [Line of Business];

If not, but the lines of business can be repeated in more than one row in
another table, then it would be:

SELECT DISTINCT [Line of Business]
FROM [Some Other Table]
ORDER BY [Line of Business];

As well as the list box you need to add a button to the form to open the
report filtered to the selected lines of business, so assuming the list box
is named lstLinesOfBusiness you'd then put something like the following code
in the Click event procedure of the button:

Const conREPORT = "YourReportNameGoesHere"
Dim varItem As Variant
Dim strLOBList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.lstLinesOfBusiness

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strLOBList = strLOBList & ",""" & ctrl.ItemData(varItem) & """"
Next varItem

' remove leading comma
strLOBList = Mid(strLOBList, 2)

strCriteria = "[Line of Business] In(" & strLOBList & ")"

' open report filtered to selected LOBs
DoCmd.OpenReport conREPORT, _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
' open report unfiltered:
DoCmd.OpenReport conREPORT, _
View:=acViewPreview
End If

If you want to be able to print as well as preview the report then add
another button for this, with the same code but change View:=acViewPreview to
View:=acViewNormal.

You'll probably also want a button to clear all the selections in the list
box with one click. The code for its Click event procedure would be:

Dim n As Integer
Dim ctrl As Control

Set ctrl = Me.lstLinesOfBusiness

For n = 0 To ctrl.ListCount - 1
ctrl.Selected(n) = False
Next n

If you are not familiar with entering code in event procedures, you select
the control in form design view and open its properties sheet if its not
already open. Then select the On Click event property in the properties
sheet. Click on the 'build' button; that's the one on the right with 3 dots.
Select 'Code Builder' in the dialogue, and click OK. The VBA window will
open at the event procedure with the first and last lines already in place.
Enter the lines of code between these two existing lines.

To open a report the user would open the form, make the selections (or none
to include all data) and then click the button.

Ken Sheridan
Stafford, England

I have a report based on a query right now that takes one Line of Business
and gives all the data associated with that. It gets the one Line of Business
from a prompt that I put under criteria, and it works fine.

Now I am trying to add something so users can add more than one Line of
Business. I thought about putting [LOB 1:], [LOB2:], etc under criteria but I
don't want the user to have to click through all of the LOBs if they only had
to do input one. In fact, I tried it, and once I clicked through all of them
I got all of the data back unless I wrote the same Line of Business in every
prompt.

Then I thought about making a form with tick boxes, and when the tick boxes
are ticked, it sets the criteria for the query. However, after much googling,
I am unsure of where to start. I can't even figure out how to make tick boxes
that check the details inside the field. For example, the field name is LOB,
but that includes Chile, Australia, etc. I want Chile, Australia to be the
names of the tick boxes and what it references. There are thousands of
records but only about 6 Line of Businesses.

Let me know if this is possible or if I am in way over my head. I am only a
beginner Access user...thanks!

 

[Caroline]

Great! I am starting to understand this yet...
I do have one last question. I also want to include the rows in which there
is no completed date in every report, no matter what year is chosen (so as to
display all the incomplete items). I was thinking about putting an "Or Is
Null" maybe in the criteria? Is that the right way to get about doing this?
It didn't like that specifically but something like that perhaps? Would it
also have to be concatenated?

Thanks for all of your help!

You need to concatenate the two criteria strings into a single string with an
'And' in the middle, which I think is what you are attempting, but you'd not
put the 'And' as part of the string, so it would actually be interpreted as a
Boolean AND operator to evaluate whether the two criteria expressions
evaluated to TRUE in themselves rather than as criteria for the report. Yeah,
I know, it makes my brain hurt too!

But the main problem is that you are only looping through the ItemsSelected
collection of one list box, List0, which I imagine is the Lines of Business
list box. You need to loop through the years list box's ItemsSelected
collection independently of this, so if the years list box is called List2
(BTW when you add a control to a form the first thing to do should be to
change its Name property to something more meaningful like lstYears, but
don't so this after you've attached code to a control or the link between the
control and the event procedure will be broken, and you'd have to rebuild the
event procedure), the code would be:

Private Sub Command2_Click()

Const conREPORT = "PMO"
Dim varItem As Variant
Dim strLOBList As String
Dim strYearList As String
Dim strCriteria As String
Dim strCriteria2 As String
Dim ctrl As Control

Set ctrl = Me.List0

If ctrl.ItemsSelected.Count > 0 Then
' loop through lines of business list box's ItemsSelected collection
' and build comma separated list of selected items
For Each varItem In ctrl.ItemsSelected
strLOBList = strLOBList & ",""" & ctrl.ItemData(varItem) & """"
Next varItem

' remove leading comma
strLOBList = Mid(strLOBList, 2)

strCriteria = "[LOB Program] In(" & strLOBList & ")"
End If

Set ctrl = Me.List2

If ctrl.ItemsSelected.Count > 0 Then
' loop through years list box's ItemsSelected collection
' and build comma separated list of selected items
For Each varItem In ctrl.ItemsSelected
strYearList = strYearList & "," & ctrl.ItemData(varItem)
Next varItem

' remove leading comma
strYearList = Mid(strYearList, 2)

strCriteria2 = "[Completed Year] In(" & strYearList & ")"
End If

If Len(strCriteria & strCriteria2) > 0 Then
' insert an ' And ' if selections made from both list boxes
If Len(strCriteria) > 0 And Len(strCriteria2) > 0 Then
strCriteria2 = " And " & strCriteria2
End If

' open report filtered to selected LOBs and/or years
DoCmd.OpenReport conREPORT, _
View:=acViewPreview, _
WhereCondition:=strCriteria & strCriteria2
Else
' open report unfiltered:
DoCmd.OpenReport conREPORT, _
View:=acViewPreview
End If

End Sub

Note that when building the strCriteria2 string expression each selected year
is not wrapped in quotes characters as was with the lines of business. This
is because the LOB's are text values, but the years are numbers (I assume) so
don't need wrapping in quotes. Also see how whether any selections have been
made is now determined by examining the length of the two criteria strings
when concatenated together; if its more than zero something must have been
selected from at least one of the lists. Then it examines their lengths
individually to see if selections have been made from both, and if so it
inserts the ' And ' into the string.

Ken Sheridan
Stafford, England

Well, I am thoroughly confused again. The Line of Business is working, and I
decided the follow the same format for Years. I want the user to be able to
choose which year/years they would like to view the data for. I made a table
with the years (all of the dates were mm/dd/yyyy so I just made a quick table
just with the years--not based on the dates, I just manually wrote them in).

Then I added the list box, and when I ran it could see all the years there
correctly. But now I'm a little confused as to how to add it correctly:

This is what I have so far:
----------------------------------------------
Private Sub Command2_Click()
Const conREPORT = "PMO"
Dim varItem As Variant
Dim strLOBList As String
Dim strYearList As String
Dim strCriteria As String
Dim strCriteria2 As String
Dim ctrl As Control

Set ctrl = Me.List0

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strLOBList = strLOBList & ",""" & ctrl.ItemData(varItem) & """"
strYearList = strYearList & ",""" & ctrl.ItemData(varItem) & """"
Next varItem

' remove leading comma
strLOBList = Mid(strLOBList, 2)
strYearList = Mid(strYearList, 2)

strCriteria = "[LOB Program] In(" & strLOBList & ")"
strCriteria2 = "[Completed Year] In(" & strYearList & ")"
' open report filtered to selected LOBs
DoCmd.OpenReport conREPORT, _
View:=acViewPreview, _
WhereCondition:=strCriteria And strCriteria2
Else
' open report unfiltered:
DoCmd.OpenReport conREPORT, _
View:=acViewPreview
End If

End Sub
--------------------------------------------
The biggest problem I'm having is with the WhereCondition part (I think).
I'm not sure how to get the report to run with both of those conditions. I'm
not sure if it's important to note but some entries don't have completed
years, so they return null values. I'd like the null years to always return
along with the completed years that the user has chosen.

Thanks!

Don't use check boxes; use a multi-select list box so that the user can
select as few or as many items as desired from the list. The list box will

[quoted text clipped - 109 lines]

Let me know if this is possible or if I am in way over my head. I am only a
beginner Access user...thanks!

 

[Caroline]

I had the right idea but not the right syntax. Thank you for all of your help!

Yes, you do test for the Completed Year being Null in a Boolean OR operation.
You'd concatenate this to strCriteria2. The OR operation also needs to be
enclosed in parentheses to force it to evaluate independently of the AND
operation with the lines of business, so the amended code would be.

strCriteria2 = "([Completed Year] In(" & strYearList & ")" & _
" Or [Completed Year] Is Null)"

Ken Sheridan
Stafford, England

Great! I am starting to understand this yet...
I do have one last question. I also want to include the rows in which there
is no completed date in every report, no matter what year is chosen (so as to
display all the incomplete items). I was thinking about putting an "Or Is
Null" maybe in the criteria? Is that the right way to get about doing this?
It didn't like that specifically but something like that perhaps? Would it
also have to be concatenated?

Thanks for all of your help!

You need to concatenate the two criteria strings into a single string with an
'And' in the middle, which I think is what you are attempting, but you'd not

[quoted text clipped - 145 lines]

Let me know if this is possible or if I am in way over my head. I am only a
beginner Access user...thanks!

 

[Caroline]

Well, I'm not even sure if my next question is feasible. I figured I would
ask here since you will see it, but maybe not see it if i posted it to
everyone.

I have other options I want in there now, and the options will show
different data in the report. However, the fields listed in the listbox are
not the same as the fields in the query, so I cannot do the same thing as I
have been doing. For example, I want the three options to be Touch Labor,
Support Labor, and Material Labor. There is no column in the query where I
can match these options as I could before, but if the user selects Touch
Labor, I want the data associated with touch labor, such as touch costs and
touch hours, to display in the report. I want the user to be able to choose
more than one if they want, so Touch Labor and Support Labor could show up if
chosen. The one query has all the necessary data for all options.
I was wondering if there is a way to do this easily. I was thinking about
doing something like if the value of the listbox = "Touch" show the touch
report. And just make all the different reports in the background. So make a
Touch report, a Support report, a Touch and Support report, etc. I don't
think this would be too difficult but I can't figure out just how to get the
value of the selected item in the listbox. Maybe cases? I also wasn't sure
where to put this criteria in the code. I want to do the same thing with
Hours and Costs. So in the end, they will be able to choose between LOB,
Year (which I already have), Labor, and whether they want the hours or
dollars saved (or both). If I can figure out how to do the labor one, I can
probably figure out how to do the last one.

Sorry for all the questions. I understand if you don't want to answer
anymore...haha. I've tried googling a good deal before asking this question
but am not even sure where to start or what to google.

Yes, you do test for the Completed Year being Null in a Boolean OR operation.
You'd concatenate this to strCriteria2. The OR operation also needs to be
enclosed in parentheses to force it to evaluate independently of the AND
operation with the lines of business, so the amended code would be.

strCriteria2 = "([Completed Year] In(" & strYearList & ")" & _
" Or [Completed Year] Is Null)"

Ken Sheridan
Stafford, England

Great! I am starting to understand this yet...
I do have one last question. I also want to include the rows in which there
is no completed date in every report, no matter what year is chosen (so as to
display all the incomplete items). I was thinking about putting an "Or Is
Null" maybe in the criteria? Is that the right way to get about doing this?
It didn't like that specifically but something like that perhaps? Would it
also have to be concatenated?

Thanks for all of your help!

You need to concatenate the two criteria strings into a single string with an
'And' in the middle, which I think is what you are attempting, but you'd not

[quoted text clipped - 145 lines]

Let me know if this is possible or if I am in way over my head. I am only a
beginner Access user...thanks!

 

[Caroline]

I see...
I do have separate columns in my main query for Touch Labor, Support Labor,
and Material Labor. So you want me to make these columns into rows? I looked
up how to do that and found this page:
http://bytes.com/topic/access/answers/555580-turning-columns-into-rows-any-easy-way
Is this something like you were referring to?

I also made a table with just three rows in it (Touch, Support, and
Material), where LaborType is the primary key. So then I will create this
junction table with LaborType as one primary key, and the primary key of the
new table that I'd be making using the append query as mentioned above. Is
that correct? And then this junction table I've created will be the one I
reference in my form when writing the code?

Thanks!
PS- Your week off sounds amazing! Have fun!

You are probably going to hate me for saying this, but if it can't be done
from the data then there is something wrong with the 'logical model', i.e.
the structure of the database in terms of the tables, the relationships
between them and the columns in the tables. What you seem to have here are
three attributes of a Labor entity type. In a relational database tables
model entity types and columns model their attributes.

With labor modelled by a table with a primary key column such as LaborType
you can then join another table to it by including a foreign key LaborType
column in that table. Or if a row in another table can be of more than one
LaborType simultaneously, which would mean it has a many-to-many relationship
with Labor, you'd model this with another table (sometimes referred to as a
'junction table') which has two foreign key columns referencing the primary
keys of Labor and the other table.

Of course, once you have the labor type represented by values in a column in
the report's underlying query in this way you it’s a simple task to restrict
the results in the same way as before.

I hope you haven't included three separate columns in a table for Touch Labor,
Support Labor, and Material Labor. That's a very bad design flaw, though
unfortunately an all too common one. Its what's known as 'encoding data as
column headings'. A fundamental principle of the database relational model
(the Information Principle) is that data is stored as values at row positions
in tables and in no other way. If you have done this the situation is
retrievable rather more easily than you might think by recasting the data
into rows in a related table using 'append' queries.

I'm afraid I'm only going to be at your beck and call for another day, then
I'm off for a week's communing with nature on the Pembrokeshire coast. There
won't be a computer in sight I'm glad to say! I'm signing off for the day
now; we are at least 5 hours ahead of you, remember.

Ken Sheridan
Stafford, England

Well, I'm not even sure if my next question is feasible. I figured I would
ask here since you will see it, but maybe not see it if i posted it to
everyone.

I have other options I want in there now, and the options will show
different data in the report. However, the fields listed in the listbox are
not the same as the fields in the query, so I cannot do the same thing as I
have been doing. For example, I want the three options to be Touch Labor,
Support Labor, and Material Labor. There is no column in the query where I
can match these options as I could before, but if the user selects Touch
Labor, I want the data associated with touch labor, such as touch costs and
touch hours, to display in the report. I want the user to be able to choose
more than one if they want, so Touch Labor and Support Labor could show up if
chosen. The one query has all the necessary data for all options.
I was wondering if there is a way to do this easily. I was thinking about
doing something like if the value of the listbox = "Touch" show the touch
report. And just make all the different reports in the background. So make a
Touch report, a Support report, a Touch and Support report, etc. I don't
think this would be too difficult but I can't figure out just how to get the
value of the selected item in the listbox. Maybe cases? I also wasn't sure
where to put this criteria in the code. I want to do the same thing with
Hours and Costs. So in the end, they will be able to choose between LOB,
Year (which I already have), Labor, and whether they want the hours or
dollars saved (or both). If I can figure out how to do the labor one, I can
probably figure out how to do the last one.

Sorry for all the questions. I understand if you don't want to answer
anymore...haha. I've tried googling a good deal before asking this question
but am not even sure where to start or what to google.

Yes, you do test for the Completed Year being Null in a Boolean OR operation.
You'd concatenate this to strCriteria2. The OR operation also needs to be

[quoted text clipped - 22 lines]

Let me know if this is possible or if I am in way over my head. I am only a
beginner Access user...thanks!