VBA query (EXPERT help needed!!)

[frankie13]

Hey guys and gals,

problem = i have i bunch of list box on a form, from which the user selects the criteria for a search. One of the list box is for selecting Age, at the moment it shows all the ages in the database (18 -920!!), from the recordsource field Age. What i would like to have is a selection of five 'BETWEEN' ages (i.e between 18 - 45, 46 - 57, etc.) in the list box for the user to select.

Unfortunately, i am using this piece of VBA code to send it to a query:-

########################
'Age code
For Each varItem In Me.lstAge.ItemsSelected
strAge = strAge & "," & Me.lstAge.ItemData(varItem)
Next varItem

'read string length, if string is 0, else create IN clause
If Len(strAge) = 0 Then
strAge = "Like'*'"
strAge = Right(strAge, Len(strAge) - 1)
strAge = "IN(" & strAge & ")"
End If

#########################
How can i change this to accept multiple BETWEEN criteria instead and then insert it into this SQL query:-

#########################
strSQL = "SELECT DISTINCT tblVolunteer.*, Year(Now())-Year([tblVolunteer]![DOB]) AS intAge, qryMedConIDjoinMedConName.MedicalConditionName, qryMedicationIDjoinMedicationName.Name " & _
"FROM (tblVolunteer LEFT JOIN qryMedConIDjoinMedConName ON tblVolunteer.SubjectID = qryMedConIDjoinMedConName.tblVolunteerMedicalCondition.SubectID) LEFT JOIN qryMedicationIDjoinMedicationName ON tblVolunteer.SubjectID = qryMedicationIDjoinMedicationName.tblVolunteerMedication.SubjectID " & _
"WHERE tblVolunteer![Gender] " & strGender & "" & _
strAgeCondition & " Year(Now())-Year([tblVolunteer]![DOB]) " & strAge & ";"

##########################

any pointer would be great!!!!

 

[ecniv]

Hi...

Hey guys and gals,

'Age code
For Each varItem In Me.lstAge.ItemsSelected
    strAge = strAge & "," & Me.lstAge.ItemData(varItem)
Next varItem

'read string length, if string is 0, um do something, else create IN clause
If Len(strAge) = 0 Then

[b]Else[/b]
    strAge = "Like'*'"
    strAge = Right(strAge, Len(strAge) - 1)
    strAge = "IN(" & strAge & ")"
End If

How can i change this to accept multiple BETWEEN criteria instead and then insert it into this SQL query:-

strSQL = "SELECT DISTINCT tblVolunteer.*, Year(Now())-Year([tblVolunteer]![DOB]) AS intAge, qryMedConIDjoinMedConName.MedicalConditionName, qryMedicationIDjoinMedicationName.Name " & _
"FROM (tblVolunteer LEFT JOIN qryMedConIDjoinMedConName ON tblVolunteer.SubjectID = qryMedConIDjoinMedConName.tblVolunteerMedicalCondition.SubectID) LEFT JOIN qryMedicationIDjoinMedicationName ON tblVolunteer.SubjectID = qryMedicationIDjoinMedicationName.tblVolunteerMedication.SubjectID " & _
"WHERE tblVolunteer![Gender] [b]='[/b] " & strGender & "[b]'[/b]" & _
strAgeCondition & " Year(Now())-Year([tblVolunteer]![DOB]) " & strAge & ";"

any pointer would be great!!!!

The bits in bold were wrong/missing...

Read up the second thread about Sql statements, see where you are going (slightly) wrong

What (data) is in the list box exactly?


Vince