Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Getting decimal when exporting to text file

Posted on 2009-02-16
26
Medium Priority
?
490 Views
Last Modified: 2012-05-06
When exporting a query to a text file, fields created using expressions are getting a decimal and 2 zeros added.  Is there  something to add to the expression make sure the decimal and zeros are not added?  See expression below.
Expression:    IIf(Not IsNull(This),That,IIf(IsNull(This),That,0))

Open in new window

0
Comment
Question by:PenningtonCounty
  • 10
  • 10
  • 5
  • +1
26 Comments
 
LVL 19

Assisted Solution

by:Eric Sherman
Eric Sherman earned 80 total points
ID: 23652644
Try adding Round([FieldName],0) to your expression.

ET
0
 
LVL 28

Assisted Solution

by:TextReport
TextReport earned 800 total points
ID: 23652712
You will need to format the field. Format(MyField,"0")
This will mean the field is TEXT in your export you can specify the text delimiter of None to eliminate this but that may impact your import routine.
Cheers, Andrew
0
 

Author Comment

by:PenningtonCounty
ID: 23652742
I forgot to note that it is exported as a fixed width field.  
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 28

Assisted Solution

by:TextReport
TextReport earned 800 total points
ID: 23652758
In a Fixed Width you should be able to specify in the Export Specification that you want zero decimals else use the format function as stated above.
Cheers, Andrew
0
 
LVL 28

Assisted Solution

by:TextReport
TextReport earned 800 total points
ID: 23652779
Please ignore the first part of my last post "In a Fixed Width you should be able to specify in the Export Specification that you want zero decimals" can not be done, therefore, please use the Format option.
Cheers, Andrew
0
 

Author Comment

by:PenningtonCounty
ID: 23653062
Neither the Round or the Format worked.  The decimal and zeros are still there.
0
 
LVL 28

Assisted Solution

by:TextReport
TextReport earned 800 total points
ID: 23653100
You must use the Format Function and not the Format Property, just tested this again in Access 2007 and works fine.
Cheers, Andrew
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 400 total points
ID: 23653135
Create a query which has the correct format for your field and export the query, not the table.
0
 

Author Comment

by:PenningtonCounty
ID: 23653140
error
0
 
LVL 28

Assisted Solution

by:TextReport
TextReport earned 800 total points
ID: 23653153
GRayL, the format property does not work when exporting to text file.
PenningtonCounty, "error" where and what error.
Cheers, Andrew
0
 

Author Comment

by:PenningtonCounty
ID: 23653168
The query is being exported not the table.  The fields created by the writing the above expression are the issue.  The format function was used.  
0
 
LVL 28

Assisted Solution

by:TextReport
TextReport earned 800 total points
ID: 23653179
What version of Access?
Cheers, Andrew
0
 

Author Comment

by:PenningtonCounty
ID: 23653229
2003    
Found a workaround.  Did a make table query and export the table.
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 400 total points
ID: 23653455
Just so we're clear I suggested:

Select fld1, fld2, CStr(Trim(fld3) from myTable;

and save this as a named query.  Then export the query.  In my example, fld3 is the troublesome field and there are several ways of getting to the format you requested.  
0
 

Author Comment

by:PenningtonCounty
ID: 23654024
I need to clarify.  When using the Format() it does change it to text but when it is exported using fixed width, it gives a value which includes the decimal and zeros anyway.  So possibly the format function retains the decimal and zeros in the string???  

GRayL, Yes thefld3 is the issue.  The other fields come thru fine.
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 400 total points
ID: 23654103
When you made your new table how did you format the troublesome field in the new table.  You are saying the new table exported correctly?
0
 
LVL 28

Assisted Solution

by:TextReport
TextReport earned 800 total points
ID: 23654253
Can you try Format$(MyField,"0") the $ forces a string, not normally required but it may be in 2003 that it is converting it back to a number when exporting.
Cheers, Andrew
0
 
LVL 28

Assisted Solution

by:TextReport
TextReport earned 800 total points
ID: 23654515
You may find this article of use to you also. http://support.microsoft.com/kb/208408/en-us
Cheers, Andrew
0
 

Author Comment

by:PenningtonCounty
ID: 23654592
GRayL,  In a simple test it did.  Still trying to see if it works in the more complicated db query.  The simple test created a table which converted the data to text in the creation of the table without doing anything more in the Expression.  

TextReport,  The Format$ didn't work either.  Had tried the CStr() but maybe I formatted it wrong so will try it again.
0
 
LVL 28

Assisted Solution

by:TextReport
TextReport earned 800 total points
ID: 23654740
The use of the format / cstr functions have been te de facto standard for many a version of access, the link above is for Access 2000 and this link is for 95 / 97 http://support.microsoft.com/kb/153364/en-us.
Cheers, Andrew
0
 

Author Comment

by:PenningtonCounty
ID: 23654920
Are there setting on the computer that can affect this?  Read about regional settings affecting the number of zeros after the decimal point.

The export wizard does not allow us to determine the field type, or am I missing something?

Just got the make table query working in the db and even tho the fields were changed to text in the table, and no decimal or zeros showed in the table field, when exported to the text file, the decimal and zeros were there?????
0
 

Author Comment

by:PenningtonCounty
ID: 23655109
To me it appears the issue is in the export wizard.  The data types don't show.  I finally was able to drag the fields and the data types showed.  That worked.
0
 
LVL 28

Assisted Solution

by:TextReport
TextReport earned 800 total points
ID: 23655165
"The export wizard does not allow us to determine the field type, or am I missing something?" you are exporting Fixed Width which means there are no text delimiters that you would worry about with a CSV file. The fact is that the Format or CStr function should work, perhaps it is worth trying CSV rather than Fixed width.

Cheers, Andrew

PS What are you opening the text file with to validate what has been exported?
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 400 total points
ID: 23655245
Come again?
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 400 total points
ID: 23655253
I mean, in http:#a23654103  -  Come again?
0
 

Accepted Solution

by:
PenningtonCounty earned 0 total points
ID: 23660115
Yes, the Export Wizard does allow the choice of datatypes.  It is not obvious at first.   Initially in the Specs it just shows the field name and start and stop but if you drag the field name to the right I found another column for the datatype appears.  I had thought the problem was somehow in the Export Wizard because that was where the decimal and zeros seemed to be added.  This datatype column showed the fields we were having trouble with were designated as "double", changed them to "text", created the text file and we are no longer getting a decimal and zeros in those colums.  I finally remembered this dragging trick from a long time ago.  It has been years since I have worked in Access.  Thanks for all of your time and help.  You helped confirm that we were covering all bases with the IIF statement.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

564 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