is it possible to update this

[Guest]

i have a large table in which there is a numeric field "batch no" with input
mask "00/00000" ie if i enter this no "0546766" it stores it as 546766 and
displays it like this "05/46766". now i want to change this field to text
field in order to store the number as it was entered .is there a way in
update qry to update the data in this field after change the property to text
to add one zero on the left and get the number like this "05/46766" and store
it like that only.

 

[Guest]

Back your data first, then you can use an update query with format

UPDATE TableNameSET TableName.[batch no] = Format([batch no],"0000000")

Assuming that the length of the field is 7 chr

But if the number is not 7 chr and you just want to add another 0 on the
left, then
UPDATE TableNameSET TableName.[batch no] = "0" & [batch no]

 

[Douglas J. Steele]

While it's possible to do in one step, I'd do it as two separate passes,
just to be safe.

First, make sure that all of your fields have 7 characters in them using:

UPDATE MyTable
SET MyField = Right$("0000000" & [MyField], 7)

Once you know that they're all the right length, you can use:

UPDATE MyTable
SET MyField = Left$([MyField], 2) & "/" & Mid$([MyField], 3)

I'd also do this on a copy first, just to make sure everything goes
smoothly!

 

[Guest]

Add a space before the Set

UPDATE TableName SET TableName.[batch no] = Format([batch no],"0000000")

Or

UPDATE TableName SET TableName.[batch no] = "0" & [batch no]

--
\\// Live Long and Prosper \\//
BS"D

Back your data first, then you can use an update query with format

UPDATE TableNameSET TableName.[batch no] = Format([batch no],"0000000")

Assuming that the length of the field is 7 chr

But if the number is not 7 chr and you just want to add another 0 on the
left, then
UPDATE TableNameSET TableName.[batch no] = "0" & [batch no]
--
\\// Live Long and Prosper \\//
BS"D

i have a large table in which there is a numeric field "batch no" with input
mask "00/00000" ie if i enter this no "0546766" it stores it as 546766 and
displays it like this "05/46766". now i want to change this field to text
field in order to store the number as it was entered .is there a way in
update qry to update the data in this field after change the property to text
to add one zero on the left and get the number like this "05/46766" and store
it like that only.

 

[John Spencer]

My method. If your present field is Batch No, then I would attempt to change it
to BatchNoOLD.

Add a new field, Batch No and set its type to Text with a length of 8 (or more)

Then run an update query
UPDATE [Your Table Name]
Set [Batch No] = Format([BatchNoOld],"0000000")
WHERE [BatchNoOld] is not null

Then run a second update to add the "/"
UPDATE [Your Table Name]
Set [Batch No] = Left([Batch No],2) & "/" & Mid([Batch No],3)
WHERE [Batch No] is not null

You probably could do that all in one update with the following.
UPDATE [Your Table Name]
Set [Batch No] = Format([BatchNoOld],"00\/00000")
WHERE [BatchNoOld] is not null

I might try that first and check the results.

Also, you may find you need to drop any relationships you have set up on the
original batch no before you can change the field name. And you will have to
reset the batch no type and values in any other tables also if you need the
batch no for relationships between tables.

 

[Guest]

thanks to all of you for your response and help. i have tried each method but
succeeded with only method given by Mr Spencer. dont know the reason for the
method given by MR Ofer and Doug.
thanks again