Converting text to numbers and losing all negative values. Please help.

[anasttin]

Hi,




I've set up a database and in one of the fields in my table the numbers are stored as text and negative numbers have a "-" character preceeding them. When I change the field
data type from text to numbers, 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

[June7]

I cannot replicate the issue. Conversion works fine.

Value is a reserved word. Should not use reserved words as names, however, that is not cause of your issue.

Why is data not stored as number?

[anasttin]

Thanks for the reply.

Using "Value" was just as an example and not in my real code.

Numbers are not stored as numbers because they come into Access via a linked table to Excel and the first 6 rows in Excel are text so Access interprets all the column contents as text even though all the real numeric data is below.

Can you suggest a work around to fix my problem, otherwise I'm in a real bind?

Thanks so much June7

Kim

I cannot replicate the issue. Conversion works fine.

Value is a reserved word. Should not use reserved words as names, however, that is not cause of your issue.

Why is data not stored as number?

[June7]

Hard to do since I cannot replicate the issue and never heard of it. If you want to provide db for analysis, follow instructions at bottom of my post.

Do you really have a comma before the FROM in your SQL statement? That should cause an error.

[anasttin]

Hi June7 and thanks again for taking an interest. Your support is super appreciated.

I've attached a zip file with an extract of the database and the Excel file the database is linking to. Both have been manipulated to remove confidential data as you mention.

As you can see, there are negative numbers in the excel file that the query just does not pick up. If you can assist to find the problem it will save me heaps of time.

Many thanks,

Kim

Hard to do since I cannot replicate the issue and never heard of it. If you want to provide db for analysis, follow instructions at bottom of my post.

Do you really have a common before the FROM in your SQL statement? That should cause an error.

[davegri]

Use 'format cells' to change desired columns to numbers in the spreadsheet.

[June7]

Here is something weird. When I have the spreadsheet open, the link and the query show negative sign. When the spreadsheet is closed they show () for negatives. The spreadsheet is formatted to show () for negative values.

I did a test and formatted one spreadsheet column as Number and to display - instead of () for negative. Now I get negative sign for that field in the link and query even when spreadsheet is closed.

[anasttin]

Hi June7 and thanks so much for your ongoing help. I've decided to just change the format of my source data and it appears to resolve the problem.

A very simple fix at the end of the day.

Truly appreciate your help.

Kim

Here is something weird. When I have the spreadsheet open, the link and the query show negative sign. When the spreadsheet is closed they show () for negatives. The spreadsheet is formatted to show () for negative values.

I did a test and formatted one spreadsheet column as Number and to display - instead of () for negative. Now I get negative sign for that field in the link and query even when spreadsheet is closed.