By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,676 Members | 1,139 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,676 IT Pros & Developers. It's quick & easy.

Field is Decimal and I want to Zero Fill

P: 31
I have a table that is named “field14" that is set to
Data Type: Currency
Format: Fixed
Decimal Places: 2

This field is populated correctly but I want to convert the currency to 14 digest with zero fill. Example: "2.20" would be "00000000000220" I have tried an update query with format but it didn't work. Any suggestions?
Wayne L
Dec 17 '07 #1
Share this Question
Share on Google+
5 Replies

Expert Mod 10K+
P: 12,365
You'll have to change your field to text. You can't store it as a number.
Dec 17 '07 #2

P: 31
You'll have to change your field to text. You can't store it as a number.
I have tried to change it to text but it converts back to a decimal. I am importing a .dbf file into the Access database. The field actually has 4 decimals to the right. example: 2.2050 this is converted to 2.20 when imported. I need to zero fill and read 220 as 00000000000220. I have created a temp table and append the master table to convert it to text but it changes back to the 4 decimal places??
Dec 17 '07 #3

Expert Mod 15k+
P: 31,487
Create a separate (text) field then use an UPDATE query to populate this new field. The Format() function can be used to lay the data out correctly.
Your example data was not very clear though. You say 20 digits but how do you want it to handle the decimals? Like a multiplication by 10,000? Simply strip the decimal point wherever it appears in the source?
Dec 17 '07 #4

P: 31
Thanks for the input. I am taking the approach of multiplying to remove the decimal and then zero fill with an update query.
Thanks again for you input.
Dec 18 '07 #5

Expert Mod 15k+
P: 31,487
No problems Wayne.
I take it from your post that you're sorted now yes?
Dec 18 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.