Remove decimal place in column when exporting data to text file in MS Access

Posted on 2012-09-08
Last Modified: 2012-09-08
Hi All,

I have the following Query output from some tables:

A01         10          0
B01          9.98      10
C01          99.99     11

When exporting the Query in Access using the Saved Exports function (External Data > Saved Exports) or manually exporting the data to a text file, the resulting text file looks like this:

A01         10          0.00
B01          9.98      10.00
C01          99.99     11.00

I want the QTY to show integers, which is the output shown in the Access SQL Query Output. It looks fine within Access but not in the exported text file.

Any ideas how to ensure the QTY only shows integers eg 0, 10 etc...

Thanks in advance.
Question by:eeclint
    LVL 29

    Assisted Solution

    The data type of your QTY field is set to Number in the table but its field size probably set to Single, Double, or Decimal.  Change that to Byte, Integer, or Long.  Byte is fine if you don't expect your quantities to go above 255.  Integer is okay up to 32,767.  Long is much greater still.

    Author Comment

    Thanks for your response.

    Data Type: Number
    Field Size: Integer
    Decimal Places: 0

    Any other thoughts?

    Thanks again.
    LVL 29

    Accepted Solution

    I tried replicating your problem with a table that had the data you gave and exporting a query from that table but I was unable to get the same result.  If you could upload a stripped down version your database that exhibits this problem, then that could help.

    Author Closing Comment

    Thanks for your response.

    Your thoughts gave me an idea. I deleted the Specification & recreated a new one & voilà  everything worked.

    I appreciate the pointers.

    Thanks again for your help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now