Format string to numeric 1

[Cozmo2]

I have an input field [Zip] defined as a string for a length of 10. I need to move the first 5 characters to a numeric field [PermZip](length of 5). I am using a append query. Any help would be appreciated.

 

[lespaul]

now you realize that as soon as you move a zipcode like:

02105

into a numeric field you are going to now have a zipcode of

2105

and you are going to have to pad zeros.....

My rule of thumb, if you are not going to be performing any arithmetic on it, it's not a number.....SSN, ZipCode, Phone numbers...how often are you adding those up? never....make them text.....Storing any financial figures? those are numbers....always adding up money!

Leslie

Have you met Hardy Heron?

 

[PHV]

[PermZip]: Left([Zip], 5)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

A Zip code is not a NUMBER, despite the fact that all the digits are numeric. Rather, it is an IDNETIFIER.

You will never do arithmetic on a Zip.

Leading zeroes are meaningless for numbers, but are of great significance for identifiers.

Numbers are not fixed width, while many identifiers' widths are significant.

Identifiers ought, almost always, to be stored as a string or character types, regardless of the composition of the characters withing the value.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Cozmo2]

Leslie, I agree with you, however, my vender doesn't!

PH, I tried the Left([Zip],5)and received 00090 for a zipcode that was originally 61530.

 

[SkipVought]

Leslie, I agree with you, however, my vender doesn't!"

Have you broached this issue with your customer, stating the techincal is practical factors?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

received 00090 for a zipcode that was originally 61530
could you post the SQL code exhibiting this behaviour ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Cozmo2]

Here is the sql:

INSERT INTO CommonLine ( BorrowerLastName, BorrowerFirstName, PermBorrowerAddr1, PermBorrowerAddr2, PermBorrowerCity, PermBorrowerState, PermBorrowerZip )
SELECT LoanInfo.last_name, LoanInfo.first_name, LoanInfo.st_addr, LoanInfo.add_addr, LoanInfo.city, LoanInfo.state, Left([Zip],5) AS Expr3
FROM LoanInfo;

Thanks

 

[PHV]

Does the following show expected result ?
SELECT Zip, Left([Zip],5) As Zip5
FROM LoanInfo

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Cozmo2]

Yes that works. The problem is when it is appending the data to the table. I changed the fieldsize on [PermZip] to double and it works just fine!
Thanks you so much for your help.

 

[PHV]

Long should suffice ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886