DSum expression with text criteria adding all records in table

[bumfuzzled]

Hello all,

I have an invoice table and a "Time" table. In the time table there is a mileage charge field, and a hours charge field, I am trying to add those together and total for each invoice in the Invoice field and update a field in the Invoice table with that Information. I realize that this is a calculated field, but it is neccesary for something else that I am working on. So, here is what I have for my DSum expression in an update query

DSum("MileageCharge","Time","'InvoiceID='" & [Invoice])
+DSum("HoursCharge","Time","'InvoiceID='" & [Invoice])

Mileage Charge and Hours Charge are both currency fields, and invoiceID and Invoice are text fields

Thank you in advance, I have tried every which way,but it won't proccess the criteria it just adds the whole table

 

[PHV]

Perhaps this ?

DSum("MileageCharge","Time","InvoiceID='" & [Invoice] & "'")+DSum("HoursCharge","Time","InvoiceID='" & [Invoice] & "'")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bumfuzzled]

thank you Ph, I understood everything except the & "'" at the end. What is the meaning of this? I am not really clear as to the meaning of the quotes and double quotes except that they distinguish between text and number in SQL and I have not found a really good resource on this yet, can you recommend one?

AW

 

[lespaul]

because you are using text fields they need a delimiter. The text delimiter is the ' mark. in your first attempt the database was sent the command:
[tt]InvoiceID = '123[/tt]

instead of

[tt]InvoiceID = '123'[/tt]

by adding the "'" at the end, you get the whole string inside the quote.

HTH



Leslie

Have you met Hardy Heron?