Special Formatting Question

  • Thread starter Thread starter W L
  • Start date Start date
W

W L

Office XP
Windows XP

I asked this question several days ago, but since I know practically zilch
about Access, I did not understand the response (thanks again to Mr. John
Vinson). I'm very adept in Excel, but for my database usage requirements,
Access will work better. Here is my question.

I have a table imported. One of the columns (column heading Idnum) contains
number data ranging from 7 to 9 digits. Since these numbers are TAX id or
Social Security #'s, I have a couple of special formatting requirements. If
I just use a format of 000-00-0000 for this number string, it gives me
leading zeros for all numbers less than 9 digits and correctly formats the
social security numbers. However, this format does not take into account the
TAX ID #'s which should be formatted thusly 00-0000000. Luckily, all tax ID
numbers are greater than 699999999. In Excel, I used an If function. Here is
my Excel formula:

"=IF(RC[-2]<10000000,""00""&LEFT(RC[-2],1)&""-""&LEFT(RC[-1],2)&""-""&RIGHT(
RC[-1],4),IF(RC[-2]<100000000,""0""&LEFT(RC[-2],2)&""-""&LEFT(RC[-1],2)&""-"
"&RIGHT(RC[-1],4),IF(RC[-2]>699999999,LEFT(RC[-2],2)&""-""&RIGHT(RC[-2],7),L
EFT(RC[-2],3)&""-""&LEFT(RC[-1],2)&""-""&RIGHT(RC[-1],4)))"

To translate, this basically says if the number in the cell is less than
10000000 (or 7 digits long), then add two leading zeros, and insert a hyphen
between the 3rd and 4th digit and insert a hyphen between the 5th and 6th
digit. If the number in the cell is less than 100000000 (or 8 digits long)
then add one leading zero, and insert a hyphen between the 3rd and 4th digit
and insert a hyphen between the 5th and 6th digit. If the cell is greater
than 699999999, then insert a hyphen between the 2nd and 3rd digit. If none
of the conditions apply (else), then insert a hyphen between the 3rd and 4th
digit and insert a hyphen between the 5th and 6th digit.

I know how to get to design view on my table but I cannot say whether the
function I need to perform on this column can be done from there. I also
know how to create queries in Access, but i do not know if the function I
need to perform on this column can be done from there either. I would
appreciate if someone could give me detailed instructions on how to perform
this function or direct me to a resource where i can learn on my own how to
perform this function.

Thank you in advance for any assistance you can provide.
 
Office XP
Windows XP

I asked this question several days ago, but since I know
practically zilch about Access, I did not understand the
response (thanks again to Mr. John Vinson). I'm very adept in
Excel, but for my database usage requirements, Access will work
better. Here is my question.

I have a table imported. One of the columns (column heading
Idnum) contains number data ranging from 7 to 9 digits. Since
these numbers are TAX id or Social Security #'s, I have a couple
of special formatting requirements. If I just use a format of
000-00-0000 for this number string, it gives me leading zeros
for all numbers less than 9 digits and correctly formats the
social security numbers. However, this format does not take into
account the TAX ID #'s which should be formatted thusly
00-0000000. Luckily, all tax ID numbers are greater than
699999999. In Excel, I used an If function. Here is my Excel
formula:

"=IF(RC[-2]<10000000,""00""&LEFT(RC[-2],1)&""-""&LEFT(RC[-1],2)&"
"-""&RIGHT(
RC[-1],4),IF(RC[-2]<100000000,""0""&LEFT(RC[-2],2)&""-""&LEFT(RC[
-1],2)&""-"
"&RIGHT(RC[-1],4),IF(RC[-2]>699999999,LEFT(RC[-2],2)&""-""&RIGHT(
RC[-2],7),L
EFT(RC[-2],3)&""-""&LEFT(RC[-1],2)&""-""&RIGHT(RC[-1],4)))"

