Re: issue with runing Select query with condition using code

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 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:9C8BE12C-3597-4892-B002-176CF97FB739@xxxxxxxxxxxxxxxx
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.)

"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: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Help! Outer Join problem
    ... dateTime type. ... you could end up with a problem of string comparisons. ... query and then bring that into another query with the other two tables. ... Try the following as the FROM clause (and you should be able to drop the ...
    (microsoft.public.access.queries)
  • Re: strSQL using Like "*"
    ... so you need to craft the WHERE clause so that it does not compare to a field at all. ... Switch the query to SQL View, and edit the WHERE clause so it looks like this: ... A much more efficient solution is to create the filter string dynamically, in code, from the boxes where the user entered a value. ... to use a strSQL for the recordset and what I am querying on is based on 5 ...
    (microsoft.public.access.modulesdaovba)
  • Re: Using a custom function in a query
    ... if query is used for recordsource ... Dim strSQL As String ... Function ChangeSQL(pstrQueryName As String, pstrSQL As String) ... > FROM TableA ...
    (microsoft.public.access.queries)
  • Re: Need help with Code Please!!!
    ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)