Re: issue with runing Select query with condition using code
- From: "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx>
- Date: Mon, 17 Sep 2007 23:48:42 +0800
So the text box could contain multiple values to match?
And possibly operators such as OR or AND as well?
You won't be able to use that text box directly in the query. Instead, you will need to parse it in your code, and build the WHERE clause as a string. You can then apply it as the Filter of a form, or the WhereCondition of OpenReport, or build the whole SQL string and apply it to the SQL property of a QueryDef.
Split() might be useful for parsing multiple elements in a text box into an array. You can then use the IN operator for the array elements. Be sure to use the correct delimiter around the values, i.e. " for strings, # for dates, and no delimiter for numeric values.
There's an example of how to loop through items and build the IN clause in this link:
http://allenbrowne.com/ser-50.html
(The article is explaining a multi-select list box, but the same logic applies to multiple values in a text box.)
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"PC" <PC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:377F49F0-708D-4653-973F-FF4F464C11DF@xxxxxxxxxxxxxxxx
Hi
I'm trying to use the DoCmd.open query statement to run a Select query. The
query has a function which captures input from a form. The query and the
code executed properly as long as the input is simple, but I run into
problems when the input is a composite one. The same thing works ok if I run
it through a report (using docmd.openreport command). Please help - I'm
attaching the and the relevant function below:
Function bu() As String
Dim strsql As String
If Forms!frmPreconsensus1!txtBU = "Core" Then
bu = "W"
ElseIf Forms!frmPreconsensus1!txtBU = "NCB" Then
bu = "K"
Else
bu = "'*'" 'This is where I'm runnning into a problem. I've tried
using like *, 'W' or 'K', etc.
End If
End Function
Can someone tell me wat I'm doing wrong or suggegst an alternate way of
accomplishing this? Thanks
.
- Follow-Ups:
- Prev by Date: RE: Dynamically referencing a recordset?
- Next by Date: Re: Round up or down in .5 increments
- Previous by thread: Re: registry vs Run time Options
- Next by thread: Re: issue with runing Select query with condition using code
- Index(es):
Relevant Pages
|
