Convert Text field to Number Field

[Guest]

I posted this earlier and recieved a message of a respose but I cant find the
Post, I've searched everywere, so I guess Ill try again.

I'm using Access 2003
Im trying to import records from two different tables
One has a field [ssn] as number format (123456789)
The other has field [Client ID] which is still the SSN Number, but it is
formatted as text (123-45-6789) I need to convert the text formated field
into the number format .

Thanks

 

[Tom Lake]

I posted this earlier and recieved a message of a respose but I cant find
the
Post, I've searched everywere, so I guess Ill try again.

I'm using Access 2003
Im trying to import records from two different tables
One has a field [ssn] as number format (123456789)
The other has field [Client ID] which is still the SSN Number, but it is
formatted as text (123-45-6789) I need to convert the text formated field
into the number format .

Thanks

Replace([Client ID], "-", "")


Tom Lake

 

[Guest]

this sounds like the answer, but Im not sure how to use it?
Where or how do i put Replace ([Client ID], "-", "")

I posted this earlier and recieved a message of a respose but I cant find
the
Post, I've searched everywere, so I guess Ill try again.

I'm using Access 2003
Im trying to import records from two different tables
One has a field [ssn] as number format (123456789)
The other has field [Client ID] which is still the SSN Number, but it is
formatted as text (123-45-6789) I need to convert the text formated field
into the number format .

Thanks

Replace([Client ID], "-", "")


Tom Lake

 

[Guest]

I think what Tom's suggestion will do is to replace the dashes with a
zero-length string (basically it just deletes the dashes). But I think what
you will be left with will still be a string - it just won't have any dashes
in it. (By the way, to answer your question, I think if you create a query
selecting the Client ID field then make the query be an Update query and
place the Replace([Client ID],"-","") in the Update to section, then run the
query it will remove all the dashes) Anyway, as I said, I think what you will
be left with will still be a string. I think (I hate to keep saying "I
thinkl" but I am kind of new to Access myself and I could be wrong) you could
just open the table containing the Client ID field in design mode and change
that field's type from Text to Number. If that won't work for you I bet there
is a function to convert String to Number but I haven't run across it yet.
You might try searching for "coercion" as changing from one data type to
another is referred to as coercing it. I hope this helps.

this sounds like the answer, but Im not sure how to use it?
Where or how do i put Replace ([Client ID], "-", "")

I posted this earlier and recieved a message of a respose but I cant find
the
Post, I've searched everywere, so I guess Ill try again.

I'm using Access 2003
Im trying to import records from two different tables
One has a field [ssn] as number format (123456789)
The other has field [Client ID] which is still the SSN Number, but it is
formatted as text (123-45-6789) I need to convert the text formated field
into the number format .

Thanks

Replace([Client ID], "-", "")


Tom Lake