To translate, this basically says if the number in the cell is
less than 10000000 (or 7 digits long), then add two leading
zeros, and insert a hyphen between the 3rd and 4th digit and
insert a hyphen between the 5th and 6th digit. If the number in
the cell is less than 100000000 (or 8 digits long) then add one
leading zero, and insert a hyphen between the 3rd and 4th digit
and insert a hyphen between the 5th and 6th digit. If the cell
is greater than 699999999, then insert a hyphen between the 2nd
and 3rd digit. If none of the conditions apply (else), then
insert a hyphen between the 3rd and 4th digit and insert a
hyphen between the 5th and 6th digit.

I know how to get to design view on my table but I cannot say
whether the function I need to perform on this column can be
done from there. I also know how to create queries in Access,
but i do not know if the function I need to perform on this
column can be done from there either. I would appreciate if
someone could give me detailed instructions on how to perform
this function or direct me to a resource where i can learn on my
own how to perform this function.

Thank you in advance for any assistance you can provide.
Click to expand...
I'd use the IIF() function. Look at choose() also.

select iif(len(callnum) > 7,mid$(ssn,1,3) & "-" & mid(ssn,4,2) &
"-" & mid(ssn,6,4), [repeat for taxid here]) from yourTable
 
Hi WL,

Since these aren't actually numbers but merely strings of digits, I'd
tackle this one by storing them complete with hyphens in a text field.

Rename the field to something like IdnumOld. Add a text field (12
characters or so) to the table and call it Idnum. Then use a series of
update queries to convert the numbers to formatted strings and put them
in the new field. First do the tax IDs by updating the new field to
Format([IdnumOld],"00-0000000")
with a criterion of >699999999 on IdnumOld. Then do the remainder, the
SSNs, by updating the new field to
Format([IdnumOld],"000-00-0000")
with a criterion of Is Null on the new Idnum field.

Finally delete the old field.

Office XP
Windows XP

I asked this question several days ago, but since I know practically zilch
about Access, I did not understand the response (thanks again to Mr. John
Vinson). I'm very adept in Excel, but for my database usage requirements,
Access will work better. Here is my question.

I have a table imported. One of the columns (column heading Idnum) contains
number data ranging from 7 to 9 digits. Since these numbers are TAX id or
Social Security #'s, I have a couple of special formatting requirements. If
I just use a format of 000-00-0000 for this number string, it gives me
leading zeros for all numbers less than 9 digits and correctly formats the
social security numbers. However, this format does not take into account the
TAX ID #'s which should be formatted thusly 00-0000000. Luckily, all tax ID
numbers are greater than 699999999. In Excel, I used an If function. Here is
my Excel formula:

"=IF(RC[-2]<10000000,""00""&LEFT(RC[-2],1)&""-""&LEFT(RC[-1],2)&""-""&RIGHT(
RC[-1],4),IF(RC[-2]<100000000,""0""&LEFT(RC[-2],2)&""-""&LEFT(RC[-1],2)&""-"
"&RIGHT(RC[-1],4),IF(RC[-2]>699999999,LEFT(RC[-2],2)&""-""&RIGHT(RC[-2],7),L
EFT(RC[-2],3)&""-""&LEFT(RC[-1],2)&""-""&RIGHT(RC[-1],4)))"

To translate, this basically says if the number in the cell is less than
10000000 (or 7 digits long), then add two leading zeros, and insert a hyphen
between the 3rd and 4th digit and insert a hyphen between the 5th and 6th
digit. If the number in the cell is less than 100000000 (or 8 digits long)
then add one leading zero, and insert a hyphen between the 3rd and 4th digit
and insert a hyphen between the 5th and 6th digit. If the cell is greater
than 699999999, then insert a hyphen between the 2nd and 3rd digit. If none
of the conditions apply (else), then insert a hyphen between the 3rd and 4th
digit and insert a hyphen between the 5th and 6th digit.

I know how to get to design view on my table but I cannot say whether the
function I need to perform on this column can be done from there. I also
know how to create queries in Access, but i do not know if the function I
need to perform on this column can be done from there either. I would
appreciate if someone could give me detailed instructions on how to perform
this function or direct me to a resource where i can learn on my own how to
perform this function.

Thank you in advance for any assistance you can provide.
Click to expand...
 
Back
Top