• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 493
  • Last Modified:

Getting decimal when exporting to text file

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
PenningtonCounty
Asked:
PenningtonCounty
  • 10
  • 10
  • 5
  • +1
17 Solutions
 
Eric ShermanAccountant/DeveloperCommented:
Try adding Round([FieldName],0) to your expression.

ET
0
 
TextReportCommented:
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
 
PenningtonCountyAuthor Commented:
I forgot to note that it is exported as a fixed width field.  
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
TextReportCommented:
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
 
TextReportCommented:
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
 
PenningtonCountyAuthor Commented:
Neither the Round or the Format worked.  The decimal and zeros are still there.
0
 
TextReportCommented:
You must use the Format Function and not the Format Property, just tested this again in Access 2007 and works fine.
Cheers, Andrew
0
 
GRayLCommented:
Create a query which has the correct format for your field and export the query, not the table.
0
 
PenningtonCountyAuthor Commented:
error
0
 
TextReportCommented:
GRayL, the format property does not work when exporting to text file.
PenningtonCounty, "error" where and what error.
Cheers, Andrew
0
 
PenningtonCountyAuthor Commented:
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
 
TextReportCommented:
What version of Access?
Cheers, Andrew
0
 
PenningtonCountyAuthor Commented:
2003    
Found a workaround.  Did a make table query and export the table.
0
 
GRayLCommented:
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
 
PenningtonCountyAuthor Commented:
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
 
GRayLCommented:
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
 
TextReportCommented:
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
 
TextReportCommented:
You may find this article of use to you also. http://support.microsoft.com/kb/208408/en-us
Cheers, Andrew
0
 
PenningtonCountyAuthor Commented:
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
 
TextReportCommented:
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
 
PenningtonCountyAuthor Commented:
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
 
PenningtonCountyAuthor Commented:
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
 
TextReportCommented:
"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
 
GRayLCommented:
Come again?
0
 
GRayLCommented:
I mean, in http:#a23654103  -  Come again?
0
 
PenningtonCountyAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 10
  • 10
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now