Help Passing Criteria From Form To Query with VBA

[Daveo]

Hi there,

I have a form which contains the following controls:

cboHealthBoard - (Corresponds to [HealthBoardNo] field in query)
cbo1stPlacement - (Corresponds to [1stPlacement] field in query)
cbo2ndPlacement - (Corresponds to [2ndPlacement] field in query)
cbo3rdPlacement - (Corresponds to [3rdPlacement] field in query)

I want to pass the values of these fields to the query criteria whether
a user selects any combination of them and leaves the others null.

I want to use "DoCmd.OpenReport "rptName",,strWhere" but I'm having
trouble constructing my strWhere.

Any help would be much appreciated.

Thanks - David

 

[John Spencer]

Assumption:
All the values you are filtering are strings. If one (or more) are number
fields, then remove the Chr(34) where appropriate.


UNTESTED AIRCODE snippet

Dim strWhere as String

If IsNull(me.CboHealthboard) = False then
StrWhere = StrWhere & " AND HealthBoardNo = " & chr(34) &
me.CboHealthBoard & Chr(34)
End If

If IsNull(me.CboHealthboard) = False then
StrWhere = StrWhere & " AND 1stPlacement= " & chr(34) &
me.cbo1stPlacement & Chr(34)
End If

If IsNull(me.CboHealthboard) = False then
StrWhere = StrWhere & " AND 2ndPlacement= " & chr(34) &
me.cbo2ndPlacement & Chr(34)
End If

If IsNull(me.CboHealthboard) = False then
StrWhere = StrWhere & " AND 3rdPlacement= " & chr(34) &
me.cbo3rdPlacement & Chr(34)
End If

IF Len(strWhere) <> 0 Then
strWhere = Mid(strWhere, 6) 'Remove the leading " AND "
DoCmd.OpenReport "rptName",,strWhere
Else
DoCmd.OpenReport "rptName"
End if

 

[Daveo]

Dear John,

That worked brilliantly (except you forgot to change the "If
IsNull(me.xxxxx)" bits after you'd copied and pasted!).

Thankyou very very much for your help!

David

 

[John Spencer]

Well, I had to give you some work to do. ;-) I did say it was UNTESTED
AIRCODE

Really, sorry about missing that bit.