Converting text to numbers and losing all negative values (1 Viewer)

Hi,

I've set up an access database and in one of the fields in my table the numbers are stored as text and negative numbers have a "-" character preceding them. When I change the field data type from text to numbers in the SQL, I lose the negative values from my output.

ie. in my code below, negative numbers are not being picked up by the select statement - only positive numbers are being captured.

-------------------------------------------------------
SELECT val(nz([db].[value])) as myamount,
from [db]
-------------------------------------------------------

Thanks in advance for any help you can offer.

Kim

I can't replicate the problem (i.e. val works for me for negative numbers) so wonder if it is something to do with your data? Or perhaps the code example you provided is not what you are actually using?

You could try specifying a value if null e.g. val(nz(value,0))

What looks like a negative sign would actually be a dash.

The strings in these expressions use the M-dash Chr(151) and N-dash Chr(150) and return zero when used with Val().
In the Immediate Window:

You can determine which it is by pasting the character between the double quotes.

Do a Replace with the negative sign character.

Thanks so much Galaxiom... that's awesome and will save me a lot of time.

Much appreciate the help.

Galaxiom said:

What looks like a negative sign would actually be a dash.

The strings in these expressions use the M-dash Chr(151) and N-dash Chr(150) and return zero when used with Val().
In the Immediate Window:

Code:Copy to clipboard

? Val("—")
? Val("–")

You can determine which it is by pasting the character between the double quotes.

Code:Copy to clipboard

? Asc(" ")

Do a Replace with the negative sign character.

Click to expand...