Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Nov 2006
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dsum and VB (MS 2003)

    I created long Dsum statement that exceeds Macro allowed length and I am trying to use Docmd.runSQL strSQL, but due to multiple quotations marks in the statement it's very complicated to create SQL string, is there a shortcut? I am just trying to sum many fields within one table.
    here is my string that I couldn't compile:
    strSql = "Update totals set PLantTotalBudget = DSum("trBudgetTotoal"+"motBudgetTotoal"+"pmpBudget Totoal"+"fanBudgetTotoal"+"tnkBudgetTotoal"+
    "4brBudgetTotoal"+"480BudgetTotoal"+"scrBudgetToto al"+"boxBudgetTotoal"+"vvBudgetTotoal"+"vsBudgetTo toal"+"conBudgetTotoal","totals")"
    Thank you

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dsum and VB (MS 2003)

    You don't need all those quotes, you can use

    ... = DSum("trBudgetTotoal+motBudgetTotoal+pmpBudgetToto al+ ... +conBudgetTotoal", "totals")

    BTW, the table design is unfortunate - instead of 12 budget fields, it would be better to have an ID field, a field that identifies the budget, and a field to hold the value. You can then sum over the records instead of over the fields.

  3. #3
    Lounger
    Join Date
    Nov 2006
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dsum and VB (MS 2003)

    Hans, now I am running through next probelms:
    1. strSQL = "Update Totals set PlantTotalBudget = DSum("trBudgetTotoal+motBudgetTotoal+pmpBudgetToto al+ ... +conBudgetTotoal", "totals")" - I still can't compile it in VB code due to extra quotation marks, compiler stops at the first one.
    2. I made string shorter to fit into 255 characters for Macro and now I have next problems:
    2a. Update totals set PLantTotalB = DSum("motTotalB"+"PmpTotalB"+"fanTotalB"+"trTotalB "+"scrTotalB"+"BoxTotalB"+"conTotalB"+"tnkTotalB"+ "VVTotalB"+"VSTotalB"+"4brTotalB"+"480TotalB","tot als") - this gives me error: "Action Failed" with condition "true"
    2b. If I do your statement - DSum("trBudgetTotoal+motBudgetTotoal+pmpBudgetToto al+ ... +conBudgetTotoal", "totals - Macros doesn't like parameter without quotes (thinks as one field- I guess)
    3. In regards of Database:
    1. I have ID for each piece of equipment with associated budget in One table
    2. I keep total for each group in table Totals
    3. I keep Plant Total which sum group totals in the same Totals table
    appreciate your support,
    Alex

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dsum and VB (MS 2003)

    1) Sorry, should have seen that. Within a quoted string, you must either use single quotes, or double the double quotes"
    <code>
    strSQL = "UPDATE Totals SET PlantTotalBudget = DSum('trBudgetTotoal+motBudgetTotoal+pmpBudgetToto al+ ... +conBudgetTotoal', 'totals')"
    </code>
    2) Where are you using this? In the code behind a form? Or in a Data Access Page?
    3) I still think it would be better to have a separate record for each type of total.

  5. #5
    Lounger
    Join Date
    Nov 2006
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dsum and VB (MS 2003)

    Thank you Hans,
    That was the code behind Form
    Now I found that it works even this way: strSQL = "Update totals set PLantTotalB =motTotalB+PmpTotalB+fanTotalB" & _
    "+trTotalB+scrTotalB+BoxTotalB+conTotalB+tnkTotalB +VVTotalB+VsTotalB"
    DoCmd.RunSQL strSQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •