Val function not giving expected results

[terrytek]

I have a query (qryTestingValComp) where, occasionally, a test score [SS] will have a plus sign, i.e. 208+ .
I need to get the difference between two test scores, so need the score converted to a number. I have used the Val function:

SSNum: Val(Nz([SS]))

However, there are currently two cases in the query where the function is returning the number followed by itself, i.e. [SS] of 175 returns SSNum 175175 .
Needless to say, this is messing up the calculations and (I think) causing an Overflow error in the queries and report that depend on qryTestingValComp.

Any idea why this is happening and how to remedy it?

I have attached a sample db with only the relevant tables and the query. Problem records are TestID 223 and TestID 310.

Thank you!

 

[JHB]

The problem isn't the Val function or the query, but the data in the table, it is double, see the picture.
If you use the Asc function you'll see it has a new line + a return ASCII code.
294+Chr(10)+Chr(13)+294.
I suspect you've done some copy and paste from another program/data source.

 

[terrytek]

The problem isn't the Val function or the query, but the data in the table, it is double, see the picture.
If you use the Asc function you'll see it has a new line + a return ASCII code.
294+Chr(10)+Chr(13)+294.
I suspect you've done some copy and paste from another program/data source.

Thank you so much for your reply; this was driving me nuts and I never would have figured this out on my own.
I designed the db, but this data was entered by the user. Could you give me more detail about "use the Asc function"? (I tried doing the Asc function on the SS field in a new query, but that didn't really show me anything). Your reply makes sense of the values the Val function returned, but I am unclear as to how you got the above picture. Just trying to learn.
What would be the best way to guard against this problem in the future? Using an input mask on the table field?

 

[JHB]

.. Could you give me more detail about "use the Asc function"? (I tried doing the Asc function on the SS field in a new query, but that didn't really show me anything).

I was using the Asc function in some VBA code I wrote. Database with the code is attached, open the form, click the button.

..
What would be the best way to guard against this problem in the future? Using an input mask on the table field?

Normally it shouldn't be possible to type that in, therefore I thought it was copied from somewhere else.