Combo box query !

[Adam]

Morning All,

I have another query.

I have a combo box which shows 3 columns from my table. It shows the
below:

Chassis Model Model YR
15 Golf 1993
16 Jetta 1988
19 Golf 1998
1C Beetle USA
1E Golf Convertable

Now I have enabled extended selection so the user can select more than
one chassis at a time.

How can I link these selections into the query?

To make things harder ! The chassis in the combo box is only the first
2 or 3 letters of a chassis number in the table. So when they select
some of the chassis it needs to look for .i.e 15* or 1C*. The user
needs to be able to select one or more chassis numbers.

Can this be done ?

Adam

 

[Adam]

This is a list box not a combo box sorry

 

[Douglas J Steele]

http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" shows one
approach.

Note that because of your last requirement, you'd have to change that code
to something like:

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from MyTable where [ChassisID] Like "
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & Chr$(34) & ctl.ItemData(varItem) & "*" & Chr$(34)
& _
" OR [ChassisID] Like "
Next varItem

'Trim the end of strSQL
strSQL=left$(strSQL,len(strSQL)-21))

 

[Adam]

Hi Douglas,

Sorry to sound stupid but what do I do with this code and how can I add
this into my query criteria ?

 

[Douglas J Steele]

All depends on what you're trying to do (open a form? open a report? ...)

Give some more details

 

[Adam]

I'm trying to run the query from the form which the list box is
contained on.

 

[Douglas J Steele]

But what's the query supposed to do?

 

[Adam]

Hi Doug,

This is the SQL of the query.

SELECT dbo_tbCaseSummary.InitialContactDate AS [Date],
dbo_tbCaseTypeAll.CaseNo, dbo_tbCaseTypeCategories.CaseType1Desc AS
[Category 1], dbo_tbCaseTypeCategories.CaseType2Desc AS [Category 2],
dbo_tbCaseTypeCategories.CaseType3Desc AS [Category 3],
dbo_tbCaseSummary.Chassis
FROM (dbo_tbCaseTypeAll INNER JOIN dbo_tbCaseSummary ON
dbo_tbCaseTypeAll.CaseNo = dbo_tbCaseSummary.RefNo) INNER JOIN
dbo_tbCaseTypeCategories ON dbo_tbCaseTypeAll.CaseType =
dbo_tbCaseTypeCategories.CaseType
WHERE (((dbo_tbCaseSummary.InitialContactDate) Between [Forms]![Cat
selection frm]![Start] And [Forms]![Cat selection frm]![End]) AND
((dbo_tbCaseTypeCategories.CaseType1Desc)=[Forms]![Cat selection
frm]![Cat1]) AND ((dbo_tbCaseTypeCategories.CaseType2Desc) Like "*" &
[Forms]![Cat selection frm]![Cat2] & "*"));


The list box field I want to search on would relate to the Chassis
field here. Like how i've done the categories
dbo_tbCaseTypeCategories.CaseType2Desc) Like "*" I want to be able to
select a number of chassis's for it to search on these in the query
criteria.

 

[Douglas J Steele]

We seem to be talking at cross purposes here. So you've got a SELECT query.
What are you doing with it? Do you use it as the Record Source for a form or
query?

You can't just plug a multi-select listbox into the SQL, the way you can a
single-select listbox or combobox. You have to build the query up
dynamically using VBA. Alternative, if you're using the query as the basis
for a form or report, you can simply build up the WHERE clause, and pass
that when you open the form or report.