issue with runing Select query with condition using code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.)
 
Thanks for looking into this, Allen. I looked into the sample code and it
uses the docmd.openreport command using strwhere as filter. I know I can get
it to work if I use a report, but I'm trying to get it to work using
docmd.openquery command. Do you have an example of code that opens a query
instead of a report?
 
Build the whole query statement, not just the WHERE clause, and assign it
like this:

Dim strSql As String
strSql = "SELECT ...
CurrentDb.QueryDefs("Query1").SQL = strSql
 
Allen

Thank You. It worked! -
pchakra


Allen Browne said:
Build the whole query statement, not just the WHERE clause, and assign it
like this:

Dim strSql As String
strSql = "SELECT ...
CurrentDb.QueryDefs("Query1").SQL = strSql
Click to expand...
 
Back
Top