Dlookup function

[Guest]

Can someone please help, this is driving me nuts

I have 2 querys with the following fields

"Work Orders Query"
[Work Order ID], [Date], [MaterialSubTotal]

"Work Order Materials Query"
[Work Order ID], [SumOfCosts]

im trying to get [MaterialSubTotal] to Dlookup[SumOfCosts] where [Work Order
ID]=[Work Order ID] using

MaterialSubTotal: DLookUp("[Sum Of Cost]","Work Order Materials
Query","[Work Order ID]= [Work Order ID]") but i keep getting the same value
in all my records within my Work Orders Query,

Can anyone help??

 

[Guest]

Why you are using a dlookup, instead of creating a third query, that join the
two queries by Work Order ID, and display the require fields?

 

[John Spencer (MVP)]

Try

DLookUp("[Sum Of Cost]","[Work Order Materials Query]","[Work Order ID]=" &
[Work Order ID])

That assumes your work order id is a number field. If not, try
DLookUp("[Sum Of Cost]","[Work Order Materials Query]","[Work Order ID]='" &
[Work Order ID] & "'")

 

[Guest]

Many Many Thanks

Just for my reference and knowledge, your Dlookup function was slighty
different to mine with respect to the "& and not haveing ]") at the end of
the statement. Can you explain these differences to me.

Try

DLookUp("[Sum Of Cost]","[Work Order Materials Query]","[Work Order ID]=" &
[Work Order ID])

That assumes your work order id is a number field. If not, try
DLookUp("[Sum Of Cost]","[Work Order Materials Query]","[Work Order ID]='" &
[Work Order ID] & "'")

Can someone please help, this is driving me nuts

I have 2 querys with the following fields

"Work Orders Query"
[Work Order ID], [Date], [MaterialSubTotal]

"Work Order Materials Query"
[Work Order ID], [SumOfCosts]

im trying to get [MaterialSubTotal] to Dlookup[SumOfCosts] where [Work Order
ID]=[Work Order ID] using

MaterialSubTotal: DLookUp("[Sum Of Cost]","Work Order Materials
Query","[Work Order ID]= [Work Order ID]") but i keep getting the same value
in all my records within my Work Orders Query,

Can anyone help??

 

[John Spencer (MVP)]

Yes,

I am grabbing the value of work order id from the current record in the query.
Your statement was grabbing the work order id from the Work Order Materials
Query and so was basically grabbing the first record where work order id had a
value.

My "Where" clause would be something like
[Work Order ID] = 123 for the first record and then
[Work Order ID] = 282 for the next record, etc.

Your's
[Work Order ID] = [Work Order ID] for every record. Something like
123 = 123

Many Many Thanks

Just for my reference and knowledge, your Dlookup function was slighty
different to mine with respect to the "& and not haveing ]") at the end of
the statement. Can you explain these differences to me.

Try

DLookUp("[Sum Of Cost]","[Work Order Materials Query]","[Work Order ID]=" &
[Work Order ID])

That assumes your work order id is a number field. If not, try
DLookUp("[Sum Of Cost]","[Work Order Materials Query]","[Work Order ID]='" &
[Work Order ID] & "'")

Can someone please help, this is driving me nuts

I have 2 querys with the following fields

"Work Orders Query"
[Work Order ID], [Date], [MaterialSubTotal]

"Work Order Materials Query"
[Work Order ID], [SumOfCosts]

im trying to get [MaterialSubTotal] to Dlookup[SumOfCosts] where [Work Order
ID]=[Work Order ID] using

MaterialSubTotal: DLookUp("[Sum Of Cost]","Work Order Materials
Query","[Work Order ID]= [Work Order ID]") but i keep getting the same value
in all my records within my Work Orders Query,

Can anyone help??