DlookUp Help Please

lmg0115

Registered User.
Local time
Yesterday, 22:05
Joined
Jul 29, 2008
Messages
38
I have the following dlookup expression that is not working. I keep getting a missing argument error. Please help me.

HDR ActHrs: DLookUp("[Header End]","Header Information","[Date]=" & [Date]),DLookUp("[Header Start]","Header Information","[Date]=" & [Date]),[Header End]-[Header Start])
 
I have the following dlookup expression that is not working. I keep getting a missing argument error. Please help me.

HDR ActHrs: DLookUp("[Header End]","Header Information","[Date]=" & [Date]),DLookUp("[Header Start]","Header Information","[Date]=" & [Date]),[Header End]-[Header Start])

I still get confused about dlookup, but I think an easy way to explain the structure is as follows:
Code:
DLookUp({Source query or table for searching}
        {What you are searching for}
        {Any qualifictions or restrictions}

Based on this understanding, your code reformats as I have shown below. I am not sure, but I think you may have your parameters switched, if "Header Information" is the name of the table being searched. Also, I do not understand the reason for the "[Header End]-[Header Start]" at the end, although I am always willing to learn something new..

Code:
DLookUp("[Header End]", 
        "Header Information",
        "[Date]=" & [Date]), 
DLookUp("[Header Start]",
        "Header Information",
        "[Date]=" & [Date]),
[Header End]-[Header Start])







http://office.microsoft.com/en-us/access/HA012288251033.aspx
 
Msaccessrookie, thank you for your response, however the syntax for dlookup is:
Syntax
DLookup(expr, domain[, criteria])

Header Information is the name of the table and what I am trying to say here is HDRActHrs = Header End-Header Start and I want to lookup the header end and header start fields where the dates are equal to the date in my query.

In addition, I tried the code you posted above and I still get an error message. It highlights the (,) before the second dlookup. When I remove the comma I get an error stating "you many have entered an operand without an operator".
 
Sorry that I could not have been of more assistance. Perhaps someone else will respond and we can both learn.
 
Now that answer makes sense. This time I see only one item being selected instead of three. This may be the first time that I am glad that I was unable to help :rolleyes:
 
A couple of items jump out at me.

HDR ActHrs: DLookUp("[Header End]","Header Information","[Date]=" & [Date]),DLookUp("[Header Start]","Header Information","[Date]=" & [Date]),[Header End]-[Header Start])

First, you have [DATE] as your criterion, which uses a reserved word as a field name. This has been known to confuse Access tremendously in the past.

Second, expression "[Date]=" & [Date] is probably wrong as well. I think you need something like this...

"[Mydate]=""" & CStr$([MyDate]) & """"

or a Format$() call of some particular date format and [MyDate].
 
And third, you can't use two DLookups together as a field in a query, or as an expression in a control, without concatenating them. You can't just separate them with a comma as you are doing.
 
Thanks for the suggestions. I tried the following and it still does not work. Please show me where I am going wrong. I received a data type mismatch error. What I am trying to do is substract the first dlookup field from the second dlookup field.

HDR ActHrs: IIf([UnionqryforProdRpt].[MyField] In ("A","B"),DLookUp("[Header End]","Header Information","[Date]=""" & CStr$([Date]) & """ & - " & DLookUp("[Header Start]","Header Information","[Date]=""" & CStr$([Date]) & """")))
 
how can you have [Date]=[Date] as a function however its masked?

is the second date supposed to be today's date?

in which case its

[date] = date()

but to mask it properly the second date has to be surrounded by "#" chars

so its

"[date] = #" & date & "#"

now if your not in US this may still be a problem, so finally its

"[date] = #" & format(date,"long date") & "#"

------
although earllier mailman I think pointed out that regional settings can affect this, so its more correct to say

"[date] = #" & format(date,"DD/MM/YYYY") & "#"
 
I'm sorry if I'm not being clear but I guess that is part of my dilemma. My query is based off of another query and I want to lookup the header end and subtract it from the header start to get the actual hours worked. I want this to happen where the date in the first query equals the date in the Header Information table.
 
Thank you to everyone who helped me work on this. Here is what worked:

HDR ActHrs: IIf([UnionqryforProdRpt].[MyField] In ("A","B"),Nz(DLookUp("[Header End]","Header Information","[Header Information].[Date]= #" & [UnionqryforProdRpt]![Date] & "#")-Nz(DLookUp("[Header Start]","Header Information","[Header Information].[Date]= #" & [UnionqryforProdRpt]![Date] & "#"))))
 

Users who are viewing this thread

Back
Top Bottom