using 'AND' in query only if not null

[ahvc]

Dear Team
I am using a unbound form, to get the criteria for a report, and then calculating the records for the report.

For example, let's say the 3 fields for criteria is City, Married_Status, Retired (Y/N). They user needs to select at least one criteria. My query's uses an AND operation to combine all 3 criterias. So if the user does not mention all 3 criterias, my query will fail to produce the desired result. I am using Access2000, and was searching for help on Null functions, but so far could not suceed.

Can someone throw some light for me please..
Thank you very much
VC

 

[Hayley Baxter]

I use this code to search for 1 criteria, all or a combination of 2 and 3 selections.

Private Sub Search_Click()
Dim MyDatabase As Database
Dim MyQueryDef As QueryDef
Dim where As Variant

Set MyDatabase = CurrentDb()

' Delete the existing dynamic query; trap the error if the query does
' not exist.
If ObjectExists("Queries", "qryDynamic_QBF") = True Then
MyDatabase.QueryDefs.Delete "qryDynamic_QBF"
MyDatabase.QueryDefs.Refresh
End If

where = Null
where = where & " AND [ConID]= " + Me![ConID]
where = where & " AND [CategoryID]= " + Me![CategoryID]
where = where & " AND [DonotContactID]= " + Me![DoNotContactID]
where = where & " AND [ContactStatusID]= " + Me![ContactStatusID]


If Not IsNull(Me![Meeting End Date]) Then
where = where & " AND [Meeting Date] between #" + _
Me![Meeting Start Date] + "# AND #" & Me![Meeting End Date] & "#"
Else
where = where & " AND [Meeting Date] >= #" + Me![Meeting Start Date] _
+ " #"
End If

Me.Form.Visible = False
Set MyQueryDef = MyDatabase.CreateQueryDef("qryDynamic_QBF", _
"Select * from tblProspects " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenReport "SearchResults", acViewPreview, "qryDynamic_QBF"
End Sub


If you need more help I can provide you with a sample.

Hay

 

[ahvc]

That worked..!! I used it for a recordsource, instead of a query. But it was the same idea.

thanks a bunch.
VC