Maketable query a text field is changing to expoential number?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I use Windows XP professional and Access 2003. I have imported Return Goods
from Excel and then brought the results into an Access database. I then have
a make table query to make tables needed to view the results in reports based
on product lines. The Item numbers come in as test fields because they are
not just numbers but some are a combination of numbers, hyphens, and letters.

They are coming in correctly from Excel but when I use the table to make
other tables so the data is more manageable the items, which have 8 digits
and start with 11, a few will change to expoential numbers. I've checked the
properties of the query and that is blank nothing has been added. Also for
some of the items it is changing it is duplicating, but if I run a find
duplicates query is shows nothing, implying there are no duplicates.

Any help will be greatly appreciated. This is only happening to a few
numbers but it is frustrating because I can't seem to track down the reason
why it is happening.

Thank you.
 
Make Table queries Bad
Append queries Good

Make table queries have several drawbacks. First, as you are experiencing,
they have to try to figure out a data type for a field. They also waste a
lot of space because they don't know how long a text field may need to be and
default to a length defined in Tools, Options, Tables/Queries. I think the
default is 50. And, although not pertinent in this situation, you loose any
relationships and indexes associated with the table.

A better approach is to create a table with the data types, field lengths,
and any other properties you want to set. Then use an append query to put
the data in the table. You only have to delete the existing data in the
table prior to running the append query. It is an easy one liner:

CurrentDb.Execute("DELETE * FROM MyTableName;")dbFailOnError
 
Hi Leslie,

Your numbers must be single or double precision numbers,
which are actually stored in exponential format

if the numbers are long integers or integers, you can use a
conversion function in your query

LongNumber: cLng(nz(expression))

IntegerNumber: cInt(nz(expression))

If you do want decimal places, you can use the currency data
type (even if the number is not money) -- this will give you
15 digits before the decimal and 4 digits after

CurrencyNumber: cCur(nz(expression))

It is not a good idea to use single or double precision
numbers to link fields or for exact comparisons, because of
the way they are stored


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
 
strive4peace said:
If you do want decimal places, you can use the currency data
type (even if the number is not money) -- this will give you
15 digits before the decimal and 4 digits after
Click to expand...

More appropriate might be the DECIMAL data type -- this will give you
up to 28 digits before the decimal and up 28 to digits after (scale) to
a combined (precision) maximum of 38 digits.

Also consider rounding rules e.g. CURRENCY uses banker's rounding
whereas DECIMAL uses truncation.

Another thing: portability. DECIMAL is in the SQL standards whereas
CURRENCY is a MS proprietary type limited to Jet/Access (i.e. not
directly portable to SQL Server).
It is not a good idea to use single or double precision
numbers to link fields or for exact comparisons
Click to expand...

Agreed.

Jamie.

--
 
Back
Top