Query criteria set by unbound form textboxes

  • Thread starter Thread starter slickdock
  • Start date Start date
S

slickdock

Since I distribute a runtime mde to my users, I can't allow them to set query
parameters on their own. I have created an unbound form:
frmCriteria
with several text boxes:
txtCriteria1, txtCriteria2, txtCriteria3.

Users need to be able to select records with this type of selection criteria:
"If fieldFruit contains apple OR grape OR pear" then select the record.

I successfully accomplished this with the query fieldFruit criteria
containing this formula:

Like "*" & [Forms]![frmCriteria]![txtCriteria1] & "*" Or _
Like "*" & [Forms]![frmCriteria]![ txtCriteria2] & "*" Or _
Like "*" & [Forms]![frmCriteria]![ txtCriteria3] & "*"

The problem is that the selection criteria might need OR or AND. ie.:
"If fieldFruit contains apple OR grape AND pear" then select the record.

I thought I would add a dropdown between each txtCriteria box
containing a choice for OR or AND, calling it cboJoin1, cboJoin2, etc.
But I can't figure out how to insert the AND or OR selections
dictated by the cboJoin boxes into the formula.

Hope you can help. Thank you.
 
This is great for converting SQL to VBA:
http://allenbrowne.com/ser-71.html

Post back with specific questions.

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


PieterLinden via AccessMonster.com said:
slickdock said:
Since I distribute a runtime mde to my users, I can't allow them to set query
parameters on their own. I have created an unbound form:
frmCriteria
with several text boxes:
txtCriteria1, txtCriteria2, txtCriteria3.

Users need to be able to select records with this type of selection criteria:
"If fieldFruit contains apple OR grape OR pear" then select the record.

I successfully accomplished this with the query fieldFruit criteria
containing this formula:

Like "*" & [Forms]![frmCriteria]![txtCriteria1] & "*" Or _
Like "*" & [Forms]![frmCriteria]![ txtCriteria2] & "*" Or _
Like "*" & [Forms]![frmCriteria]![ txtCriteria3] & "*"

The problem is that the selection criteria might need OR or AND. ie.:
"If fieldFruit contains apple OR grape AND pear" then select the record.

I thought I would add a dropdown between each txtCriteria box
containing a choice for OR or AND, calling it cboJoin1, cboJoin2, etc.
But I can't figure out how to insert the AND or OR selections
dictated by the cboJoin boxes into the formula.

Hope you can help. Thank you.
Click to expand...

You would have to build the SQL in a string variable and then assign the
string to the SQL property of a querydef.

--
Message posted via AccessMonster.com


.
Click to expand...
 
Back
Top