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))
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Neither the Round or the Format worked. The decimal and zeros are still there.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
error
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
2003
Found a workaround. Did a make table query and export the table.
Found a workaround. Did a make table query and export the table.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
GRayL, Yes thefld3 is the issue. The other fields come thru fine.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
TextReport, The Format$ didn't work either. Had tried the CStr() but maybe I formatted it wrong so will try it again.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?????
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?????
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER