Dlookup Function with Multiple Criteria in Query

[wadheda]

Expr1: IIf(IsNull([Cost]);DLookUp("[Cost]";"Items Cost Query";" [Control_No] = & [Control_No] " And " [Transaction_Type] =' Addition ' ");[Cost])

I Created this syntax to fill value for item when [Transaction_Type] = Removal with value to the same item when [Transaction_Type] = Addition



But return the value for the first item in the query

[ranman256]

no semis, use commas.

[ranman256]

Instead of dlookup, you could just use a query and join it to the [Items Cost Query]. this does the lookup.
then you need 2 queries
1. (IsNull([Cost]) to join to LOOKUP table
2.NOT (IsNull([Cost]) ,no join.

[June7]

I think non-U.S. versions of Access use semi-colon.

Missing &. Missing ". Unnecessary " and spaces in the criteria string (spaces in ' Addition ' will cause match to fail). Table/query name has spaces, use [].

Expr1: IIf(IsNull([Cost]); DLookUp("[Cost]"; "[Items Cost Query]"; "[Control_No] =" & [Control_No] & " And [Transaction_Type] ='Addition'"); [Cost])

[wadheda]

Thanks for your Care
I don't understand how can i make 2 Query

[wadheda]

Thanks for your Care
But It's return null value

[June7]

The syntax I suggest is correct. It returns Null because no data matches the criteria. I don't know your data structure so don't know why it fails. Is Control_No a number type field?

[wadheda]

Item_Cost: IIf([Cost] Is Null;(DLookUp("[Cost]";"Items Cost Query";"[ID_Inv_Tr]=" & [ID_Inv_Tr]-1));[Cost])

this is another syntax but it's fill one filed by the correct value but the second filed is null

[June7]

Don't know your db so can't say why that happens.

[wadheda]

ID_Inv_Tr

Control_No

Code_No

Category

Transaction_Type

Transaction_Date

Cost

Quants

This's my Query to Calculate Inventory Cost ( Transaction_Type "Add & Remove" ) when adding items with cost but when removing without cost
the balances of items quantities ( add - remove )

[June7]

Those are fields but not a query statement. Last post makes no sense to me.

[wadheda]

ID_Inv_Tr

Control_No

Code_No

Category

Transaction_Type

Tran_Date

Cost

Quants

1

125

SP00125

Spare Parts

Addition

2014/11/25

9.95

18

2

125

SP00125

Spare Parts

Removal

2014/11/27

-3

3

125

SP00125

Spare Parts

Removal

2014/12/01

-2

4

125

SP00125

Spare Parts

Addition

2014/12/05

10.05

10

5

125

SP00125

Spare Parts

Removal

2014/12/08

-3

6

125

SP00125

Spare Parts

Removal

2014/12/11

-4

I'm sorry if i cant explain
this's my simple data
i want fill cost filed by Addition value to calculate balance of quantity and it's cost

[June7]

How would the "cost" of balance be determined?

Should enter raw data into table and do aggregate calcs (such as quantity balance) when needed in queries or reports. Aggregate calcs should not be saved into table.

[wadheda]

To calculate the cost i want fill the Blank fields as it's Arrange by the item cost which previously then ( Cost * Quants )in new column "Result" then sum the result With Note when transaction type = Remove the result is (-)

but regardless my question how can i fill the Blank fields as it's Arrange by the item cost which previously?

[June7]

If I understand, you want the 'Removal' records to pull value from another record of same table. That requires nested subquery or DLookup() domain aggregate function.

http://allenbrowne.com/subquery-01.html#AnotherRecord