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!