Dlookup query with multiple criterias

Y

yeasir01

Registered User.
Local time
Today, 07:44
Joined
Mar 26, 2018
Messages
67
Hello,

I need to display closing volume value where the date is one day prior & tanks are equal to the current record tank ID. Help is appreciated.

Heres what I have, but I can't seem to get it working.
Code: 
Expr1: DLookUp([CLOSING_VOLUME],[DAILY_TASK_WETSTOCK],("TANK_ID=" & [TANK_ID]) AND ([DATES] = # & [DATES] & #-1))
 
Perhaps:
Code: 
Expr1: DLookUp("[CLOSING_VOLUME]","[DAILY_TASK_WETSTOCK]","TANK_ID=" & [TANK_ID] & " AND ([DATES] = "# & [DATES]-1 & "#")
 
bob fitz said:
Perhaps:
Code: 
Expr1: DLookUp("[CLOSING_VOLUME]","[DAILY_TASK_WETSTOCK]","TANK_ID=" & [TANK_ID] & " AND ([DATES] = "# & [DATES]-1 & "#")
Click to expand...

It show invalid syntax with
# & [DATES]-1 & "#
Click to expand...
highlighted.
 
Can you copy and paste the expression that you have tried
 
Here you go
Code: 
Expr1: DLookUp("[CLOSING_VOLUME]","[DAILY_TASK_WETSTOCK]","TANK_ID=" & [TANK_ID] & " AND ([DATES] = "# & [DATES]-1 & "#")
 
Sorry. Try:
Code: 
Expr1: DLookUp("[CLOSING_VOLUME]","[DAILY_TASK_WETSTOCK]","TANK_ID=" & [TANK_ID] & " AND ([DATES] = #" & [DATES]-1 & "#")
 
bob fitz said:
Sorry. Try:
Code: 
Expr1: DLookUp("[CLOSING_VOLUME]","[DAILY_TASK_WETSTOCK]","TANK_ID=" & [TANK_ID] & " AND ([DATES] = #" & [DATES]-1 & "#")
Click to expand...
That did somthing but, im getting an "ERROR" in every record where the value should be.
 
here's what I get when I click on Error
 

Attachments

  • pic1.png
    pic1.png
    86.8 KB · Views: 88
Expr1: DLookUp("[CLOSING_VOLUME]","[DAILY_TASK_WETSTOCK]","TANK_ID=" & [TANK_ID] & " AND [DATES] = #" & [DATES]-1 & "#")
 
Here you go thanks for the Help!
 
Last edited:
Brackets are needed around Dates -1

This should work:
Code: 
Expr1: DLookUp("[CLOSING_VOLUME]","[DAILY_TASK_WETSTOCK]","TANK_ID=" & [TANK_ID] & " AND [DATES] = #" & [COLOR="Red"]([/COLOR][DATES]-1[COLOR="red"])[/COLOR] & "#")

EDIT: I sent this before I saw you had posted your db. It should work anyway
 
ridders said:
Brackets are needed around Dates -1

This should work:
Code: 
Expr1: DLookUp("[CLOSING_VOLUME]","[DAILY_TASK_WETSTOCK]","TANK_ID=" & [TANK_ID] & " AND [DATES] = #" & [COLOR="Red"]([/COLOR][DATES]-1[COLOR="red"])[/COLOR] & "#")

EDIT: I sent this before I saw you had posted your db. It should work anyway
Click to expand...

Thanks.

Now it doesn't show a record at all. Any other ideas?
 
The DLookup is using the wrong domain.
I think it should be:
Code: 
Expr1: DLookUp("[CLOSING_VOLUME]","[INV_DELIV_QRY]","TANK_ID=" & [TANK_ID] & " AND [DATES] = #" & [DATES]-1 & "#")
 
Just looking at your database now & found the same thing.

The issue is that you are attempting a DLookup on fields from two different tables which it isn't designed to do. It would have worked if all fields were in the same table

One way of solving this would be to create another query based on INV_DELIV_QRY (without the DLookup) and then add a DLookup in the new query
EDIT: Or just change the domain as in Bob's last answer which appeared whilst I was replying

OR perhaps better would be to use a subquery to get the previous value
Have a look at the 'Get the values in another record' example at this link: http://allenbrowne.com/subquery-01.html
 
bob fitz said:
The DLookup is using the wrong domain.
I think it should be:
Code: 
Expr1: DLookUp("[CLOSING_VOLUME]","[INV_DELIV_QRY]","TANK_ID=" & [TANK_ID] & " AND [DATES] = #" & [DATES]-1 & "#")
Click to expand...

You're Awesome, that worked!
 
ridders said:
Just looking at your database now & found the same thing.

The issue is that you are attempting a DLookup on fields from two different tables which it isn't designed to do. It would have worked if all fields were in the same table

One way of solving this would be to create another query based on INV_DELIV_QRY (without the DLookup) and then add a DLookup in the new query
EDIT: Or just change the domain as in Bob's last answer which appeared whilst I was replying

OR perhaps better would be to use a subquery to get the previous value
Have a look at the 'Get the values in another record' example at this link: http://allenbrowne.com/subquery-01.html
Click to expand...

This is useful information, I will defiantly read the article.
 
You must log in or register to reply here.

Similar threads

M
Dlookup frustration, multiple criteria
Replies
12
Views
556
Pat Hartman
P
H
Query of Sum of One field with different criteria
Replies
14
Views
853
hrdpgajjar
H
E
Solved DLookup value display with a minus sign
Replies
16
Views
784
Mark_
M
S
Need to create an alias for the totals of a field in query
Replies
5
Views
497
sheckay
S
H
Solved Query with multiple criteria
Replies
6
Views
961
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom