Convert Text field to Number Field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

davedinger - 05 Jun 2007 21:19 GMT

I have a field that is formated as 123-45-6789 in text format with a field
length of 50. I need to convert it to a number IE: 123456789. I cant get it
to convert because of the "-" is there a way to do this?

Reply to this Message

Douglas J. Steele - 05 Jun 2007 21:59 GMT

Val(Replace(NameOfField, "-", ""))

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>I have a field that is formated as 123-45-6789 in text format with a field
> length of 50. I need to convert it to a number IE: 123456789. I cant get
> it
> to convert because of the "-" is there a way to do this?

Reply to this Message

John W. Vinson - 05 Jun 2007 22:26 GMT

>I have a field that is formated as 123-45-6789 in text format with a field
>length of 50. I need to convert it to a number IE: 123456789. I cant get it
>to convert because of the "-" is there a way to do this?

If this is an identifier (it looks like a Social Security Number by the
format) then I'd say you DON'T want to convert it to a Number datatype. Will
you be doing arithmatic with it?

To keep it as a Text field but just remove the hyphens, run an Update query
updating it to

Replace([fieldname], "-", "")

Also remove any Input Mask on the table field or form control which might be
introducing the punctuation.

John W. Vinson [MVP]

Reply to this Message