Query criteria set by unbound form textboxes

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

 

[PieterLinden via AccessMonster.com]

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.

 

[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''.

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.

--



.