dlookup and sql

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

From: Johnny C. (JohnnyC_at_discussions.microsoft.com)
Date: 08/21/04 

Date: Sat, 21 Aug 2004 07:21:01 -0700

I'm looking for an easier, more elegant method...I have code in a form that
creates a temp query using sql, then using dlookup to get data, eg. using sql
to return a count of records that meet a specific criteria from a table, then
using dlookup to return the count. eg:

Dim qry As QueryDef
Set qry = New QueryDef
            
With qry
   .Name = "tempqry"
   .sql = "SELECT Count(LastName) as CountLastName, LastName " & _
   "FROM Staff " & _
   "GROUP BY LastName " & _
   "WHERE LastName = 'Smith';"
End With

then later

dim x as integer

x = DLOOKUP("[CountLastName]","[tempqry]","[LastName] = 'Smith'")

Is there a more elegant way to pass the result from a sql statement straight
into a variable?

Relevant Pages

  • Re: correct way to write the syntax for a Dlookup in a query expression in VB
    ... those that return false when the query runs to another table and leave ...    c) that you rewrite your sql select as a join checking for nulls ... DLookup; I am not). ... the duplicates may include differing information in particular ...
    (comp.databases.ms-access)
  • Re: correct way to write the syntax for a Dlookup in a query expression in VB
    ... nothing you have written indicates a need for a DLookup. ... And, just as a matter of interest, why wouldn't you use the Query Builder ... instead of writing SQL from scratch? ... the duplicates may include differing information in particular ...
    (comp.databases.ms-access)
  • Re: correct way to write the syntax for a Dlookup in a query expression in VB
    ... nothing you have written indicates a need for a DLookup. ... And, just as a matter of interest, why wouldn't you use the Query Builder ... instead of writing SQL from scratch? ... the duplicates may include differing information in particular ...
    (comp.databases.ms-access)
  • Re: Row source Best practice??
    ... Orginization is deffinately a good reason to keep SQL ... Jet saves optimization plans for recordsource and rowsource queries, ... recently screwed up a combo box on one form that used a saved query ... DLookup() for some logical purpose ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Building a Complex Crosstab
    ... I built the qry in SQL as you suggested ... I don't see how to convert these data in this query. ... I don't see how to use these conversions in this query SQL. ... Dim rs As DAO.Recordset ...
    (comp.databases.ms-access)