Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2007
    Posts
    102

    Unanswered: Formatting issue

    Hello All,
    I have 2 subforms on a Main form....In subform 1 I get an 'theorderdetailtotal' (from unitprice X quantity calculation on subform 1 (called Order Details Subform))..
    Code:
    =Forms![Add an Order and Details]![Order Details Subform].Form!TheOrderDetailTotal
    The amount (from 'theorderdetailtotal')shows in the Main form in a control labeled 'Order SubTotal', no problem

    The problem comes in when I attempt to add the freight charge and sales tax to the 'Order Total', I can't seem to get it formatted properly to make it sum up together. Controls 'FreightCharge' and 'SaleTaxAlone' are subform 2.

    Note: SalesTaxAlone is calculated in subform 2 (Order Ship Details Subform)from another control 'anewsalestax'(rate)...it works well.

    I'm just having trouble stringing them all together into control labeled 'Order Total' '. I've put the parenthesis everywhere,,,but here's the latest rendition:

    Code:
    =Forms![Add an Order and Details]![AnOrderSubtotal]+(Forms![Add an Order and Details]![Order Ship Details Subform].Form!shipcost)+(Forms![Add an Order and Details]![Order Ship Details Subform].Form!SalesTaxAlone)
    I get #Error in the 'Order Total' control.
    thanks so much
    Last edited by imrosie; 09-10-07 at 08:38.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by imrosie
    I'm just having trouble stringing them all together into control labeled 'Order Total'
    Remember that the ampersand (&) is used in string concatenation. Plus is used to... add things!
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, the plus sign can be used for concatenation too

    guess which one handles nulls correctly

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    When working with numerics, the plus sign adds them together...
    1 + 1 = 2
    1 & 1 = 11

    "test" + Null = Null
    "test" & Null = "test"
    George
    Home | Blog

  5. #5
    Join Date
    Aug 2007
    Posts
    102

    you guys are great! can I ask 2 more???

    GeorgeV,

    thanks so much, I put that & in place of the plus and it worked beautifully.

    Now I'm struggling with my sales tax event. The sales tax control should display the amount of sales tax by utilizing the sales tax percent control, even when no sales tax is entered,it has a default of 6%. Both the sales tax percent and sales tax (ANewSalesTax) controls are on subform (#2). The 'AnOrderDetailSubTotal' is from subform #1.

    this won't calculate:
    Code:
    =Forms![Add an Order and Details]![Order Details Subform]!AnOrderSubtotal*Nz([ANewSalesTax])
    Lastly, the control (post #1) for the event where I changed the plus signs to ampersand's won't display in Currency format now...I've got 2 decimal places and Currency as the format; I've deleted is (suspect it may be corrupted) and added back the control...it is displaying for $65.00.....6500, with NO dollar sign or decimal places....any ideas??? I did keep the same name (because it's used in other calculations)....
    imrosie

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Then why are we concatenating? Why not simply have 1 currency format control per value?

    Also, doesn't the Nz function require more parameters?
    George
    Home | Blog

  7. #7
    Join Date
    Aug 2007
    Posts
    102

    I don't know....is the answer

    Hi GeorgeV,
    because (newbie) I thought it should work...Upon finding that it doesn't...I asked for help...I'm trying to understand.

    As for the nz...the last programmer here used it all over the place in the previous db..I looked it up and thought if one of my controls had a null, I would still get some value back...but I"m not expert on nz by any means.

    As for the "use one currency format per value" I"m going now to check that out...I thought that's what I was doing.
    thanks

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx

    Also, you missed out the keyword of "current format control per value".
    One textbox/label per value
    Last edited by gvee; 09-10-07 at 18:04.
    George
    Home | Blog

  9. #9
    Join Date
    Aug 2007
    Posts
    102

    thanks but that page cannot be found..

    GeorgeV

    I assume there's information that will help me solve this? the link doesn't work, even if I go to the microsoft site and paste it in..

    Also there is only 1 currency format control per value..
    thanks

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i fixed george's link
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh dear - that was a straight copy paste from the site!
    Try it again now.
    Code:
    Nz(variant, [valueifnull])
    EDIT: Thank you Rudy! You beat me to it
    George
    Home | Blog

  12. #12
    Join Date
    Aug 2007
    Posts
    102

    Thanks

    I appreciate..take care

Posting Permissions

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