Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Dear Loungers

    I have two fields that hold a) a numeric value (currency) and b) a sign. I want to concatenate then so that I get the, for example: +10.00, +56.20, -20.00. This so that I can sum them.

    Before you wonder why it's like thsi it's because transaction will have a positive or negative value depending on the circumstance. I wanted a simple qury that did Sum(Value) but it need to know whther the value is positive or negative. How can I do this and is it possible using a query?

    thank you................ liz

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Are your fields defined as currency or as text? If the fields are currency you can do a simple sum, but if they are separate text fields (that raises other questions about how the table is structured, but skipping those for now), then you can create an expression which concatenates the two fields, and then use a conversion function to create a currency value. It would look something like this in the query:
    CurrValue: CCur([SignField] & [CurrField])

    Is this data being imported from another system where the fields are separate? If so you might want to consider making the fields into a single currency field and tweak the import process so you can deal with this in a more straightforward fashion.
    Wendell

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    You could have a calculated expression in a query:

    SignedNumber: iif([signfield]="+", [numberfield],-1*[numberfield])

    Assuming [numberfield] really is a number.
    Regards
    John



Posting Permissions

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