Two Multi-Select List Boxes as Filters

[Stacey Crowhurst]

Hi. I have a form with two multi-select list boxes. One "listMonth" allows
the user to select months, the other "listYear" selects year. This box then
acts as a filter for my transaction report "rptTransactionsByPeriod". So if
you pick March and April and 2007 it will only show transactions dated in
March and April 2007. I have the month part working, but I am unsure how to
add in the year part. Any help is appreciated! Thanks in advance Stacey
[FYI: my year field is yYearID]

Here is the code I have so far:

Private Sub cmdViewTransactions_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDoc = "rptTransactionsByPeriod"


With Me.listMonth
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[mMonthID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

 

[Ken Snell MVP]

Not sure that I fully understand your question.... can the user select more
than one "year" from the listYear listbox? If yes, how do you associate a
year to a month? Is the intent that, if the user selects March and April in
the listMonth listbox, and 2008 and 2009 in the listYear listbox, that you'd
want values for March 2008, March 2009, April 2008, and April 2009?

 

[Stacey Crowhurst]

Ken,
I see what you mean. I'll change my list box so that each month is already
associated with a year, and then I'll only need one list box on the form.

As a follow up though, my company's fiscal year runs from August to July.
Ideally, I want a separate form to have a list box where the user can select
FY07 or FY08 or FY09, etc. I'll have to figure out how to tell access that
FY07 equals August 2006 - July 2007.

Thanks for the follow up.
Stacey

Not sure that I fully understand your question.... can the user select more
than one "year" from the listYear listbox? If yes, how do you associate a
year to a month? Is the intent that, if the user selects March and April in
the listMonth listbox, and 2008 and 2009 in the listYear listbox, that you'd
want values for March 2008, March 2009, April 2008, and April 2009?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi. I have a form with two multi-select list boxes. One "listMonth"
allows
the user to select months, the other "listYear" selects year. This box
then
acts as a filter for my transaction report "rptTransactionsByPeriod". So
if
you pick March and April and 2007 it will only show transactions dated in
March and April 2007. I have the month part working, but I am unsure how
to
add in the year part. Any help is appreciated! Thanks in advance
Stacey
[FYI: my year field is yYearID]

Here is the code I have so far:

Private Sub cmdViewTransactions_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDoc = "rptTransactionsByPeriod"


With Me.listMonth
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) & """,
"
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[mMonthID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

 

[Ken Snell MVP]

RE: "I'll have to figure out how to tell access that FY07 equals August
2006 - July 2007."

You can do this in a variety of ways. Assuming that the fiscal year is not
going to change for the business, you could hardcode into your query the
date ranges based on months, and then use the year from the FY07 to fill in
the year amounts:

SELECT *
FROM TableName
WHERE DateField Between
DateSerial([YearFromFY07ListBox]), 8, 1) And
DateSerial([YearFromFY07ListBox] + 1), 7, 31);

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Ken,
I see what you mean. I'll change my list box so that each month is
already
associated with a year, and then I'll only need one list box on the form.

As a follow up though, my company's fiscal year runs from August to July.
Ideally, I want a separate form to have a list box where the user can
select
FY07 or FY08 or FY09, etc. I'll have to figure out how to tell access
that
FY07 equals August 2006 - July 2007.

Thanks for the follow up.
Stacey

Not sure that I fully understand your question.... can the user select
more
than one "year" from the listYear listbox? If yes, how do you associate a
year to a month? Is the intent that, if the user selects March and April
in
the listMonth listbox, and 2008 and 2009 in the listYear listbox, that
you'd
want values for March 2008, March 2009, April 2008, and April 2009?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi. I have a form with two multi-select list boxes. One "listMonth"
allows
the user to select months, the other "listYear" selects year. This box
then
acts as a filter for my transaction report "rptTransactionsByPeriod".
So
if
you pick March and April and 2007 it will only show transactions dated
in
March and April 2007. I have the month part working, but I am unsure
how
to
add in the year part. Any help is appreciated! Thanks in advance
Stacey
[FYI: my year field is yYearID]

Here is the code I have so far:

Private Sub cmdViewTransactions_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDoc = "rptTransactionsByPeriod"


With Me.listMonth
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) &
""",
"
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[mMonthID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

 

[Stacey Crowhurst]

Hi Ken. I'm almost there! My sql is wrong somewhere though.

WHERE ((tblTransactions.trCheckDate) Between
DateSerial(([Forms]![flkpTransactionsByFiscalYear]![listFiscalYears],[yYearID]),8,1)
And
DateSerial(([Forms]![flkpTransactionsByFiscalYear]![listFiscalYears],[yYearID]+1),7,31))

I wasn't sure how to reference the list box. What should I be doing
differently?
Thanks for the help!!!

RE: "I'll have to figure out how to tell access that FY07 equals August
2006 - July 2007."

You can do this in a variety of ways. Assuming that the fiscal year is not
going to change for the business, you could hardcode into your query the
date ranges based on months, and then use the year from the FY07 to fill in
the year amounts:

SELECT *
FROM TableName
WHERE DateField Between
DateSerial([YearFromFY07ListBox]), 8, 1) And
DateSerial([YearFromFY07ListBox] + 1), 7, 31);

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Ken,
I see what you mean. I'll change my list box so that each month is
already
associated with a year, and then I'll only need one list box on the form.

