Numbers/Currency import into Access as text

[kenmc]

I'm importing & linking both numbers & currency into Access via ODBC from
QAD's MFG/PRO system. The problem is that the numbers & currency import/link
into Access as text, thus not usable for mathmatical calculations.

I've been told that QAD's MFG/PRO has more positions to the right of the
decimal place than Access will allow, thus considering the information as
text since it won't "fit".

This is really a big problem and we're having a hard time finding a fix.

 

[Klatuu]

Is this a text file created by the other system?
You should be able to solve your problems with data types by creating an
Import Specification.
To do that, start an import manually File, Get External Data, Import
Select text file types.
Navigate to the file and select it.
The Import Text wizard will pop up.
Click the Advanced button in the lower left. Now you can create field
names, data types and skip fields you don't need. When you have the spec
defined, click Save As and give the spec a name.

Then use the TransferText method and use that name in the import and it will
apply the format you defined.

 

[kenmc]

The data is originally either numerical or currency in the QAD MFG/PRO ERP
system, which uses a Progress Server. I've been told that QAD has a decimal
point precision greater than MS Access. I can't confirm if that is true.

When I create a link table to the QAD source data through an ODBC connection
the data type is not a changable option.

For now, I'm bringing the data into a temporary table and converting it into
number. However, this work around is very cumbersome.

 

[Klatuu]

Wow, I didn't know Progress was still alive.
Well, I don't know about their precision by but Access has the Double data
type that is a double-precision floating-point number stored as a 64-bit
number ranging in value from approximately -1.797E308 to -4.940E-324
(negative), from 4.94E-324 to 1.797E308 (positive). I don't know how that
compares to your Progress data types, but if it is being carried as text,
when you convert it, it should either truncate or round. I don't know how
precise your data needs to be, so I don't know if that is a problem.

I also don't know how you are converting the data now, but I would suggest a
rountine to establish the link, Run an append and/or update query that would
do the type conversion during the import.