Formatting numbers in Text Export

  • Thread starter Thread starter KWhamill
  • Start date Start date
K

KWhamill

This particular issue is giving me fits. I have a qry which is supposed to
prepare certain records for export to upload into a Database. There are two
apparent issues. The first is that the transaction number is five digits
However when it is exported ends up looking like 12345.00, the data uploader
does not like this. The other problem is that the transaction amount which is
in millions comes in Sci-notation ( or maybe just fixed) which the uploader
also does not like. I have attempted a couple of different ways to format the
numbers in the qry to no avail.
one pertinant restriction, the file must be *.csv or *.tab
Note: I can export this whole thing as a single *.xls then save the
individual tabs as *.csv and it works fine. I would however like to work
around this step.
 
K, are you using the export wizard to format your fields? If not, I think
that would help you get to your desired result. Use the File Export menu
itme, Save As Type 'text' and then the wizard will open. Note - if you save
these specifications, and then you make a change to your query fields in any
way, you will have to do the specs again. Hope this helps.
 
KWhamill said:
This particular issue is giving me fits. I have a qry which is supposed to
prepare certain records for export to upload into a Database. There are two
apparent issues. The first is that the transaction number is five digits
However when it is exported ends up looking like 12345.00, the data uploader
does not like this. The other problem is that the transaction amount which is
in millions comes in Sci-notation ( or maybe just fixed) which the uploader
also does not like. I have attempted a couple of different ways to format the
numbers in the qry to no avail.
one pertinant restriction, the file must be *.csv or *.tab
Note: I can export this whole thing as a single *.xls then save the
individual tabs as *.csv and it works fine. I would however like to work
around this step
Click to expand...

Actually i take that back this problem is a little more complicated than i
thought. there are five fields including the Transaction number and amount
that the datauploader doesn't like. The other three are text fields, two of
which are blank. But only when it comes out of Access. I'm looking at the
two, The one from access and the one from excel, text files side by side and
i can't see a difference. if any one has any ideas please let me know.
 
K,
you can usually work around these problems by using calculated fields in the
query to convert the number fields to text.
Once the numbers appear as text in the query, they will export OK.
Here is an example for the transaction number.
Create a new column in the query.
For the field row put
-->
TransNo: CStr([NameOfTransactionNoField])

Now switch the query to datasheet view to check that the number looks
correct.
When you do the export, export the calculated field instead of the field it
replaces.

Note: replace NameOfTransactionNoField with the real name of your field


Jeanette Cunningham -- Melbourne Victoria Australia
 
Jeanette Thank you,
unforetunetly this does not appear to be the problem. I tried the solution
you suggested, however, i am getting the same invalid type error on the same
set of fields. This from the data uploader not Access. out of 12 fields i'm
having trouble with 5 that are consecutive and at the beginning. I'll have to
puzzle this out myself i guess but thank you for help.
R,
Karl

Jeanette Cunningham said:
K,
you can usually work around these problems by using calculated fields in the
query to convert the number fields to text.
Once the numbers appear as text in the query, they will export OK.
Here is an example for the transaction number.
Create a new column in the query.
For the field row put
-->
TransNo: CStr([NameOfTransactionNoField])

Now switch the query to datasheet view to check that the number looks
correct.
When you do the export, export the calculated field instead of the field it
replaces.

Note: replace NameOfTransactionNoField with the real name of your field


Jeanette Cunningham -- Melbourne Victoria Australia


KWhamill said:
Actually i take that back this problem is a little more complicated than i
thought. there are five fields including the Transaction number and amount
that the datauploader doesn't like. The other three are text fields, two
of
which are blank. But only when it comes out of Access. I'm looking at the
two, The one from access and the one from excel, text files side by side
and
i can't see a difference. if any one has any ideas please let me know.
Click to expand...
Click to expand...
 
Back
Top