As a follow up though, my company's fiscal year runs from August to July.
Ideally, I want a separate form to have a list box where the user can
select
FY07 or FY08 or FY09, etc. I'll have to figure out how to tell access
that
FY07 equals August 2006 - July 2007.

Thanks for the follow up.
Stacey

Not sure that I fully understand your question.... can the user select
more
than one "year" from the listYear listbox? If yes, how do you associate a
year to a month? Is the intent that, if the user selects March and April
in
the listMonth listbox, and 2008 and 2009 in the listYear listbox, that
you'd
want values for March 2008, March 2009, April 2008, and April 2009?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message Hi. I have a form with two multi-select list boxes. One "listMonth"
allows
the user to select months, the other "listYear" selects year. This box
then
acts as a filter for my transaction report "rptTransactionsByPeriod".
So
if
you pick March and April and 2007 it will only show transactions dated
in
March and April 2007. I have the month part working, but I am unsure
how
to
add in the year part. Any help is appreciated! Thanks in advance
Stacey
[FYI: my year field is yYearID]

Here is the code I have so far:

Private Sub cmdViewTransactions_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDoc = "rptTransactionsByPeriod"


With Me.listMonth
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) &
""",
"
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[mMonthID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

 

[Ken Snell MVP]

Very close. Change the commas to ! characters:

WHERE ((tblTransactions.trCheckDate) Between
DateSerial(([Forms]![flkpTransactionsByFiscalYear]![listFiscalYears]![yYearID]),8,1)
And
DateSerial(([Forms]![flkpTransactionsByFiscalYear]![listFiscalYears]![yYearID]+1),7,31))

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Ken. I'm almost there! My sql is wrong somewhere though.

WHERE ((tblTransactions.trCheckDate) Between
DateSerial(([Forms]![flkpTransactionsByFiscalYear]![listFiscalYears],[yYearID]),8,1)
And
DateSerial(([Forms]![flkpTransactionsByFiscalYear]![listFiscalYears],[yYearID]+1),7,31))

I wasn't sure how to reference the list box. What should I be doing
differently?
Thanks for the help!!!

RE: "I'll have to figure out how to tell access that FY07 equals August
2006 - July 2007."

You can do this in a variety of ways. Assuming that the fiscal year is
not
going to change for the business, you could hardcode into your query the
date ranges based on months, and then use the year from the FY07 to fill
in
the year amounts:

SELECT *
FROM TableName
WHERE DateField Between
DateSerial([YearFromFY07ListBox]), 8, 1) And
DateSerial([YearFromFY07ListBox] + 1), 7, 31);

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Ken,
I see what you mean. I'll change my list box so that each month is
already
associated with a year, and then I'll only need one list box on the
form.

As a follow up though, my company's fiscal year runs from August to
July.
Ideally, I want a separate form to have a list box where the user can
select
FY07 or FY08 or FY09, etc. I'll have to figure out how to tell access
that
FY07 equals August 2006 - July 2007.

Thanks for the follow up.
Stacey

:

Not sure that I fully understand your question.... can the user select
more
than one "year" from the listYear listbox? If yes, how do you
associate a
year to a month? Is the intent that, if the user selects March and
April
in
the listMonth listbox, and 2008 and 2009 in the listYear listbox, that
you'd
want values for March 2008, March 2009, April 2008, and April 2009?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


in
message Hi. I have a form with two multi-select list boxes. One
"listMonth"
allows
the user to select months, the other "listYear" selects year. This
box
then
acts as a filter for my transaction report
"rptTransactionsByPeriod".
So
if
you pick March and April and 2007 it will only show transactions
dated
in
March and April 2007. I have the month part working, but I am
unsure
how
to
add in the year part. Any help is appreciated! Thanks in advance

Stacey
[FYI: my year field is yYearID]

Here is the code I have so far:

Private Sub cmdViewTransactions_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDoc = "rptTransactionsByPeriod"


With Me.listMonth
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem)
&
""",
"
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[mMonthID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

 

[Stacey Crowhurst]

Thanks Ken. I got that in but now another hiccup! I get the "too complex"
error message when trying to execute. Here is the complete SQL:

SELECT tblTransactions.trCCPID, tblProjects.prjProjectName,
tblTransactionDetail.tdVendorID, tblVendors.vdVendorName,
tblTransactions.trDateEntered, tblTransactionStatus.tstStatusDesc,
tblTransactions.trCheckNo, tblTransactionSource.tsoSourceDesc,
tblTransactionDetail.tdInvoiceNo, tblTransactionDetail.tdInvoiceDate,
tblTransactionDetail.tdContractID, tblBudgetCodes.bcBudgetCodeID,
tblBudgetCodes.bcBudgetCodeDesc, tblCostCodes.cocCostCodeID,
tblCostCodes.cocCostCodeDesc, Sum(tblTransactionDetail.tdLineAmount) AS
tdLineAmount
FROM ((((((tblTransactionDetail INNER JOIN tblTransactions ON
tblTransactionDetail.tdtrAutoNumberID = tblTransactions.trAutoNumberID) INNER
JOIN tblVendors ON tblTransactionDetail.tdVendorID = tblVendors.vdVendorID)
INNER JOIN tblTransactionSource ON tblTransactions.trSource =
tblTransactionSource.tsoSourceID) INNER JOIN tblTransactionStatus ON
tblTransactions.trStatusID = tblTransactionStatus.tstStatusID) INNER JOIN
tblCostCodes ON tblTransactionDetail.tdCostCodeID =
tblCostCodes.cocCostCodeID) INNER JOIN tblBudgetCodes ON
tblCostCodes.cocBudgetCodeID = tblBudgetCodes.bcBudgetCodeID) INNER JOIN
tblProjects ON tblTransactions.trCCPID = tblProjects.prjCCPID
WHERE (((tblTransactions.trCheckDate) Between
DateSerial(([Forms]![flkpTransactionsByFiscalYear]![listFiscalYears]![yYearID]),8,1)
And
DateSerial(([Forms]![flkpTransactionsByFiscalYear]![listFiscalYears]![yYearID]+1),7,31)))
GROUP BY tblTransactions.trCCPID, tblProjects.prjProjectName,
tblTransactionDetail.tdVendorID, tblVendors.vdVendorName,
tblTransactions.trDateEntered, tblTransactionStatus.tstStatusDesc,
tblTransactions.trCheckNo, tblTransactionSource.tsoSourceDesc,
tblTransactionDetail.tdInvoiceNo, tblTransactionDetail.tdInvoiceDate,
tblTransactionDetail.tdContractID, tblBudgetCodes.bcBudgetCodeID,
tblBudgetCodes.bcBudgetCodeDesc, tblCostCodes.cocCostCodeID,
tblCostCodes.cocCostCodeDesc;

Thanks again for your help, though!
Stacey

 

[Ken Snell MVP]

This expression, I'm guessing, is the cause of the error:

[Forms]![flkpTransactionsByFiscalYear]![listFiscalYears]![yYearID]


What is listFiscalYears? Isn't that the listbox? So what is yYearID?

 

[Stacey Crowhurst]

listFiscalYears is my list box. yYearID is the primary key ID I assigned to
fiscal years table. I made a table 'tblFiscalYears' and it looks like this...

yYearID yFiscalYear
2004 FY05
2005 FY06
2006 FY07
2007 FY08
etc. up to
2014 FY15

Then I used that to create the list box having yYearID as column 1 (bound
but not shown) and yFiscalYear as shown and selectable.

I change the query sql to remove yYearID and now the query functions great.
But, I have one little thing left. In the code I copied for my list box form
I am not sure what field to put here

strWhere = "[yYearID] IN (" & Left$(strWhere, lngLen) & ")"

I copied the code from a useful Allen Browne website. Anyway, let me know
what you think. Thanks again for all of your assistance

This expression, I'm guessing, is the cause of the error:

[Forms]![flkpTransactionsByFiscalYear]![listFiscalYears]![yYearID]


What is listFiscalYears? Isn't that the listbox? So what is yYearID?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thanks Ken. I got that in but now another hiccup! I get the "too
complex"
error message when trying to execute. Here is the complete SQL:

SELECT tblTransactions.trCCPID, tblProjects.prjProjectName,
tblTransactionDetail.tdVendorID, tblVendors.vdVendorName,
tblTransactions.trDateEntered, tblTransactionStatus.tstStatusDesc,
tblTransactions.trCheckNo, tblTransactionSource.tsoSourceDesc,
tblTransactionDetail.tdInvoiceNo, tblTransactionDetail.tdInvoiceDate,
tblTransactionDetail.tdContractID, tblBudgetCodes.bcBudgetCodeID,
tblBudgetCodes.bcBudgetCodeDesc, tblCostCodes.cocCostCodeID,
tblCostCodes.cocCostCodeDesc, Sum(tblTransactionDetail.tdLineAmount) AS
tdLineAmount
FROM ((((((tblTransactionDetail INNER JOIN tblTransactions ON
tblTransactionDetail.tdtrAutoNumberID = tblTransactions.trAutoNumberID)
INNER
JOIN tblVendors ON tblTransactionDetail.tdVendorID =
tblVendors.vdVendorID)
INNER JOIN tblTransactionSource ON tblTransactions.trSource =
tblTransactionSource.tsoSourceID) INNER JOIN tblTransactionStatus ON
tblTransactions.trStatusID = tblTransactionStatus.tstStatusID) INNER JOIN
tblCostCodes ON tblTransactionDetail.tdCostCodeID =
tblCostCodes.cocCostCodeID) INNER JOIN tblBudgetCodes ON
tblCostCodes.cocBudgetCodeID = tblBudgetCodes.bcBudgetCodeID) INNER JOIN
tblProjects ON tblTransactions.trCCPID = tblProjects.prjCCPID
WHERE (((tblTransactions.trCheckDate) Between
DateSerial(([Forms]![flkpTransactionsByFiscalYear]![listFiscalYears]![yYearID]),8,1)
And
DateSerial(([Forms]![flkpTransactionsByFiscalYear]![listFiscalYears]![yYearID]+1),7,31)))
GROUP BY tblTransactions.trCCPID, tblProjects.prjProjectName,
tblTransactionDetail.tdVendorID, tblVendors.vdVendorName,
tblTransactions.trDateEntered, tblTransactionStatus.tstStatusDesc,
tblTransactions.trCheckNo, tblTransactionSource.tsoSourceDesc,
tblTransactionDetail.tdInvoiceNo, tblTransactionDetail.tdInvoiceDate,
tblTransactionDetail.tdContractID, tblBudgetCodes.bcBudgetCodeID,
tblBudgetCodes.bcBudgetCodeDesc, tblCostCodes.cocCostCodeID,
tblCostCodes.cocCostCodeDesc;

Thanks again for your help, though!
Stacey

 

[Ken Snell MVP]

You'll need to show all the code in which the one code line is being used.
Without the context, it's difficult to say what you should change.

 

[Stacey Crowhurst]

Here is the code that tells the command button "cmdViewTransactions" on my
list box form how to filter the transaction report
"rptTransactionsByFiscalYear", which is based on the query
"qryTransactionDetailbyFiscalYear" that has the date serial function.

Private Sub cmdViewTransactions_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDelim = """"
strDoc = "rptTransactionsByFiscalYear"


With Me.listFiscalYears
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[yYearID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub


Thanks, Ken!

You'll need to show all the code in which the one code line is being used.
Without the context, it's difficult to say what you should change.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

listFiscalYears is my list box. yYearID is the primary key ID I assigned
to
fiscal years table. I made a table 'tblFiscalYears' and it looks like
this...

yYearID yFiscalYear
2004 FY05
2005 FY06
2006 FY07
2007 FY08
etc. up to
2014 FY15

Then I used that to create the list box having yYearID as column 1 (bound
but not shown) and yFiscalYear as shown and selectable.

I change the query sql to remove yYearID and now the query functions
great.
But, I have one little thing left. In the code I copied for my list box
form
I am not sure what field to put here

strWhere = "[yYearID] IN (" & Left$(strWhere, lngLen) & ")"

I copied the code from a useful Allen Browne website. Anyway, let me know
what you think. Thanks again for all of your assistance

 

[Ken Snell MVP]

Do I understand that your question is, "which field name do I put in this
code line:
strWhere = "[yYearID] IN (" & Left$(strWhere, lngLen) & ")"

If yes, the [yYearID] would be replaced by the name of the field in the
report's RecordSource query that contains the values on which you want the
query to be filtered. This expression will be used to filter the report.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Here is the code that tells the command button "cmdViewTransactions" on my
list box form how to filter the transaction report
"rptTransactionsByFiscalYear", which is based on the query
"qryTransactionDetailbyFiscalYear" that has the date serial function.

Private Sub cmdViewTransactions_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDelim = """"
strDoc = "rptTransactionsByFiscalYear"


With Me.listFiscalYears
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) & """,
"
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[yYearID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub


Thanks, Ken!

You'll need to show all the code in which the one code line is being
used.
Without the context, it's difficult to say what you should change.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

listFiscalYears is my list box. yYearID is the primary key ID I
assigned
to
fiscal years table. I made a table 'tblFiscalYears' and it looks like
this...

yYearID yFiscalYear
2004 FY05
2005 FY06
2006 FY07
2007 FY08
etc. up to
2014 FY15

Then I used that to create the list box having yYearID as column 1
(bound
but not shown) and yFiscalYear as shown and selectable.

I change the query sql to remove yYearID and now the query functions
great.
But, I have one little thing left. In the code I copied for my list
box
form
I am not sure what field to put here

strWhere = "[yYearID] IN (" & Left$(strWhere, lngLen) & ")"

I copied the code from a useful Allen Browne website. Anyway, let me
know
what you think. Thanks again for all of your assistance