By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 1,382 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

<<HELP>> Getting rid of part of a text field

P: n/a
Hello,

I have an Access XP database that has several fields. One of the
fields is a text field and has an account number that is preceeded by
a text character, like: F102354. Every account number has this prefix
and is always followed by a 6 digit number.

What I need to do is to drop the character at the beginning and keep
the remaining part of the account number.

Is there a way to remove this character without having to do it
manually because I have over 40,000 fields to change.

I would appreciate any help.

Thanks
Chuck
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
DFS
Chuck,

This update query will do it, but you should backup the original data before
updating just in case you change your mind or need it later:

UPDATE [TABLE] SET [FIELD] = Mid([FIELD],2) WHERE [FIELD] Is Not Null and
Len([FIELD]) = 7;

The brackets are needed only if your table and/or field names have spaces in
them. The Len() check is optional, too, but you might want to use it.

"Chuck" <w0***@yahoo.com> wrote in message
news:64**************************@posting.google.c om...
Hello,

I have an Access XP database that has several fields. One of the
fields is a text field and has an account number that is preceeded by
a text character, like: F102354. Every account number has this prefix
and is always followed by a 6 digit number.

What I need to do is to drop the character at the beginning and keep
the remaining part of the account number.

Is there a way to remove this character without having to do it
manually because I have over 40,000 fields to change.

I would appreciate any help.

Thanks
Chuck

Nov 12 '05 #2

P: n/a
If you want to do this to records in a table, create an update query that only
includes the account number field and put this expression where it says "Update
To":
Right([NameOfAccountNumberField],6)
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Chuck" <w0***@yahoo.com> wrote in message
news:64**************************@posting.google.c om...
Hello,

I have an Access XP database that has several fields. One of the
fields is a text field and has an account number that is preceeded by
a text character, like: F102354. Every account number has this prefix
and is always followed by a 6 digit number.

What I need to do is to drop the character at the beginning and keep
the remaining part of the account number.

Is there a way to remove this character without having to do it
manually because I have over 40,000 fields to change.

I would appreciate any help.

Thanks
Chuck

Nov 12 '05 #3

P: n/a
An****************@See.Comment.Header (U N Me) wrote:
Check out self.zip at


Do NOT click on the above URL.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #4

P: n/a
Tony Toews wrote:
An****************@See.Comment.Header (U N Me) wrote:
Check out self.zip at


Do NOT click on the above URL.

Tony


It appears I have made the anonomous-remailer mad. It's a shame used my
e-mail address to post his abberations and deleriums.

One has to pity the fool. I imagine he gets sexually excited as he
peruses the internet looking for pornography to show to others. I don't
imagine it shows it to his mother, father, friends, and co-workers.
That would confirm the fact it is psychotic, most likely a sociopath.

This is a definition of a sociopath. I believe it describes
Anonymous-Remailer aptly. It appears it subscribes to 10 out of the 10
general symptoms/

Antisocial Personality Disorder is also known as psychopathy or
sociopathy. Individuals with this disorder have little regard for the
feeling and welfare of others. As a clinical diagnosis it is usually
limited to those over age 18. It can be diagnosed in younger people
if the they commit isolated antisocial acts and do not show signs of
another mental disorder.

Antisocial Personality Disorder is chronic, beginning in adolescence
and continuing throughout adulthood. There are ten general
symptoms:

not learning from experience
no sense of responsibility
inability to form meaningful relationships
inability to control impulses
lack of moral sense
chronically antisocial behavior
no change in behavior after punishment
emotional immaturity
lack of guilt
self-centeredness

People with this disorder may exhibit criminal behavior. They may
not work. If they do work, they are frequently absent or may quit
suddenly. They do not consider other people's wishes, welfare or
rights. They can be manipulative and may lie to gain personal
pleasure or profit. They may default on loans, fail to provide child
support, or fail to care for their dependents adequately. High risk
sexual behavior and substance abuse are common. Impulsiveness,
failure to plan ahead, aggressiveness, irritability, irresponsibility,
and a reckless disregard for their own safety and the safety of
others are traits of the antisocial personality.

Socioeconomic status, gender, and genetic factors play a role.
Males are more likely to be antisocial than females. Those from
lower socioeconomic groups are more susceptible. A family history
of the disorder puts one at higher risk.

There are many theories about the cause of Antisocial Personality
Disorder including experiencing neglectful parenting as a child, low
levels of certain neurotransmitters in the brain, and belief that
antisocial behavior is justified because of difficult circumstances.
Psychotherapy, group therapy, and family therapy are common
treatments. The effects of medical treatment are inconclusive.
Unfortunately, most people with Antisocial Personality Disorder
reject treatment. Therefore, recovery rates are low.

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.