Using list box to pass selection to query

[wesley.allen]

I admit first that I am not very good using SQL. I am new to Access,
but I am trying to pass a selection in a list box as criteria for a
query. This list box is set to Multiselect "None", so I only need to
pass a single selection to the query. The list box is populated with
months from a table saved as "Month Table". I need to pass the
selection to a query and return all items that occur in that month.
Can anyone help.

Thanks.

 

[Sandra Daigle]

Just refer to the listbox in the criteria section for that field in the
query:

forms!MyForm!lstMyList

Replace "MyForm" with the name of your form, replace "lstMyList" with the
name of your listbox control.

 

[Wes]

Thank you very much.

 

[Wes]

Thank you very much for the reply Sandra. When I do this, the query
does not return any results. I set up a query and followed your
instruction, adding an execute button to open the query. Any idea why
it is not returning any results?

Thanks again.

 

[Sandra Daigle]

Please post the code that you are using. Also post the SQL from your query.
To get the SQL open the query in design view then click View->SQL View.

Thanks,

 

[Wes]

SELECT [Fund Name Info].[Fund Name], [Fund Name Info].[Fund Number],
[Fund Name Info].[Trust Name], [Fund Name Info].[Year End], [Fund Name
Info].[Fund Group], [Fund Name Info].Custodian, [Fund Name
Info].[Account Number], [Fund Name Info].Location, [Fund Name
Info].[Share Classes]
FROM [Fund Name Info]
WHERE ((([Fund Name Info].[Year
End])=[forms]![FundMonthListBox]![lstYearEnd]));

 

[Sandra Daigle]

In the Rowsource query of the listbox, how many fields do you have and which
one is bound to the listbox? Is the bound field the one that would match the
value in the [Year End] field of [Fund Name Info]?

Also, please post the code you are using to open the query.

 

[Wes]

Rowsource of list box:

SELECT [Month Table].Month FROM [Month Table] ORDER BY [Month];

There is only 1 field, Bound Column is set to 1.

The Year End field is the same as Month. Year End refers to the month,
so it does match the value.

To open the query, I created a button using the tool box and had it
simply open the query. The code from "On Click" is below.

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String

stDocName = "Fund Month Select"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub

 

[Sandra Daigle]

I am at a loss, unless - are the data types of the two fields different? If
not I'm not sure where else to look without actually seeing the database. If
you want to make a stripped down, and compacted copy of your database to
send to me I'll take a look. You can email it to (e-mail address removed). Delete
any tables, forms, queries and data that doesn't pertain to this problem.
Then compact the database. Save it with a different extension other than
..mdb and then email it to me and I'll have a look.