Help with code for multiple search (1 Viewer)

[Hayley Baxter]

Hi All

I am using QBF to search multiple fields then producing these results in a report this all works well. However the search items on the form are all combo boxes or text boxes, the latest addition to this form is a check box but the check box is not quite doing the job it is throwing up a data type mismatch error and as I am still not to good with vb thought some of you kind people can shed some light on this.

here is the code

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]
where = where & " AND [RequireProspectus]= " + Me![RequireProspectus]


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

The last line of the where is the checkbox named RequireProspectus.

Thanks to you all
Hay

 

[pono1]

Hayley,

Have you tried using an ampersand (&) rather than a plus sign (+) for the requireprospectus criterion?

where = where & " AND [RequireProspectus]= " _
& Me![RequireProspectus]

Regards,
Tim

 

[Hayley Baxter]

Hi Tim

Thank you so much that was all it was.

Thanks again
Hayley

 

[Hayley Baxter]

I have noticed a slight problem with using this & for requireprospectus, there's really starnge goings on with this. The rest of the fields work for searching when I use the + for the requireprospectus checkbox, however the check box gives me a data type mismatch error when I use this. If I change this to & then I get problems when searching on the other fields and the checkbox works fine BUT here is the really strange bit If I first do a search on the checkbox then secondly search on the other fields (still using & for the checkbox) then it works again)

I'm stumped If I know what's going on here???

Anyone any ideas??
Thanks in advance
Hay

 

[Jeff Bailey]

Hayley

The first thing I'd do is to change that variable name (where). It's asking for trouble to use a reserved word as a field/variable name. At the very least make it mWhere or xWhere or what-have-you.

Jeff

 

[pono1]

Hayley,

Not absolutely sure, but perhaps the function to delete the old query isn't working... Replace it with the following and see how it goes:

--CODE--
On Error Resume Next
MyDatabase.QueryDefs.Delete "qryDynamic_QBF"
On Error GoTo 0
-- END CODE --

AND / OR try using ampersands throughout to build up the WHERE clause of your SQL statement.

If your still in trouble -- could it be that there is empty criteria? You only covered yourself -- at least in code -- against a blank textbox with the Meeting dates...


-- CODE sample with more ampersands --

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

Set MyDatabase = CurrentDb()

On Error Resume Next
MyDatabase.QueryDefs.Delete "qryDynamic_QBF"
On Error GoTo 0

where = Null
where = "where " & "[ConID]= " & Me![conid] 'numeric
where = where & " AND [CategoryID]= '" & Me![categoryid] & "'" 'this is a string
where = where & " AND [DonotContactID]= " & Me![donotcontactid] 'numeric
where = where & " AND [ContactStatusID]= " & Me![contactstatusid] 'numeric
where = where & " AND [RequireProspectus]= " & Me![requireprospectus] 'numeric

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

'test
Debug.Print where
'test

'Me.Form.Visible = False

Set MyQueryDef = MyDatabase.CreateQueryDef("qryDynamic_QBF", _
"Select * from tblProspects " & where)

DoCmd.OpenQuery "qryDynamic_QBF"
'DoCmd.OpenReport "SearchResults", acViewPreview, "qryDynamic_QBF"
End Sub

Regards,
Tim