Query By Form Like Statement

[jonhanks]

I'm trying to search by form a field in a table called 'subject' via
two combo boxes on a form. In the query I have the following criteria
in the 'Subject' field:

(Like "*" & [Forms]![frmFind]![cmbOp] & "*") Or (Like "*" &
[Forms]![frmFind]![cmbSub] & "*")

This seems to work fine untill I leave either [cmbOp] blank, or
[cmbSub] blank - at which point it returns ALL records with no blank
Subject field. Any ideas?

many thanks

jon

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Instead of using the Form reference in the query, you'll have to
dynamically create the SQL string in VBA. E.g.:

If not IsNull(Me!cmbOp) Then
strWhere = strWhere & " Like "'*" & Me!cmbOp & "*'"
End If

If Not IsNull(me!cmbSub) Then
strWhere = strWhere & " Like '*" & Me!cmbSub & "*'"
End If

If Len(strWhere)>0 Then
strSQL = strSQL & strWhere
End If

'.... then put the SQL string into a QueryDef
' CurrentDb.QueryDefs("myQuery").SQL = strSQL

' or get a recordset
set rs = db.openrecordset(strSQL)

.... etc. ...

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQdmXzoechKqOuFEgEQIwewCglMvI0jz2XkZDnGbNY8EV+H FaxNAAoIUp
9H5O1Rz61UstPjJbspkFMynn
=+Rkj
-----END PGP SIGNATURE-----
jonhanks wrote:

I'm trying to search by form a field in a table called 'subject' via
two combo boxes on a form. In the query I have the following criteria
in the 'Subject' field:

(Like "*" & [Forms]![frmFind]![cmbOp] & "*") Or (Like "*" &
[Forms]![frmFind]![cmbSub] & "*")

This seems to work fine untill I leave either [cmbOp] blank, or
[cmbSub] blank - at which point it returns ALL records with no blank
Subject field. Any ideas?