Results 1 to 9 of 9
  1. #1
    Join Date
    May 2020
    Posts
    8

    Can't add numbers

    Hi,


    I'm sure there's a simple solution to this, but it's driving me nuts.
    I've got to fields I want to add togther in a query, both numbers.

    Lets say [number1] = 2 and [number2] = 3

    [number1] * [number2]

    I get 6, fine

    If I do this:

    [number1] + [number2]

    I get 23, not 5

    Can anyone help please?
    This just seems nuts.

    Thanks,
    A

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    My guess i that your variables are text/alpha and not numeric/integers/longs.
    Show us how you have dimmed number1 and number2.

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I agree with orange, sounds like you have text fields containing numeric data. Sort out your fields' data types or try:

    CInt([number1]) + CInt([number2])
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Post full SQL statement.

    Are fields number type?

    The + character is also a concatenation operator carried over from ancient BASIC into VBA.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Join Date
    May 2020
    Posts
    8
    Thanks for the replies, much appreciated.
    Pating the full SQL underneath, and the results as a screen grab.

    In this query:
    1st field is just an identifier
    2nd field is [number1]
    3rd field is [number2]
    Expr1 shows that if I use "+" it just concatenates the numbers as if they were strings
    Expr2 shows I can subtract them just fine (any other operator also works)
    Expr3 shows what happens when I use the CInt suggestion above

    I just cannot understand that if Access is happy to use any other operator perfectly well, then why not add them? Appreciate that + can be usedstead of &, but they're NUMBERS as I would think proved by the fact it can do anything else with them other than add them together.

    I've been through all my tables and queries with a fine toothcomb, and it's numbers all the way.

    Where else could I look to find the problem?
    Can't believe I'm looking at having wasted weeks of work beacsue Access can't add two numbers!

    Thanks folks.

    SELECT [Copy Of qryPROJECT].PROJECT.ProjectID, [Copy Of qryPROJECT].SummaryAgreedPOValue, [Copy Of qryPROJECT].SummaryForecastCost, [Copy Of qryPROJECT]![SummaryAgreedPOValue]+[Copy Of qryPROJECT]![SummaryForecastCost] AS Expr1, [Copy Of qryPROJECT]![SummaryAgreedPOValue]-[Copy Of qryPROJECT]![SummaryForecastCost] AS Expr2, CInt([Copy Of qryPROJECT]![SummaryAgreedPOValue])+CInt([Copy Of qryPROJECT]![SummaryForecastCost]) AS Expr3
    FROM [Copy Of qryPROJECT];

    Click image for larger version. 

Name:	Capture.JPG 
Views:	15 
Size:	133.1 KB 
ID:	42258

  6. #6
    Join Date
    May 2020
    Posts
    8
    Exp4 in this gives me what I want, but it's not very satifying.
    I subtract a negative of the number I want to add.

    SELECT [Copy Of qryPROJECT].PROJECT.ProjectID, [Copy Of qryPROJECT].SummaryAgreedPOValue, [Copy Of qryPROJECT].SummaryForecastCost, [Copy Of qryPROJECT]![SummaryAgreedPOValue]+[Copy Of qryPROJECT]![SummaryForecastCost] AS Expr1, [Copy Of qryPROJECT]![SummaryAgreedPOValue]-[Copy Of qryPROJECT]![SummaryForecastCost] AS Expr2, CInt([Copy Of qryPROJECT]![SummaryAgreedPOValue])+CInt([Copy Of qryPROJECT]![SummaryForecastCost]) AS Expr3, [Copy Of qryPROJECT]![SummaryAgreedPOValue]-(-[Copy Of qryPROJECT]![SummaryForecastCost]) AS Expr4
    FROM [Copy Of qryPROJECT];

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You've already been given the answer, you're just not accepting it.

    Note how the 2 fields are left-aligned in your picture. Text values align left, numbers and dates align right. You have numbers stored in the fields but you've told Access they're text with the data type. That means when you use + it assumes you want to concatenate. The other operators it will try to make an implicit conversion and perform the operation.

    You either need to change the data type of those fields in the table to a numeric type, or fix the underlying query which may be converting them to text (some functions will do that).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Here's a quick clue. In your displayed query columns, some of the columns are right justified and some left. The left justified columns are defined as TEXT.
    Try this in query design to coerce them to numeric:


    Click image for larger version. 

Name:	numeric.png 
Views:	12 
Size:	34.7 KB 
ID:	42259
    Last edited by davegri; 06-23-2020 at 04:20 PM. Reason: clarif

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    As Paul said, something is happening in intermediate query. Format() and FormatNumber() functions return a string, not a true number. Since data shows commas and left alignment, I suspect that is what is happening.

    Because both values are strings, this takes precedence with + operator and concatenation is result.

    Advise not to format data in queries. Or use conversion function to convert at least one value back to a number as you did with Expr3. But neither value can retain comma introduced by function. That will still force concatenation.

    Or your - (-) trick.

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 2
    Last Post: 08-30-2017, 07:11 AM
  2. Replies: 1
    Last Post: 03-17-2017, 06:08 PM
  3. Replies: 8
    Last Post: 03-10-2014, 11:47 AM
  4. same numbers = differnet numbers?
    By mike02 in forum Queries
    Replies: 1
    Last Post: 07-17-2013, 03:40 PM
  5. Replies: 1
    Last Post: 11-29-2011, 08:43 AM

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
  •  
Other Forums: Microsoft Office Forums