DLookup causing Type Conversion Error

[pduncan]

I am trying to do a DLookup but no matter what I do, keep getting Type Conversion Errors.

In Access, I am using an update query, and trying to use the looked up value as the "update to" field.

Here is what is in the "Update To" field of the Query:
DLookUp("[RegionalPrice]","tblRegionalPrice","[tblRegionalPrice]![RegionalPriceID] = """ & [tblInvoice]![PartID] & """")

Here is the SQL from the query:
UPDATE tblInvoice INNER JOIN tblRegionalPrice ON tblInvoice.PartID = tblRegionalPrice.RegionPriceID SET tblInvoice.RegionalSubtotal = DLookUp("[RegionalPrice]","tblRegionalPrice","[tblRegionalPrice]![RegionalPriceID] = """ & [tblInvoice]![PartID] & """");

I have ensured that RegionPrice and RegionalSubtotal are both currency values, and that the RegionPriceID and PartID are both Text values.

Any ideas what I am doing wrong here?

Thanks
Patrick

 

[PHV]

Why not simply something like this ?
UPDATE tblInvoice INNER JOIN tblRegionalPrice ON tblInvoice.PartID = tblRegionalPrice.RegionPriceID
SET tblInvoice.RegionalSubtotal = tblRegionalPrice.RegionalPrice;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pduncan]

I had to make a change to your code to match field names... here is what I used:
UPDATE tblInvoice INNER JOIN tblRegionalPrice ON tblInvoice.PartID = tblRegionalPrice.RegionPriceID SET tblInvoice.RegionalSubtotal = [tblRegionalPrice].[RegionPriceID];

but still get type conversion error -

Can you think of any causes?

 

[PHV]

I had to make a change to your code to match field names
Why ?


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

and what did you change? Looks like the exact query except for [].


Leslie

 

[PHV]

RegionalPrice changed to RegionPriceID in the SET clause.
I don't understand why.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pduncan]

The code as given caused an error – I changed it so it wouldn’t – but I guess what I changed didn’t work either. I’ll go back and look into it further, but I am really at a loss as to why I am getting type conversion failures – I have searched the site and cant find any reference threads to indicate what I am doing wrong… Can you provide some guidance or tips as to how best to avoid conversion failures? Conversion between what? I am using the sane types of fields….