Full Version: Formatting percentages and numbers
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
Hi all,
I'm new to this forum.
I have spent the last few hours banging my head against an Access brick wall in regards formating of database fields in a query. I need to create a query which I can then use to create a fixed length flat file to be used by another system.
First issue: there is a column which holds a percentage amount, it is defined as a percentage in the table design, I need to present the data in this field as follows:
50.9685 needs to be 050968500
100.453 needs to be 100453000
I have used the following command to try and get the amount before the decimal place, currently it returns nothing for 100% and 7. for when percentage is 0.075:
Mid([Interest%] * 100,1,InStr(1,[Interest%],'.')) AS mid100
When using the Mid function it seems that the percentage held in the database 0.509685 so therefore * 100 seems to work to some extent.
Please can you give me a helping hand. I hope this makes some sort of sense!
Use Int to get the part before the decimal place:
hole: Int([FieldName])
Then this to get the part after the decimal place:
Part: [FieldName] - Whole
This to add zeros to part before decimal:
WholeString: String("0", 3 - Len(CStr([Whole]))) & [Whole]
This to add zeros to part after decimal:
PartString: [Part] & String("0", 6 - Len([Part]))
This to join them together into value to export:
ExportString: WholeString & PartString
any thanks for such a quick reply, I have a few issues with your suggested code which I will look at tomorrow.
Graham, let me know if you have any questions. It is possible that I have misunderstood.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.