DSUM in Update Query - Type Conversion Failure 1

[cariengon]

I am trying to use DSUM in an Update query. When I run the query, none of my records in my table I'm attempting to update are updated due to a "Type Conversion Failure". Not sure why this is happening because the field that I'm updating (fld_Total_Amt_Allowed)is a Double, and the field that I'm summing to put in the field is a Double (fld_Amount).

I'm not sure if I'm having problems because of the Inner Join or the Where clause. I need to have the Where because I only want to do this update where the Decision field = "Allow".

My SQL is below for the query. Maybe I'm just missing something or have something in the same order??

UPDATE 104_BCRT_Creditor_tbl INNER JOIN 201_Claims_Register_Decisions ON [104_BCRT_Creditor_tbl].BCRT_Creditor_Num=[201_Claims_Register_Decisions].BCRT_Creditor_Num SET 104_BCRT_Creditor_tbl.fld_Total_Amt_Allowed = DSum("[fld_Amount]","[201_Claims_Register_Decisions]","BCRT_Creditor_Num='" & [104_BCRT_Creditor_tbl].BCRT_Creditor_Num & "'")
WHERE ((([201_Claims_Register_Decisions].fld_Decision)="Allow"));

 

[scriverb]

I would suspect that the criteria expression(3rd parameter/argument) of the DSum is where the problem occurs. See red code. You have this setup as a string. Any chance it is a number

DSum("[fld_Amount]","[201_Claims_Register_Decisions]","BCRT_Creditor_Num=[red]'" & [104_BCRT_Creditor_tbl].BCRT_Creditor_Num & "'"[/red])

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[cariengon]

Yep! It's a numeric field. So I changed the formatting, but now it doesn't run. When I run it, it doesn't give me any of the warnings to update the fields like it did before...And nothing happens... Somehow I feel like the end of the DSum is repetitive of the ON clause. Would that cause this problem? If so, how do I fix it...

Here's the new code:
UPDATE 104_BCRT_Creditor_tbl INNER JOIN 201_Claims_Register_Decisions ON [104_BCRT_Creditor_tbl].BCRT_Creditor_Num = [201_Claims_Register_Decisions].BCRT_Creditor_Num SET [104_BCRT_Creditor_tbl].fld_Total_Amt_Allowed = DSum("[fld_Amount]","[201_Claims_Register_Decisions]","BCRT_Creditor_Num= [104_BCRT_Creditor_tbl].BCRT_Creditor_Num")
WHERE ((([201_Claims_Register_Decisions].fld_Decision)="Allow"));

 

[scriverb]

Let's see if you are getting the correct records to update based upon a Select that uses the same logic. If you don't get any records here then we know why no update takes place:

Select * FROM  104_BCRT_Creditor_tbl INNER JOIN 201_Claims_Register_Decisions ON [104_BCRT_Creditor_tbl].BCRT_Creditor_Num = [201_Claims_Register_Decisions].BCRT_Creditor_Num 
WHERE ((([201_Claims_Register_Decisions].fld_Decision)="Allow"));

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[cariengon]

Yes - This worked. I had 20 records returned - which is my expected outcome. Out of these 20, when grouped/summed on the fld_Amount and BCRT_Creditor_Num, there should be 15 records as there are 2 BCRT_Creditor_Num's that have more than one record.

 

[scriverb]

Try this. In the expression of the DSum the Numeric value has to be cancatenated outside the quotes otherwise it just compares to the name of the field.

UPDATE 104_BCRT_Creditor_tbl INNER JOIN 201_Claims_Register_Decisions ON [104_BCRT_Creditor_tbl].BCRT_Creditor_Num = [201_Claims_Register_Decisions].BCRT_Creditor_Num SET [104_BCRT_Creditor_tbl].fld_Total_Amt_Allowed = DSum("[fld_Amount]","[201_Claims_Register_Decisions]","BCRT_Creditor_Num = " & [104_BCRT_Creditor_tbl].BCRT_Creditor_Num )
WHERE ((([201_Claims_Register_Decisions].fld_Decision)="Allow"));

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[cariengon]

Here's my results. The 3rd column represents the fld_Amount where fld_Decision = "Allow". The total in the 2nd column includes the sum of where the fld_Decision = ALL (Allow, Object, & Null) as what was updated in the query that you provided to me.

BCRT_Creditor_Num fld_Total_Amt_Allowed SumOffld_Amount
100057 178,381.62 176,979.80
100069 8,046,214.14 6,609,013.14
100218 7,354,976.79 2,454,276.75
100353 6,440,669.95 1,224,856.25

What I should be getting is the total in column 3 in column 2. I tried some attempts at adding additional parameters to the DSum portion ie.

DSum("[fld_Amount]","[201_Claims_Register_Decisions]","BCRT_Creditor_Num = " & [104_BCRT_Creditor_tbl].BCRT_Creditor_Num AND "[201_Claims_Register_Decisions].fld_Decision) = "Allow")

But this updates the fld_Total_Amt_Allowed to the sum of ALL the records.

Is my thinking right on making this adjustment in the Dsum portion - if so, what am I doing wrong?

Thanks so much for all your help!

 

[cariengon]

Hooray! Played with the syntax ALL day and finally got the results I wanted!

DSum("[fld_Amount]","[201_Claims_Register_Decisions]","BCRT_Creditor_Num = " & [104_BCRT_Creditor_tbl].[BCRT_Creditor_Num] & " And [201_Claims_Register_Decisions].[fld_Decision]='Allow'")

Bob - Thanks for pointing me in the right direction!
Carie

 

[scriverb]

carie: Great!!!
Glad that you were able to work out your query problems. Thanks for the Star as it is much appreciated.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]