Results 1 to 3 of 3
  1. #1
    FuSE is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    1

    Adding Fields in Query Error

    Hi All,

    I have just registered so hope to become a bit of a regular on here as it seems like a great resource!

    I am wondering if someone can help me out with the below.

    I have a query which uses fields from multiple other queries. These columns are all made up of queries
    Paid1 OS1 Paid2 OS2

    Example column names in the query:
    Key Paid1 OS1 Paid2 OS2 Total_Paid Total_OS Total_Total


    Formula in each total column is as follows

    Total_paid: [Paid1] + [Paid2]
    Total_OS: [OS1] + [OS2]

    Total_Total: [Total_Paid] + [Total_OS]


    Example of what each field should show in a row when query is ran



    Key Paid1 OS1 Paid2 OS2 Total_Paid Total_OS Total_Total
    1 100 900 1500 500 1600 1400 3000
    2 250 0 55 0 305 0 305


    Example of what each field is actually showing!!
    It seems to be concatenating the total_paid and total_os columns instead of adding them

    Key Paid1 OS1 Paid2 OS2 Total_Paid Total_OS Total_Total
    1 100 900 1500 500 1600 1400 16001400
    2 250 0 55 0 305 0 3050



    Any ideas why this would be happening?

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    It looks like the fields are set to text instead of being numeric or currency. You can force your last one to show the correct result by using Total_Total:ccur(Nz([Total_Paid],0) + ccur(Nz([Total_OS],0))

    Cheers,
    Vlad

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    As Gicu says, these are text fields - number fields are right aligned, yours are left aligned.
    Although Gicu's solution will work, I suggest you change the fields to a suitable number datatype
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 06-06-2016, 02:26 PM
  2. Replies: 6
    Last Post: 02-11-2014, 05:20 AM
  3. Replies: 1
    Last Post: 08-06-2013, 07:52 PM
  4. adding fields in query
    By slimjen in forum Queries
    Replies: 6
    Last Post: 09-24-2011, 09:06 PM
  5. Replies: 4
    Last Post: 09-21-2009, 01:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums