Special Formatting

[Wendy L]

Windows XP and Office XP

Forgive me for cross posting but I have received no response in the "getting
started" sub directory of this group.

I import data into a table from a text file. The two fields are "Idnum" and
"Idtype". When these fields are imported, they represent social security
numbers and entity numbers in the "Idnum" field and either an "E" or an "S"
in the "Idtype" field indicating whether the number in "Idnum" field of data
is an entity number or a social security number .

So my table might look like this

Idnum Idtype
78901234 S
751234567 E

What i would like to do is somehow add leading zeros to all strings in the
"Idnum" field that are less than 9 numeric characters long. Also, if the
"Idnum" is indicated as "S" in the "Idtype" field, I would like to format it
as a social security number (078-90-1234). If "E", i would like to format it
as an entity number (75-1234567).

Could someone please give me simplistic instructions on how this might be
accomplished? Thank you in advance for any advice rendered.

~Wendy

 

[Cheryl Fischer]

After you have imported the data to your table, you can do an UpdateQuery on
the IDNum field, using the following expression in the Update to: row of the
query:


IIf([idtype]="S",Format([idnum],"000-00-0000"),Format([idnum],"00-0000000"))


Try using the above expression first in a select query to see how it works
for you. If it's ok, then do an update query, which will make the update
permanent.

hth,

 

[Guest]

Wendy

You need to set up the data fields like this
Idnu
Data Type - Numbe
Field Size - Long Intege
Format - 00000000
Validation rule <=99999999
Validation text - Must contain only 9 digits

Idtyp
Data Type - Tex
Field Size -
Format - > (force uppercase
Validation Rule ="E" or ="S

For any report or form which displays the data, you can write an if statement (although it will end up appearing rather complex) that will format the data properly

OK, now for the bad news. While these formats will make your numbers appear to have leading zeros, they won't really, they will just look that way. Second, I'm not sure you want to keep two different types of data in the same field. (Violation of the normalization guidelines.

Bob Sulliva

----- Wendy L wrote: ----


I import data into a table from a text file. The two fields are "Idnum" an
"Idtype". When these fields are imported, they represent social securit
numbers and entity numbers in the "Idnum" field and either an "E" or an "S
in the "Idtype" field indicating whether the number in "Idnum" field of dat
is an entity number or a social security number

So my table might look like thi

Idnum Idtyp
78901234
751234567

What i would like to do is somehow add leading zeros to all strings in th
"Idnum" field that are less than 9 numeric characters long. Also, if th
"Idnum" is indicated as "S" in the "Idtype" field, I would like to format i
as a social security number (078-90-1234). If "E", i would like to format i
as an entity number (75-1234567)

Could someone please give me simplistic instructions on how this might b
accomplished? Thank you in advance for any advice rendered

~Wend

 

[Wendy L]

Thank you Cheryl, this worked perfectly to perform the actions i wished to
perform. I appreciate so much your taking time to respond.

After you have imported the data to your table, you can do an UpdateQuery on
the IDNum field, using the following expression in the Update to: row of the

query:

IIf([idtype]="S",Format([idnum],"000-00-0000"),Format([idnum],"00-0000000"))


Try using the above expression first in a select query to see how it works
for you. If it's ok, then do an update query, which will make the update
permanent.

hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

Windows XP and Office XP

Forgive me for cross posting but I have received no response in the "getting
started" sub directory of this group.

I import data into a table from a text file. The two fields are "Idnum" and
"Idtype". When these fields are imported, they represent social security
numbers and entity numbers in the "Idnum" field and either an "E" or an "S"
in the "Idtype" field indicating whether the number in "Idnum" field of data
is an entity number or a social security number .

So my table might look like this

Idnum Idtype
78901234 S
751234567 E

What i would like to do is somehow add leading zeros to all strings in the
"Idnum" field that are less than 9 numeric characters long. Also, if the
"Idnum" is indicated as "S" in the "Idtype" field, I would like to

format

it
as a social security number (078-90-1234). If "E", i would like to

format

it
as an entity number (75-1234567).

Could someone please give me simplistic instructions on how this might be
accomplished? Thank you in advance for any advice rendered.

~Wendy

 

[Wendy L]

Thank you for your response Bob. I appreciate your taking time to offer your
expertise.

Wendy,

You need to set up the data fields like this:
Idnum
Data Type - Number
Field Size - Long Integer
Format - 000000000
Validation rule <=999999999
Validation text - Must contain only 9 digits.

Idtype
Data Type - Text
Field Size - 1
Format - > (force uppercase)
Validation Rule ="E" or ="S"

For any report or form which displays the data, you can write an if

statement (although it will end up appearing rather complex) that will
format the data properly.

OK, now for the bad news. While these formats will make your numbers

appear to have leading zeros, they won't really, they will just look that
way. Second, I'm not sure you want to keep two different types of data in
the same field. (Violation of the normalization guidelines.)