Query criteria set by unbound form textboxes

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.
 
P

PieterLinden via AccessMonster.com

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.

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

ryguy7272

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.

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

--



.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top