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 000000000000007 26
$24,974.57 becomes 000000000024974 57

and so on.

Can anyone help with what formula to put into the Update To: section of the query???

[ADezii]

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 000000000000007 26
$24,974.57 becomes 000000000024974 57

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_Amoun t] 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!!