By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,501 Members | 2,850 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,501 IT Pros & Developers. It's quick & easy.

Query By Form Like Statement

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
-----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?

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.