Re: Global variables are slowing down the query

In the query design grid, typically in the Criteria row, you can refer to a
text box on an open form like this:
[Forms].[Form1].[Text0]

This becomes part of the SQL statement like this:
WHERE Table1.Field1 = [Forms].[Form1].[Text0]

Ideally you want to declare this as a parameter to the query, to ensure the
data type is interpreted correctly.

If you are comfortable with writing SQL, you could create the SQL string
dynamically, concatenating the value from the form or variable directly into
the SQL string, e.g.:
Dim strSql As String
strSql = "SELECT Table1.* FROM Table1 WHERE Table1.Field1 = " &
MyVariable & ";"

Then you can assign this to the RecordSource of a form:
Me.RecordSource = strSql
or to a saved query:
dbEngine(0)(0).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.

"Jean" <jeanjanssens@xxxxxxxxxxx> wrote in message
news:1123590128.375309.31220@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> Yes, I guess I could use that method.
>
> Originally, I have the user select the string in a ComboBox, and the
> date(s) from the second column in a ListBox. I have to iterate through
> the date ListBox, as it is in Extended mode and a user can select
> multiple entries.
>
> But the point is: How do I reference the value of a form's control -
> directly in SQL? Or does one do it in the QBE grid? I have never seen
> this done so I would appreciate it if you could tell me.
>
> Thanks for your support so far!


.

Relevant Pages

  • Re: Problem with OpenRecordSet
    ... I don't know your Query but let's say the SQL String is something like: ... The JET database engine which handles the processing of data including ... When you run a Query through the Access interface, ...
    (microsoft.public.access.formscoding)
  • Re: duplicating control and incrementing another control
    ... > sql string, step 2 is to run it. ... Provided that the new record needs to be written with null values ... >>> out of the query will work fine. ...
    (microsoft.public.access.formscoding)
  • Re: Multi-Select ListBox
    ... I was under the impression that only the SQL code of the "matching CASE" ... West" in listbox 2, then I only would get East/West coast cities. ... Now -- with the DEBUG in front of the qdf.SQL, the query executes but I ... > Post the SQL String of the saved Query. ...
    (microsoft.public.access.forms)
  • Re: Fuzzy search using sql
    ... Are you building this SQL string in code, or are you trying to build it in a ... query and save it. ... build the SQL in code behind a command button, ... > It isnt working, its giving all the records as out put, text5 is the ...
    (microsoft.public.access.queries)
  • Re: How do I concatenate fields when Im writing code?
    ... Create your query using the query design grid. ... the way you want, select View SQL. ... > to filter a combo box selection on the basis of another combo box ...
    (microsoft.public.access.gettingstarted)