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

B

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

George Nicholson

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

HTH,
 
B

Bombay

Thanks that worked!

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

HTH,


Bombay said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top