Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Dec 2009
    Location
    Spokane, WA, USA
    Posts
    56
    Thanks
    4
    Thanked 2 Times in 2 Posts

    Creating Expressions in Access (2010) Forms

    I am having problems creating expressions in Access 2010 forms.
    I either get a "type mismatch" error in Design View or on the form it displays "#Type!".

    What I want to do is to create, in the section footer (not the report footer) an expression as follows:

    __ items purchased from ________________ at a cost of __________________

    The first blank would be the sum of the quantity for that vendor.
    The second blank would be the vendor's name.
    The third blank would be the total cost for that vendor.

    Here's what I'd like the expression to look like on the form:

    6 items purchased from ABC Train Shop at a cost of $ 123.45

    The first blank is from my field "Qty"
    The second blank is from my field "Sold_by"
    The third blank is from my field "Calc_Total"

    The reason for putting all this in one expression is because the vendor's name varies in length and I'd like the total cost to be immediately after the vendor's name - not way across the page.

    I can format the individual control sources, but when I try to put them together in one text box, that's when I get the errors.


    Thanks

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    The Controlsource for a single textbox would be:

    =[Qty] & " items purchased from " & [Sold_By] & " at a cost of " & format([Calc_Total],"currency")
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Star Lounger
    Join Date
    Dec 2009
    Location
    Spokane, WA, USA
    Posts
    56
    Thanks
    4
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by MarkLiquorman View Post
    The Controlsource for a single textbox would be:

    =[Qty] & " items purchased from " & [Sold_By] & " at a cost of " & format([Calc_Total],"currency")
    Since the above control source gave me only the qty for the last item, I changed [Qty] to Sum([Qty]). Now it gives me the total quantity for the vendor.

    How do I format the currency portion? In other places I use the format of "$ #,##0.00" so it looks like $ 123.45. I'm presuming I would put that somewhere in the control source text box, but where?

    Thanks!
    Last edited by paulp575; 2016-11-27 at 13:10.

  4. #4
    Star Lounger
    Join Date
    Dec 2009
    Location
    Spokane, WA, USA
    Posts
    56
    Thanks
    4
    Thanked 2 Times in 2 Posts
    Figured out how to format the currency portion of the text box.

    I need to add the sum function somewhere in the Calc_Total portion as it is currently only displaying the Calc_Total of the last item.

    Currently the text box has this as the Control Source:

    =Sum([Qty]) & " items purchased from " & [Sold_By] & " at a total cost of " & Format([Calc_Total],"$ #,##0.00")

    I know it should go somewhere after the last "&", but have tried different places and it keeps giving me errors.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    What was wrong with: format([Calc_Total],"currency")
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Star Lounger
    Join Date
    Dec 2009
    Location
    Spokane, WA, USA
    Posts
    56
    Thanks
    4
    Thanked 2 Times in 2 Posts
    format([Calc_Total],"currency") only formatted the last item in the group. "Calc_Total" is the purchase price plus shipping & handling or sales tax for each item. I need to sum the total of all the records in a group.

  7. #7
    Star Lounger
    Join Date
    Dec 2009
    Location
    Spokane, WA, USA
    Posts
    56
    Thanks
    4
    Thanked 2 Times in 2 Posts
    Further update!

    The text box is as follows:

    =Sum([Qty]) & " items purchased from " & [Sold_By] & " at a total cost of " & Format([Purch_Total],"$ #,##0.00")

    Qty is correct; however, the Purch_Total is only including the Purch_Total from the first item - regardless of how many items are in the group.

    I need to Purch_Total to be the total Purch_Total of all the items in the group.

    I need to add the sum function to the Purch_Total part of the expression, but no matter where I place it, I get an error that says the expression is incorrect.

Tags for this Thread

Posting Permissions

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