Re: Maketable query a text field is changing to expoential number?

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

*

Leslie wrote:
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.
.

Relevant Pages

  • Re: Duplicate Records From a Table
    ... >> I'm trying to find duplicates within a table where a field name called ... >> APP_KEY is made up of 12 digits. ... I want the query to ONLY find ... Prev by Date: ...
    (microsoft.public.access.queries)
  • Re: Duplicate Records From a Table
    ... You're right on the money with what I want. ... >>> I'm trying to find duplicates within a table where a field name called ... >>> I want the query to show me all FIVE records because they are all alike ... >>> within the first nine digits. ...
    (microsoft.public.access.queries)
  • Re: SQL Query - precision error
    ... We cannot change the precision as ... > If I run the query in Query Analyzer without the Front Page Search ... >> decimal number with a maximum of 10 digits in it. ... >> Sometimes the elephant eats you. ...
    (microsoft.public.frontpage.programming)
  • RE: Duplicate Records From a Table
    ... "R Layedra" wrote: ... > I'm trying to find duplicates within a table where a field name called ... > APP_KEY is made up of 12 digits. ... I want the query to ONLY find ...
    (microsoft.public.access.queries)
  • Duplicate Records From a Table
    ... I'm trying to find duplicates within a table where a field name called ... APP_KEY is made up of 12 digits. ... I want the query to show me all FIVE records because they are all alike ... Prev by Date: ...
    (microsoft.public.access.queries)