Number format errors with negative numbers

[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

 

[Douglas J. Steele]

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.

 

[jeq214]

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]

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)

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

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.

 

[jeq214]

That's exactly what I needed to do. Thank you so much!

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)

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

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