Update Query w/user input, Need to add a formula.

[Bombay]

I am running a update query that prompts you for user values and then updates
table "Shipping".

The value for "PO Number" will be prompted and then needs to be modified
before it is put into the table. They will be entering in a 5 digit PO
Number, and then it needs to be padded with Zeros and then truncated to 12
digits.

Example
user enters 30015
00000000000 is added to the left making it now 00000000030015.

Then it is to be truncated down to 000000030015

here is the SQL code I have so far.

UPDATE Shipping SET Shipping.[po number] = [Enter New PO],
Shipping.recipientName = [Business Name], Shipping.addressInformation =
[Address Line 1], Shipping.addressLine1 = [Address Line 2], Shipping.state =
[Enter State], Shipping.City = [Enter City], Shipping.postalCode = [Enter Zip
Code], Shipping.country = [Enter Country]
WHERE (((Shipping.ID)=1));


here is the formula to truncate it.

Right(("00000000000000" & "" & [po number]),12)

How do i enter this into the query, so after it is prompted this formula
will run?

 

[George Nicholson]

Try:
......SET Shipping.[po number] = Format(clng([Enter New PO]),"000000000000"),
.......

HTH,

 

[Bombay]

Thanks that worked!

Try:
......SET Shipping.[po number] = Format(clng([Enter New PO]),"000000000000"),
.......

HTH,

I am running a update query that prompts you for user values and then
updates
table "Shipping".

The value for "PO Number" will be prompted and then needs to be modified
before it is put into the table. They will be entering in a 5 digit PO
Number, and then it needs to be padded with Zeros and then truncated to 12
digits.

Example
user enters 30015
00000000000 is added to the left making it now 00000000030015.

Then it is to be truncated down to 000000030015

here is the SQL code I have so far.

UPDATE Shipping SET Shipping.[po number] = [Enter New PO],
Shipping.recipientName = [Business Name], Shipping.addressInformation =
[Address Line 1], Shipping.addressLine1 = [Address Line 2], Shipping.state
=
[Enter State], Shipping.City = [Enter City], Shipping.postalCode = [Enter
Zip
Code], Shipping.country = [Enter Country]
WHERE (((Shipping.ID)=1));


here is the formula to truncate it.

Right(("00000000000000" & "" & [po number]),12)

How do i enter this into the query, so after it is prompted this formula
will run?