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!

Thanks,

Graham.

Use Int to get the part before the decimal place:

Whole: 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

Frank,

Many thanks for such a quick reply, I have a few issues with your suggested code which I will look at tomorrow.

Thanks,

Graham.

Graham, let me know if you have any questions. It is possible that I have misunderstood.