[Last Call] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2012-09-08
Medium Priority
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
  • 2
  • 2
LVL 29

Assisted Solution

IrogSinta earned 2000 total points
ID: 38380340
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

ID: 38380362
Thanks for your response.

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

Any other thoughts?

Thanks again.
LVL 29

Accepted Solution

IrogSinta earned 2000 total points
ID: 38380371
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

ID: 38380410
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.

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

831 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