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

[ally82]

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!

[alansidman]

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.

[ally82]

you're NOT psychic?!???

expr1:

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:

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!...

[meratigoerr]

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.

[ally82]

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.