use an Access query as input to dLookup (1 Viewer)

[Michael.Koppelgaard]

It's possible to use a query from Access as input to dCount.

dim n as integer
n = DCount("*", "query1")

I'm wondering if you can do the same thing with dLookup?
I get an error when I try;

Dim res As Variant
Set res = DLookup("*", "query1")

 

[Gasman]

Why not just try it?, take a few seconds in the immediate window.
However, I would have thought you would have criteria, else would just pick up the first record?

 

[ebs17]

Set res = DLookup("FieldCount", "query2")

Only use set when assigning objects.
DLookup doesn't count, it fetches a value of a field from a table/query. Query2 should be e.g.:

SELECT COUNT(*) AS FieldCount FROM Query1

 

[Michael.Koppelgaard]

I'm not interested in the dCount, but thanks anyways, I'm only trying to get
res = DLookup("*", "Q_sending", "*") to run, but it seems it not possible. Also tried without the *

 

[Michael.Koppelgaard]

I'm not interested in the dCount, but thanks anyways I'm only trying to get

res = DLookup("*", "Q_sending", "*")

to run, but it seems it not possible. I also tried without the *

 

[jdraw]

This works for me

Sub testres()
Dim res As String
res = DLookup("aname", "QAnimalLatestSighting")
Debug.Print res
End Sub

But I get an error if I add the "*" for the criteria

 

[Pat Hartman]

It's possible to use a query from Access as input to dCount.

Access treats select queries as equivalent to linked/local tables for most purposes. So, any place you can use a table, you can almost always use a select query. That is why Access will not let you create a query and a table with the same name. You can create a form named Quote and a Macro named Quote and a report named Quote and a Table named Quote and Access has no problem telling them apart due to context. However, you cannot have a table named Quote AND a query named Quote since Access would have no way of knowing to which object you are referring.

res = DLookup("*", "Q_sending", "*")

That is NOT the correct syntax. dLookup() returns a SINGLE field from a SINGLE row. the first "*" must be the name of the field. The second "*" makes no sense in this context. It would be the Where clause if you add criteria in addition to what the query itself already contains.

Try dLookup("SomeFieldName", "Q_sending")
OR if you really do have additional criteria, then
dLookup("SomeFieldName", "Q_sending", "SomeDate = #" & SomeDateField & "#")

 

[ebs17]

It's possible to use a query from Access as input to dCount.

What do you really want? You don't really care about the actual syntax of DLookup that you can read about. Your test only reveals an unsatisfactory trial and error.

 

[The_Doc_Man]

I have used domain aggregates of queries many times. They are fine. The trick is to get the syntax of the arguments correct.

The field or value (1st argument) has to exist in the domain (2nd argument). The criterion must be like a WHERE clause without the word WHERE. Which means if you HAVE any criteria, they must be formed as though you had a full WHERE clause and then at the last moment plucked out the WHERE. There is also the little gotcha that the domain MUST be a named object because you are not allowed to put an actual SQL SELECT query in the 2nd argument. It has to be a table or a named query.

 

[ebs17]

There is also the little gotcha that the domain MUST be a named object because you are not allowed to put an actual SQL SELECT query in the 2nd argument. It has to be a table or a named query.

Replacement functions for domain aggregate functions are offered by various parties because these sometimes offer better runtimes. It should be noted that the consideration of runtime differences is irrelevant for a single execution, something like this will only occur with a very large number of repetitions - a case that you will usually avoid because it can be solved better.

I myself like to use such a universal replacement function because it allows me to use SQL statements in addition to the well-known domain aggregate functions. If you are able to read and write SQL statements, you can put cases like MIN, MAX, COUNT in the statement. You can also use more complex cases (JOIN, groupings) live, without having to create named objects first.

Public Function LookupSQL(ByVal SqlText As String, _
                          Optional ByVal Index As Variant = 0&, _
                          Optional ByVal ValueIfNull As Variant = Null) As Variant

    Dim rst As DAO.Recordset
    On Error GoTo HandleErr

    Set rst = CurrentDbC.OpenRecordset(SqlText, dbOpenForwardOnly)
    With rst
        If .EOF Then
            LookupSQL = ValueIfNull
        Else
            LookupSQL = Nz(.Fields(Index), ValueIfNull)
        End If
        .Close
    End With
    Set rst = Nothing

ExitHere:
    Exit Function
HandleErr:
    If Not (rst Is Nothing) Then
        rst.Close
        Set rst = Nothing
    End If
    Err.Raise Err.Number, "LookupSQL:" & Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
End Function

CurrentDbC ... is a persistent reference to CurrentDb.

If I look at the topic title, that could be what it means: Evaluate any SQL statement like DLookup does.