Dlookup query with multiple criterias (1 Viewer)

Y

yeasir01

Registered User.

Local time

Today, 00:24

Joined

Mar 26, 2018

Messages

67

• Apr 20, 2018

• #1

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:Copy to clipboard

Expr1: DLookUp([CLOSING_VOLUME],[DAILY_TASK_WETSTOCK],("TANK_ID=" & [TANK_ID]) AND ([DATES] = # & [DATES] & #-1))

 

bob fitz

AWF VIP

Local time

Today, 08:24

Joined

May 23, 2011

Messages

4,721

• Apr 20, 2018

• #2

Perhaps:

Code:Copy to clipboard

Expr1: DLookUp("[CLOSING_VOLUME]","[DAILY_TASK_WETSTOCK]","TANK_ID=" & [TANK_ID] & " AND ([DATES] = "# & [DATES]-1 & "#")

 

Y

yeasir01

Registered User.

Local time

Today, 00:24

Joined

Mar 26, 2018

Messages

67

• Apr 20, 2018

• #3

bob fitz said:

Perhaps:

Code:Copy to clipboard

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.

 

bob fitz

AWF VIP

Local time

Today, 08:24

Joined

May 23, 2011

Messages

4,721

• Apr 20, 2018

• #4

Can you copy and paste the expression that you have tried

 

Y

yeasir01

Registered User.

Local time

Today, 00:24

Joined

Mar 26, 2018

Messages

67

• Apr 20, 2018

• #5

Here you go

Code:Copy to clipboard

Expr1: DLookUp("[CLOSING_VOLUME]","[DAILY_TASK_WETSTOCK]","TANK_ID=" & [TANK_ID] & " AND ([DATES] = "# & [DATES]-1 & "#")

 

bob fitz

AWF VIP

Local time

Today, 08:24

Joined

May 23, 2011

Messages

4,721

• Apr 20, 2018

• #6

Sorry. Try:

Code:Copy to clipboard

Expr1: DLookUp("[CLOSING_VOLUME]","[DAILY_TASK_WETSTOCK]","TANK_ID=" & [TANK_ID] & " AND ([DATES] = #" & [DATES]-1 & "#")

 

Y

yeasir01

Registered User.

Local time

Today, 00:24

Joined

Mar 26, 2018

Messages

67

• Apr 20, 2018

• #7

bob fitz said:

Sorry. Try:

Code:Copy to clipboard

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.

 

Y

yeasir01

Registered User.

Local time

Today, 00:24

Joined

Mar 26, 2018

Messages

67

• Apr 20, 2018

• #8

here's what I get when I click on Error

 

Attachments

• 
  pic1.png

  86.8 KB · Views: 56

bob fitz

AWF VIP

Local time

Today, 08:24

Joined

May 23, 2011

Messages

4,721

• Apr 20, 2018

• #9

Can you post a copy of the db

 

bob fitz

AWF VIP

Local time

Today, 08:24

Joined

May 23, 2011

Messages

4,721

• Apr 20, 2018

• #10

Expr1: DLookUp("[CLOSING_VOLUME]","[DAILY_TASK_WETSTOCK]","TANK_ID=" & [TANK_ID] & " AND [DATES] = #" & [DATES]-1 & "#")

 

Y

yeasir01

Registered User.

Local time

Today, 00:24

Joined

Mar 26, 2018

Messages

67

• Apr 20, 2018

• #11

Here you go thanks for the Help!

 

Last edited: Apr 20, 2018

Y

yeasir01

Registered User.

Local time

Today, 00:24

Joined

Mar 26, 2018

Messages

67

• Apr 20, 2018

• #12

Querry is named "INV_DELIV_QRY" for reference.

 

isladogs

MVP / VIP

Local time

Today, 08:24

Joined

Jan 14, 2017

Messages

18,216

• Apr 20, 2018

• #13

Brackets are needed around Dates -1

This should work:

Code:Copy to clipboard

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

 

Y

yeasir01

Registered User.

Local time

Today, 00:24

Joined

Mar 26, 2018

Messages

67

• Apr 20, 2018

• #14

ridders said:

Brackets are needed around Dates -1

This should work:

Code:Copy to clipboard

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?

 

bob fitz

AWF VIP

Local time

Today, 08:24

Joined

May 23, 2011

Messages

4,721

• Apr 20, 2018

• #15

The DLookup is using the wrong domain.
I think it should be:

Code:Copy to clipboard

Expr1: DLookUp("[CLOSING_VOLUME]","[INV_DELIV_QRY]","TANK_ID=" & [TANK_ID] & " AND [DATES] = #" & [DATES]-1 & "#")

 

isladogs

MVP / VIP

Local time

Today, 08:24

Joined

Jan 14, 2017

Messages

18,216

• Apr 20, 2018

• #16

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

 

Y

yeasir01

Registered User.

Local time

Today, 00:24

Joined

Mar 26, 2018

Messages

67

• Apr 20, 2018

• #17

bob fitz said:

The DLookup is using the wrong domain.
I think it should be:

Code:Copy to clipboard

Expr1: DLookUp("[CLOSING_VOLUME]","[INV_DELIV_QRY]","TANK_ID=" & [TANK_ID] & " AND [DATES] = #" & [DATES]-1 & "#")

Click to expand...

You're Awesome, that worked!

 

Y

yeasir01

Registered User.

Local time

Today, 00:24

Joined

Mar 26, 2018

Messages

67

• Apr 20, 2018

• #18

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.