#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    6
    Rep Power
    0

    I feel silly... why are Expressions "strings"? trying to use Sum...


    Ok, so I've got an odd problem and it seems like the silliest issue...

    I have a series of expressions in a query that are working very well, and basically spit out a number between 0 and 3 after running through the expression.

    So, I have three columns, each with a 0,1,2, or 3 per row.

    What I want to do is have a "simple" column after that which adds those numbers, which would range from a total sum from 0 to 9.

    However, when I use "Sum:[field1]+[field2]+[field3]" I get a series of strings, strung together, so instead of "5" I will get "122"...!!

    The only way I figured out how to "solve" this is creating a copy of those expression columns using the "conversion/val" function, which then properly sums the 1+2+2 into 5.

    ie, "expr: Val([Field1])"
    and then sum those copies.

    But, what am I doing wrong?? Is there a better way to do this? Isn't there a way to tell the expression that the field it's creating is a number and not a string?

    Thanks!
  2. #2
  3. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Location
    Lake County, IL
    Posts
    865
    Rep Power
    152
    Please post the current expressions that are giving you strings instead of numbers so that we can evaluate what is happening and offer a sound solution. Without that, we are guessing as to what you have. The mods forced us to return all crystal balls last week and we have to employ this technique now.
    Alan Sidman
    Office 2010, 2007, 2002

    If I helped you, then click "give rep" button in the lower left corner.
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    6
    Rep Power
    0
    you're NOT psychic?!???

    expr1:
    Code:
    FFI-A: IIf([Fracture Angle %R]<=15,"0",IIf([Fracture Angle %R] Between 16 And 30,"1",IIf([Fracture Angle %R] Between 31 And 46,"2",IIf([Fracture Angle %R]>=46,"3"))))
    expr2:
    Code:
    FFI-S: IIf([Fracture Surface]="SS","0",IIf([Fracture Surface]="SR","1",IIf([Fracture Surface]="RS","2",IIf([Fracture Surface]="RR","3"))))
    So you can see, 0,1,2,3 is an arbitrary label, but I want it to act like a real number, to be summed up in another column.

    I'm currently still working on a better expr3. The algorithm I used was incorrect before.... that's a whole other problem!...
  6. #4
  7. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    59
    Don't put the numbers in quotes. When access sees the quote it records the number as text, instead of a number. Without the quote it will see the number as a number and then you can do calcualations on the field.
  8. #5
  9. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    6
    Rep Power
    0
    see, I knew it would be something that easy.

    I wasn't sure about that because I thought without quotes it would confuse with the binary use of 0, etc., but I was certainly over thinking.

    Works great now, thanks.

Similar Threads

  1. Budget crises make mayors feel like "bad guys": poll (Reuters)
    By RSS_News_User in forum Business News
    Replies: 0
    Last Post: November 22nd, 2011, 12:00 AM
  2. Replies: 1
    Last Post: November 18th, 2009, 12:12 PM
  3. Bank of America CEO: Recession "feel" may last year (Reuters)
    By RSS_News_User in forum Business News
    Replies: 0
    Last Post: July 9th, 2008, 05:04 PM
  4. Bank of America CEO: Recession "feel" may last year (Reuters)
    By RSS_News_User in forum Business News
    Replies: 0
    Last Post: July 9th, 2008, 03:07 PM
  5. Dunkin' Donuts scarf ad flap "silly"-Islamic group (Reuters)
    By RSS_News_User in forum Technology News
    Replies: 0
    Last Post: May 29th, 2008, 06:03 PM

IMN logo majestic logo threadwatch logo seochat tools logo