Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DSum expression with text criteria adding all records in table

Status
Not open for further replies.

bumfuzzled

Programmer
Joined
Jun 12, 2008
Messages
12
Location
US
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
Code:
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
 
Perhaps this ?
Code:
DSum("MileageCharge","Time","InvoiceID='" & [Invoice] & "'")+DSum("HoursCharge","Time","InvoiceID='" & [Invoice] & "'")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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
 
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?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top