Update Query fails at type conversion

[maduko]

I'm having trouble with an update query that says it cannot update the field because of a type conversion failure. The field in my table is set as currency as well as the source field from the query.

I'm using this SQL:

"[batch].[total]-Sum[qryBreakfasts].[amount]"

Any suggestions?

 

[PHV]

You may try to play with the CCur function:
CCur([batch].[total]-Sum([qryBreakfasts].[amount]))
or perhaps a Null issue ?
Nz([batch].[total],0)-Nz(Sum([qryBreakfasts].[amount]),0)

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

 

[maduko]

I just noticed something- no matter what I use it seems to want " quotes around the expression. If I don't include them it coughs up:

You tried to execute a query that does not include the specified expression 'amount' as part of an aggregate function

Uhhh... yeah.

 

[PHV]

Why not posting the SQL code ?

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

 

[maduko]

Here's the SQL....

UPDATE (qryBreakfasts INNER JOIN Results ON qryBreakfasts.ID = Results.ID) INNER JOIN batch ON Results.ID = batch.reg_id SET batch.amount = "[batch].[total]-Sum([qryBreakfasts].[amount])";

 

[PHV]

I quite don't understand the use of an aggregate function (Sum) in such query ...

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

 

[maduko]

That part works fine. It totals the amounts paid for breakfast tickets. The idea is to then subtract that from the total amount in the batch table.

 

[PHV]

You wanted this ?
UPDATE batch
SET amount = total - DSum("amount", "qryBreakfasts", "ID='" & reg_id & "'")

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

 

[maduko]

When I preview it looks great.

But when I run it I'm prompted for Total then it spits back Data Type Mismatch. After all of those roll p[ast is generates the same error as before.

 

[PHV]

Does the batch table have a field named total ?

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

 

[maduko]

No, the field in the batch table is Amount. Total is the field from the query.

 

[PHV]

Could you please post the schema of batch and qryBreakfasts, some input samples and expected result ?
I'm sorry, but I quite don't understood what you have and what you want.

Oh, an idea:
UPDATE batch
SET amount = amount - DSum("amount", "qryBreakfasts", "ID='" & reg_id & "'")

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

 

[maduko]

Thank you. It *almost* works now.

I was using reg_id to match the transactions in the batch table. That doesn't always work! I now have the OrderID field in the query and that is unique and present on each record.

Here's what I have:

qryBreakfast
OrderID ID amount
B7E8AA 2171 $30.00
B7E8AA 2172 $30.00
7CC9A3 2194 $30.00

Batch
reg_id amount OrderID
1955 $0.00 AAA99
2102 $1,250.00 ABCDE
2171 $175.00 B7E8AA
2188 $550.00 3Z11H
2194 $275.00 7CC9A3

And the intended outcome is to change the amount field in the Batch table:

Batch
reg_id amount OrderID
1955 $0.00 AAA99
2102 $1,250.00 ABCDE
2171 $115.00 B7E8AA
2188 $550.00 3Z11H
2194 $245.00 7CC9A3

 

[PHV]

UPDATE batch
SET amount = amount - Nz(DSum("amount", "qryBreakfasts", "OrderID='" & OrderID & "'"), 0)

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

 

[maduko]

BRAVO!

Thank you. Works like a champ!