Convert number field to text field. 
Author Message
 Convert number field to text field.

Hi,
 I was wondering how do we change the number field to string field. The
field is initially a varchar data type in sql database.

This field change automatically to a number field when I insert them in
report. I have tried to used the format formula : ToText(x,places) , but it
still have the two zero at the back. eg. 123.00 to 12300 . The value of the
field is initially 000123  in the database.

Thanks for any reply.

Best regards,
ngcn



Mon, 29 Dec 2003 17:51:08 GMT  
 Convert number field to text field.
Try this... Totext(fld,"#")


Quote:
> Hi,
>  I was wondering how do we change the number field to string field. The
> field is initially a varchar data type in sql database.

> This field change automatically to a number field when I insert them in
> report. I have tried to used the format formula : ToText(x,places) , but
it
> still have the two zero at the back. eg. 123.00 to 12300 . The value of
the
> field is initially 000123  in the database.

> Thanks for any reply.

> Best regards,
> ngcn



Tue, 30 Dec 2003 01:32:30 GMT  
 Convert number field to text field.
Hi Arcadio,
I have tried that before, but the problem is how to get back the zeros in
front of the figure. For example, the data in database is 000456, when
insert this field into report it become 456.00

Thank you for your help.

regards,
Nee



Quote:
> Try this... Totext(fld,"#")



> > Hi,
> >  I was wondering how do we change the number field to string field. The
> > field is initially a varchar data type in sql database.

> > This field change automatically to a number field when I insert them in
> > report. I have tried to used the format formula : ToText(x,places) , but
> it
> > still have the two zero at the back. eg. 123.00 to 12300 . The value of
> the
> > field is initially 000123  in the database.

> > Thanks for any reply.

> > Best regards,
> > ngcn



Tue, 30 Dec 2003 10:03:59 GMT  
 Convert number field to text field.
  You will want to use an IF THEN and a CONCATENATE function.  If all of
your data is 3 digits, you can drop the If-THEN and just cat However a
simple example

If (Length(ToText(fld,"#"))=3 Then
    "000"+FLD
ELSE
If (Length(ToText(fld,"#"))=4 Then
     "00"+FLD
ELSE
YADA YADA YADA

Or you can use this opportunity to explore the SELECT CASE function

Replace the %%%% with field length eq 4,5,6

--
Dave Chamberlain
  (remove the SPAM from my address to send an email)


Quote:
> Hi Arcadio,
> I have tried that before, but the problem is how to get back the zeros in
> front of the figure. For example, the data in database is 000456, when
> insert this field into report it become 456.00

> Thank you for your help.

> regards,
> Nee



> > Try this... Totext(fld,"#")



> > > Hi,
> > >  I was wondering how do we change the number field to string field.
The
> > > field is initially a varchar data type in sql database.

> > > This field change automatically to a number field when I insert them
in
> > > report. I have tried to used the format formula : ToText(x,places) ,
but
> > it
> > > still have the two zero at the back. eg. 123.00 to 12300 . The value
of
> > the
> > > field is initially 000123  in the database.

> > > Thanks for any reply.

> > > Best regards,
> > > ngcn



Tue, 30 Dec 2003 10:44:11 GMT  
 Convert number field to text field.
What about ToText({table.field},"000000")


Quote:
> Hi Arcadio,
> I have tried that before, but the problem is how to get back the zeros in
> front of the figure. For example, the data in database is 000456, when
> insert this field into report it become 456.00

> Thank you for your help.

> regards,
> Nee



> > Try this... Totext(fld,"#")



> > > Hi,
> > >  I was wondering how do we change the number field to string field.
The
> > > field is initially a varchar data type in sql database.

> > > This field change automatically to a number field when I insert them
in
> > > report. I have tried to used the format formula : ToText(x,places) ,
but
> > it
> > > still have the two zero at the back. eg. 123.00 to 12300 . The value
of
> > the
> > > field is initially 000123  in the database.

> > > Thanks for any reply.

> > > Best regards,
> > > ngcn



Tue, 30 Dec 2003 17:54:19 GMT  
 Convert number field to text field.
Hi Bruce,
The problem is that the zeros in front of the figure is not fixed, it
depends on what the user key in & the figure is not neccessary to be zeros.
Its just that when I retrieve these figures, the zeros in front of the
figure just disappeared.

The funny things is some of the reports can retrieved the whole figure (
including the zeros). Do u understand my problem ?
Anyway, thanks for ur reply, it might be useful in future.

regards,
ngcn


Quote:
> What about ToText({table.field},"000000")



> > Hi Arcadio,
> > I have tried that before, but the problem is how to get back the zeros
in
> > front of the figure. For example, the data in database is 000456, when
> > insert this field into report it become 456.00

> > Thank you for your help.

> > regards,
> > Nee



> > > Try this... Totext(fld,"#")



> > > > Hi,
> > > >  I was wondering how do we change the number field to string field.
> The
> > > > field is initially a varchar data type in sql database.

> > > > This field change automatically to a number field when I insert them
> in
> > > > report. I have tried to used the format formula : ToText(x,places) ,
> but
> > > it
> > > > still have the two zero at the back. eg. 123.00 to 12300 . The value
> of
> > > the
> > > > field is initially 000123  in the database.

> > > > Thanks for any reply.

> > > > Best regards,
> > > > ngcn



Tue, 06 Jan 2004 11:06:07 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Please help!!Convert text field to date field(newbie)

2. convert text field to numeric field being used in Selection Formula

3. Concatenating a Text Field and a Number Field

4. Reading a field and using that field to place a number in another field

5. Converting Field numbering

6. Convert Number to Date Field

7. Data Type Errors when converting an empty field to a number

8. When you create a field each field has a id number (field number) (how can you edit this number)

9. Breaking up a phone number field into seperate fields

10. How to convert a Yes/No field to a numeric field

11. IN Code - convert a field type from Text to Hyperlink in Access 97

12. Converting Text Fields to Proper Case

 

 
Powered by phpBB® Forum Software