Help Passing Criteria From Form To Query with VBA

  • Thread starter Thread starter Daveo
  • Start date Start date
D

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
 
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
 
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
 
Well, I had to give you some work to do. ;-) I did say it was UNTESTED
AIRCODE

Really, sorry about missing that bit.
 
Back
Top