Re: issue with runing Select query with condition using code

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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?
--
pchakra


"Allen Browne" wrote:

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


.

Relevant Pages

  • Re: Using VBA to filter a chart
    ... Say the query behind the chart is called qryChart and you have a second ... You could then append a WHERE statement to the string, ... Dim qdf_Chart as QueryDef ... Normally I would use a query to filter the data then ...
    (microsoft.public.access.modulesdaovba)
  • Re: Use wildcard in combo box with multiple strings
    ... Add the field to the query then. ... filter by it, it has to be in the query... ... use the field to filter what rows are in the RecordSource for the report. ... The string you use as a filter must repeat the name of the field for ...
    (microsoft.public.access.forms)
  • RE: Query form coding
    ... I have tried to include the query when using the wizard to design the report ... Dim strSource As String ... ' Remove Filter ...
    (microsoft.public.access.formscoding)
  • Re: Filtering records on a form using multiple combo boxes
    ... The goal is to make a valid query statement. ... You can also ask Access to print the string to the Immediate window. ... = page the text boxes are on ... filter the field in the record list box.. ...
    (microsoft.public.access.forms)
  • Re: Use wildcard in combo box with multiple strings
    ... I CAN'T add it to the query. ... My problem is I can not add the field to the query and I can not filter if ... My question is - How do I query for a report for multiple wildcards strings ... The string you use as a filter must repeat the name of the field for ...
    (microsoft.public.access.forms)