Update query to certain number format

[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???

[ADezii]

@ckpoll2
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
 

[ckpoll2]

You are amazing!! Thanks so much for your help!!

[NeoPa]

Very much along the lines ADezii has already suggested :

SELECT [Amount],
       Format(100*[Amount],String(17,'0')) AS Converted_Amount
FROM   tblProject
WHERE  ([Amount] Is Not Null)

[ckpoll2]

You guys are great. Thanks again!!