Converting Data Types

[Wildmantribe]

I am new to Access 2007, but not new to programming. Yet, I still run across simple twists that seem to baffle me. So I need some advice.
I have a large exsisting database that contains alot of inventory items and along with the items particulars it lists each items price.
I have to run a query and pull out certain data elements on chosen items and dump it all into a text document so it can be cut and pasted into a new military database. So, I have encountered two problems in the doing...
First, I need to convert the Currency data type into a 7 digit Text data type and I am not sure how to accomplish this. From the tests I have run, Access rounds up the data. Also I am not sure how to convert it to text strings of any length, let alone in the 7 digit format I need.
Second, if I can convert the Currency into Text strings, then I need to dump 7 elements of each record into a text file, jamming all the data together, with no separators of any type, except a strategic space or two in certain positions within the long string of characters.
I am able to dump the data down to a text file easy enough, but how to get it to look the way I need without alot of editing on the text file after the dump? Is there a simple way to get Access to dump the data from the elements to Text and jam it all together into one long string and then insert a few strategic spaces into that string?
Whew! ....To anyone that can set me on the path to a simple, automated way to do these two things....I THANK YOU SO MUCH!

 

[HiTechCoach]

You can use the Format() function to convert a currency data type into a test string.

Examples:

? CCur("$12345.67")
 12345.67 
 
? Format(CCur("$12345.67"),"00000.00") 
12345.67

? Format(CCur("$45.67"),"00000.00") 
00045.67

? Format(CCur("$12345.67")* 100,"0000000") 
1234567

? Format(CCur("$45.67")* 100,"0000000") 
0004567

On exporting, have you tried fixed width?

 

[zgnd]

I am having the similar problem, i need to export a field in a table "amount" to be present as below

100050 ---> 000000000100050
320 ---> 000000000000320

Please help. Thank you very much

 

[DCrake]

zgnd:
Thi sis known as hijacking a post. Please stick to your own post and await a reply. As it happens I have given you a solution there.

David

 

[gemma-the-husky]

format(mynumber,"000000000000000")
format(mynumber,"############")
format(mynumber,"###########0")

in the first case numbers are masked into the resulting string, but zeroes are displayed
in the second case numbers are masked into the resulting string, but zeroes are NOT displayed - so zero shows as blank
in the third case numbers are masked into the resulting string, but zeroes are NOT displayed, except for the last digit, so zeroes are displayed as 0