Comparing multi select list box criteria against two fields

[Rana]

Hello again,

I'm trying to create a query that uses a multi select list box to set the
criteria which is then used to look up values from a table. I have written
VBA code (with lots of appreciated help from this discussion group) for
comparing the selected criteria to one field however I want it to compare the
selection to two fields. To make things clearer, I want to compare selected
skills/interests to two fields that contain these "skills/interests",
Skills/Interests Preference 1 and Skills/Interests Preference 2.

Here is my code:

'***********************************************************
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim i As Integer

Dim strSQL As String
Dim strIN As String
Dim strWhere As String

Set db = CurrentDb
strSQL = "SELECT
Skills_Interests.ROTAID,Skills_Interests.Skills_Interests_Preference1 FROM
Skills_Interests "


For i = 0 To lstSkillsInterests.ListCount - 1
If lstSkillsInterests.Selected(i) Then
strIN = strIN & "'" & lstSkillsInterests.Column(0, i) & "', "
End If
Next i

strWhere = "Where Skills_Interests.Skills_Interests_Preference1 IN( " &
Left(strIN, Len(strIN) - 1) & ")"

strSQL = strSQL & strWhere

'MsgBox strSQL

'*** delete the previous query
db.QueryDefs.Delete "SIP1"
Set qdf = db.CreateQueryDef("SIP1", strSQL)

'***********************************************************

I've tried writing out the same bit of code and creating another query,
SIP2, that compares the selection directly to the second field however when I
run the "Mother query" that collates all the info. I only get records that
contain my selection in both fields. Can I do something to the VBA code or is
it something to do with the "Mother query"? I'm not sure how clear this is
but any help would be grately appreciated.

Regards,
Rana.

 

[Michel Walsh]

Indeed, you need:

..... WHERE preference1 IN(list) OR preference2 IN(list)


while you probably have

..... WHERE preference1 IN(list) AND preference2 IN(list)


So, rather than doing 2 queries, do just one, and change


==================
strWhere = "Where Skills_Interests.Skills_Interests_Preference1 IN( " &
Left(strIN, Len(strIN) - 1) & ")"

strSQL = strSQL & strWhere
===================

to

===================
Dim theList AS String
theList = " IN( " & Left(strIN, Len(strIN) - 1) & ")"

strWhere =" WHERE Skills_Interests_Preference1 " & theList
& " OR Skills_Interests_Preference2 " & theList

strSQL = strSQL & strWhere
======================


If there is a problem, debug print strSQL and paste it in a new query (SQL
view) then try to get the design view.


Vanderghast, Access MVP

 

[Rana]

Michael, thanx for the tip it works like a charm. I now however want the
query to run even if i dont select any of the criteria from the multi-select
box. I know I should have " Is Null" somewhere but I dont know where to/how
to include it in the VBA code.

Any ideas?
Thanx.
Rana

 

[Michel Walsh]

Since you build the query at run time, it would be easier to not add the
WHERE clause:


if Len(strIn) <=1 then
' do nothing
else
strWhere = " WHERE ..... " ' as you have right now
end if



Vanderghast, Access MVP

 

[Rana]

Michel, thanx for the prompt reply. Ill have a go at it.