Number format errors with negative numbers

  • Thread starter Thread starter jeq214
  • Start date Start date
J

jeq214

I've created a schema.ini file for fixed-length text files and I'm using a
query to view the data. It's giving me #Num! for all the negative numbers.
The text file displays negative numbers in parenthesis. I have the number
format to double in the schema file. Can someone help me figure out why it's
doing this?

Jon
 
Thank you for your response, but the option didn't work for me. I tried
putting that option in the header and after the column definitions. I read
that article too and was wondering if they omitted other information that
could've been useful. Here's a copy of the schema file:

[PTD_ACT.TXT]
ColNameHeader=False
Format=FixedLength
MaxScanRows=0
Col1=CO Text Width 14
Col2=ACCT Text Width 14
Col3=CC Text Width 16
Col4=PTD_BGT Double Width 12
Col5=PTD_ACT Double Width 12
Col6=PTD_VAR Double Width 8
Col7=YTD_BGT Double Width 13
Col8=YTD_ACT Double Width 13
Col9=YTD_VAR Double Width 8
Col10=LYTD_ACT Double Width 13
Col11=LYTD_VAR Double Width 8
NumberNegFormat=(1)

I'm just doing a simple query that selects all from the text file to view
the data. Also, is that option custom and can I create other options? Why
does it matter if the option if before or after the column definitions? Thank
you again for your time.

Jon
 
I wasn't trying to imply that NumberNegFormat was a legitimate parameter: it
was just a stab at it.

To be honest, I don't know whether it matters if the definition is at the
beginning or end.

What you may have to do is read it in as text, then write a function to read
the string and return the appropriate numerical value.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


jeq214 said:
Thank you for your response, but the option didn't work for me. I tried
putting that option in the header and after the column definitions. I read
that article too and was wondering if they omitted other information that
could've been useful. Here's a copy of the schema file:

[PTD_ACT.TXT]
ColNameHeader=False
Format=FixedLength
MaxScanRows=0
Col1=CO Text Width 14
Col2=ACCT Text Width 14
Col3=CC Text Width 16
Col4=PTD_BGT Double Width 12
Col5=PTD_ACT Double Width 12
Col6=PTD_VAR Double Width 8
Col7=YTD_BGT Double Width 13
Col8=YTD_ACT Double Width 13
Col9=YTD_VAR Double Width 8
Col10=LYTD_ACT Double Width 13
Col11=LYTD_VAR Double Width 8
NumberNegFormat=(1)

I'm just doing a simple query that selects all from the text file to view
the data. Also, is that option custom and can I create other options? Why
does it matter if the option if before or after the column definitions?
Thank
you again for your time.

Jon

Douglas J. Steele said:
Looking at http://msdn2.microsoft.com/en-ca/library/ms709353.aspx it
doesn't
look as though the ability to specify the format for negative numbers
exists, which surprises me!

You could try adding a line

NumberNegFormat=(1)

after the definitions of the individual fields and see whether that makes
a
difference.
Click to expand...
Click to expand...
 
That's exactly what I needed to do. Thank you so much!

Douglas J. Steele said:
I wasn't trying to imply that NumberNegFormat was a legitimate parameter: it
was just a stab at it.

To be honest, I don't know whether it matters if the definition is at the
beginning or end.

What you may have to do is read it in as text, then write a function to read
the string and return the appropriate numerical value.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


jeq214 said:
Thank you for your response, but the option didn't work for me. I tried
putting that option in the header and after the column definitions. I read
that article too and was wondering if they omitted other information that
could've been useful. Here's a copy of the schema file:

[PTD_ACT.TXT]
ColNameHeader=False
Format=FixedLength
MaxScanRows=0
Col1=CO Text Width 14
Col2=ACCT Text Width 14
Col3=CC Text Width 16
Col4=PTD_BGT Double Width 12
Col5=PTD_ACT Double Width 12
Col6=PTD_VAR Double Width 8
Col7=YTD_BGT Double Width 13
Col8=YTD_ACT Double Width 13
Col9=YTD_VAR Double Width 8
Col10=LYTD_ACT Double Width 13
Col11=LYTD_VAR Double Width 8
NumberNegFormat=(1)

I'm just doing a simple query that selects all from the text file to view
the data. Also, is that option custom and can I create other options? Why
does it matter if the option if before or after the column definitions?
Thank
you again for your time.

Jon

Douglas J. Steele said:
Looking at http://msdn2.microsoft.com/en-ca/library/ms709353.aspx it
doesn't
look as though the ability to specify the format for negative numbers
exists, which surprises me!

You could try adding a line

NumberNegFormat=(1)

after the definitions of the individual fields and see whether that makes
a
difference.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I've created a schema.ini file for fixed-length text files and I'm
using a
query to view the data. It's giving me #Num! for all the negative
numbers.
The text file displays negative numbers in parenthesis. I have the
number
format to double in the schema file. Can someone help me figure out why
it's
doing this?

Jon
Click to expand...
Click to expand...
Click to expand...
 
Back
Top