Hey,

So I'm kind of new to access and am having trouble getting my dlookup and dsum expressions to work properly in the database although i am typing it in in exactly the same way that the internet help menu tells me to.

my expression is as follows:

DLookUp("[Live]","[Contracted Producer Delivery]","[Tattoo]= " & [Queries]![Contract Query]![TattooNumber])

This is what happens when i try to change it from design to table view, 3 different things pop up

it asks for parameter value

Syntax error (missing operator) in query expression “[Tattoo]= “

The expression is typed incorrectly, or it is too complex to be evaluated…


I suspect the first one may be due to my criteria being defined by a query field that i defined myself and wrote an expression into so I think it may not be reading it. It was necessary to create this row in order to match it (it is basically an ID number type of thing) because in the tables that the query is based on it is listed out with letters in front of it as well.

if that is an issue then does anyone know how i am supposed to type in the criteria as well in order to base it on a query field on the same query that I added in with an expression?

Welcome to UtterAccess!

You should not use domain aggregate functions in queries (they shouldn't be used against full recordsets). You can still get the information you need by simply including the subquery ([Contract Query]) in this query and link them together using the appropriate field(s).

Now, to answer why the DLookUp() isn't working. It is probalby because [Tattoo] is a text field and you have not delimited the criteria with single or double quotes.

DLookUp("[Live]","[Contracted Producer Delivery]","[Tattoo]= '" & [Queries]![Contract Query]![TattooNumber] & "'")

thanks jerry,

Does a lookup function always have to be in number format? because the tattoo field has numbers as well as letters so that is why i entered it as text.

i am trying to create a database that does a lot of dsum and dlookups. we were using excel before but we need something that stores data better

Oh, nevermind my last comment, I found something online that answers it. the microsoft help menu isn't very helpful

DLookUp() will work with text, numeric, and date values. It is important that you format the query properly to insure correct results are returned as I described earlier. You should also avoid using domain aggregate function (DSum, DLookUp, Dcount, etc....), especially in queries or the detail section of reports. They are notoriously slow to execute and there are much better methods available.

What are some faster methods?

Use a subquery.

How does that work? For instance, how could the following (source for a textbox on a form) be rewritten to be faster?
=DLookUp("[EndDate]","[Class Info]","[Soc Sec] = Forms![Apprentice Information]![Soc Sec #] AND [Type] <> 'NC/Flunk' AND [Class Name] = 'Supplementary Math'")

It depends on the nature of the form. If it is a form that will be used to enter or edit records then a subquery is not a recommended solution (it will render the recordset non updatable). In this case, a Domain aggregate function is probably the best solution. PROVIDED the form is NOT continuous or datasheet.