Re: Need to get result of a select query in code

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Sun, 20 Jan 2008 20:07:54 -0500, "Robert" <cpq1bcle[REMOVE]@verizon.net>
wrote:

The problem is that it's a nested query with 2 where clauses. I was
thinking along the lines of using a string and concatenating in these 2
variables. But the currentdb.execute or docmd.runsql approach rejects
select queries. So I was wondering if there was any way a select string
could be executed in code without creating a recordset. Maybe not.

If you can save the query, you can use DLookUp to look up a value in the
query.

You do not need to, nor should you, "run" or "open" the query to do so. Just

xyz = DLookUp("[fieldname]", "[queryname]")

will do, if the query returns only one record.

A Recordset may well be better, especially if your query returns more than one
record or if you want to retrieve more than one field from the record.

John W. Vinson [MVP]
.

Relevant Pages

  • Re: As a generale rule - Query or VBA?
    ... But I end up with loads of queries with long names like: ... Stored queries have a precompiled query plan. ... For a code jockey the SQL string is tempting. ...
    (comp.databases.ms-access)
  • 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: Updating only changed records
    ... What exactly are the queries doing? ... a query that will update matching records, and add records that don't match. ... Dim stDocName1 As String ... Dim Msgstr2 As String ...
    (microsoft.public.access.formscoding)
  • Re: Make Multiple Tables via Query
    ... Execute method with a select query. ... Select queries contain a SELECT statement and can return ... Dim strExcelFileName As String ...
    (microsoft.public.access.queries)
  • Re: Data type mismatch in criteria expression. (Error 3464)
    ... I keep getting the Error 3464 when I run a particular query. ... This query is based on two other queries. ... Public Function FirstNumber(ByVal StringIn As String) As String ... Dim strChar As String ...
    (microsoft.public.access.formscoding)