DLookup Date Returning #Error

[feathers212]

I have a table (All_Dyer_Belt_Transactions) that I am querying. In this table is the [Install_Date] for each the dyer belt location installation. In the query, I am attempting to create a [Remove_Date] field for each dryer belt; this field is should be equal to the next install date at the same press-dryer-fan-zone location (looking up the next record at the location). It took me forever to at least get the formula below to be accepted in my query. But now, the only thing that is displayed is a "#Error" message in each record. Any thoughts on what is wrong?

Remove_Date:
DLookup("[All_Dryer_Belt_Transactions]![Install_Date]", "All_Dyer_Belt_Transactions",
"[All_Dryer_Belt_Transactions]![Press] = [Press] AND 
[All_Dryer_Belt_Transactions]![Dryer] = [Dryer] AND 
[All_Dryer_Belt_Transactions]![Fan] = [Fan] AND 
[All_Dryer_Belt_Transactions]![Zone] = [Zone] AND 
[All_Dryer_Belt_Transactions]![Install_Date] = #" & DMin("[All_Dryer_Belt_Transactions]![Date]","All_Dyer_Belt_Transactions",
"[All_Dryer_Belt_Transactions]![Install_Date] > #" & [Install_Date] & "#") & "#")

 

[CraigDolphin]

Hmm. Couple of things:

You do not need to qualify the table every time you reference a field in that table in a domain agregate function like Dlookup, DMin, etc. The second parameter in the function IS the table or query reference. Note: If you are using this expression in a query, and the fields you are using to supply values to the domain agregate function appear in more than one table in your query, you WILL need to supply the full table reference in the bits of the expression that are not encapsulated by quotes.

Secondly, remember that the third parameter is parsed as a string.
Thus "[Press]=[Press]"

is equivalent to saying:

...Where the field [Press] equals the value '[Press]'
Firstly, if the field contains numeric data, you will get an error by trying to compare a text string to a number.

Secondly, if the field contains text values, then this would only return results where the text value itself is "[Press]" However, you did not include the correct delimiter (quotes) around the text value so this will also cause an error.

In the same way that you need to calculate the data on the fly, you also need to calculate the required text string on the fly. This means that you also have to reference the value in the field outside the quotes, rather than use the name of the field as a value inside the quotes.

Thirdly, NEVER use 'Date' as a name for a field. It is a reserved word. For more examples of names NOT to use, see http://support.microsoft.com/kb/286335

Assuming that the fields Press, Dryer, Fan, and Zone contains text values, you could try:

Remove_Date:
Dlookup(“[Install_Date]", "All_Dyer_Belt_Transactions",
"[Press] = [COLOR="red"]‘[/COLOR]“ & [Press] & “[COLOR="red"]’[/COLOR] AND 
[Dryer] = [COLOR="red"]‘[/COLOR]“ & [Dryer] & “[COLOR="red"]’[/COLOR] AND 
[Fan] = [COLOR="red"]‘[/COLOR]“ & [Fan] & “[COLOR="red"]’[/COLOR] AND 
[Zone] = [COLOR="red"]‘[/COLOR]“ & [Zone] & “[COLOR="red"]’[/COLOR] AND 
[Install_Date] = #" & DMin("[[COLOR="Red"]Date[/COLOR]]","All_Dyer_Belt_Transactions",
"[Install_Date] > #" & [Install_Date] & "#") & "#")

If they contain numeric values, then you will need to remove the highlighted single-quotes.

Hopefully this helps.

 

[feathers212]

I have played around with the suggested changes (some of which I should have known better). I still cannot get this to work. Now I'm getting empty fields.

Could somebody take a look at this (Access 2003) and let me know your thoughts on how I can determine the Remove_Date?

Thanks a bunch!
~Heather

 

[CraigDolphin]

If no one sle gets to this, I'll download it tonight and look at it at home where I have AC2003 (I only have A2K here at work)

 

[CraigDolphin]

Ok...now I see the problem. Recall that your dlookup has a a bunch of conditions that it is looking to meet in the table "All_Dryer_Belt_Transactions".

You have it looking for fields in that table which are simply not there. You have joined to those fields in your query, but the dlookup is a separate function altogether. That's why you're not getting any values: the dlookup is trying the match fields that are not present in the table you're pointing it at.

The solution is fairly simple. First, get rid of the remove_date field from your current query and save.

Create another query which selects the records from your current query and uses the following calculated field for your remove date.

Remove_Date: DMin("[Install_Date]","All_Dryer_Belt_Transactions_Query","([Press] = '" & [Press] & "') AND ([Dryer] = '" & [Dryer] & "') AND ([Fan] = '" & [Fan] & "') AND ([Zone] = '" & [Zone] & "') AND ([Install_Date] > #" & [Install_Date] & "#)")

This looks for the minimum install date that is higher than the current install date, where everything else matches.

Note that the DMin function, like Dlookup, requires that all fields be present in the source recordset/table. In this case, I used your original query as the source, not the table itself.

 

[feathers212]

You have it looking for fields in that table which are simply not there.

Note to self: turn brain on at beginning of week.

Thanks Craig. Everything works perfectly now.

 

[CraigDolphin]

Note to self: turn brain on at beginning of week.

LOL

I know the feeling far too well...

Glad it worked for you