Quote:
Originally Posted by ckpoll2 
I'm trying to figure out how to get an update query to work. I need to get dollar amounts into standard lengths and formats for export into our main system. The dollar amounts need to be 17 digits with zero's to the left and no decimal. For example:
$7.26 becomes 00000000000000726
$24,974.57 becomes 00000000002497457
and so on.
Can anyone help with what formula to put into the Update To: section of the query???
With a Currency Field, you may be better off displaying your results in a Calculated Field. Assuming your Table name is tblProject, and your Currency Field is [Amount], to Format the results in a Calculated Field named [Converted_Amount] to 17 digits with leading zeros, no Decimals, and the 2 rightmost digits representing cents:
- SELECT tblProject.Amount, Format(100*[Amount],"00000000000000000") AS Converted_Amount
-
FROM tblProject
-
WHERE (((tblProject.Amount) Is Not Null));
Sample OUTPUT: -
Amount Converted_Amount
-
$7.26 00000000000000726
-
$24,974.57 00000000002497457
-
$85.00 00000000000008500
-
$1.03 00000000000000103
-
