473,320 Members | 1,950 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Dealing with Name data

I want to copy the following data from field Owner to field
PropertyOwner:
Desired Result
HAMILTON,JEFF JEFF HAMILTON
JOHNSON, JIMMY JIMMY JOHNSON
JONES, MARY & DAVID MARY & DAVID JONES
SMITHSON INDUSTRIES SMITHSON INDUSTRIES
GONZALES, TRUST GONZALEZ TRUST
DAVIDIAN,TR DAVIDIAN TR
CHO,TR ET AL CHO TR ET AL
O'BANNON, LIVING TRUST O'BANNON LIVING TRUST

I can deal with the first 5 examples, but the variations of TRUST are
killing me. Can anyone help me? I'm not a programmer. I have a sample
table, query and VBA module that I think is close to the right thing.
I can email to someone who responds.

Thanks
Nov 12 '05 #1
4 1491


Jeff Hamilton wrote:
I want to copy the following data from field Owner to field
PropertyOwner:
Desired Result
HAMILTON,JEFF JEFF HAMILTON
JOHNSON, JIMMY JIMMY JOHNSON
JONES, MARY & DAVID MARY & DAVID JONES
SMITHSON INDUSTRIES SMITHSON INDUSTRIES
GONZALES, TRUST GONZALEZ TRUST
DAVIDIAN,TR DAVIDIAN TR
CHO,TR ET AL CHO TR ET AL
O'BANNON, LIVING TRUST O'BANNON LIVING TRUST

I can deal with the first 5 examples, but the variations of TRUST are
killing me. Can anyone help me? I'm not a programmer. I have a sample
table, query and VBA module that I think is close to the right thing.
I can email to someone who responds.

Thanks


I see that Smithson industries did not have a comma. Those with Trust
do. I work with A97 and there is no built in Access function to remove
commas. Here is an example of one
Dim strName As "Jeff, Hamilton
Debug.Print Left(strName,Instr(strName,",") - 1) & " " & _
Trim(Mid(strName(Instr(strName,",") + 1))
This will print Jeff Hamilton, The first Left/Instr finds the comma and
takes all of the characters to the left of the comma to 1 character
before the command and the second Mid/Instr takes all characters after
the comma and concatenates it it with the first on. It adds a space
between commas and trims spaces in the second Mid/Instr.

You could build a query. In one column enter
Instr({YourTableFieldName]," Trust") > 0 and in the criteria row enter
True. In another column, drag the field name to update, make the query
and update, and in the update row put my routine excluding the
Debug.Print.

I think you will have to make multiple passes since you may have a "space
Trust", and ", Trust", a ",Trust" . Then do the same for your TRs.
Nov 12 '05 #2
Jeff Hamilton wrote:
I want to copy the following data from field Owner to field
PropertyOwner:
Desired Result


If you do mass updates using a routine line the one I posted, I recommend
you first click on the table and then do a CTRL+C and then a CTRL+V to
create a copy of the table. You may not want to do mass updates without
a backup....this ensure you can start over if you mess it up.
Nov 12 '05 #3

Thanks very much for your reply. I appreciate the fact that you
explained the code--that helps me to learn and maybe I don't have to beg
for help so much in the future.

I received a reply from another source that uses VBA code in an Access
module that solves the problem. I don't remember if A97 supports the
same features, but if you're interested, send me an email and I'll
forward the code to you.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #4
Jeff Hamilton wrote:
Thanks very much for your reply. I appreciate the fact that you
explained the code--that helps me to learn and maybe I don't have to beg
for help so much in the future.

I received a reply from another source that uses VBA code in an Access
module that solves the problem. I don't remember if A97 supports the
same features, but if you're interested, send me an email and I'll
forward the code to you.


People that post responses here post 1) to the poster, and 2) to everyone
else. You may have a problem that others will have in the future. They may
read this thread months from now and go "AhhHa!, that's the solution!" Many
of us use Google to find answers to our problems first before posting a
question. Google archives the messages in the newsgroup. Private replies,
as well as the solution, are private. Nobody here cares whether or not you
received a solution here or privately. Often times people will post the
solution they came up on their own, or in your case privately, so everyone
can benefit.

The bottom line....this newsgroup is a repository of knowledge. Private
responses are worthless to the knowledge base.

So thank you for your offer, but no thanks.
Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Eric | last post by:
Let me start by saying that I'm still very new at PHP/MySql and will take any advice. :-) The issues is that every once in a while users get an error when they use this code. I believe the...
1
by: ccarpenter | last post by:
(This has been posted on the PHP.general group also.) I'm writing an app where I want the user to be able to change a date on a record. Date field is 'TngDate'. I can pull the record from the...
1
by: Fazer | last post by:
Hello, I have the following code: #!/usr/local/bin/python import cgi import Cookie C = Cookie.SimpleCookie() C = "f"
2
by: Wayne | last post by:
This is probably simple but: The following portion of an SQL statement that I've copied from the SQL view of the query grid (the query works fine) is giving me an error to the effect that it is...
2
by: Jesse | last post by:
Whenever I write a line of code that uses quotation marks, such as.. <% foreach (merri.Data.dsMerri.AlbumDetailsRow dr in merri.Business.AllBusiness.ArtistAndAlbumByStyle("Children")) {%> I...
16
by: lovecreatesbeauty | last post by:
/* When should we worry about the unwanted chars in input stream? Can we predicate this kind of behavior and prevent it before debugging and testing? What's the guideline for dealing with it? ...
3
by: Richard Lewis Haggard | last post by:
We are having a lot of trouble with problems relating to failures relating to 'The located assembly's manifest definition with name 'xxx' does not match the assembly reference" but none of us here...
5
by: Aussie Rules | last post by:
Hi, I have about 30 sql SP with around 50 paramaters each. Currently in each SP, each param has its own name (prmC, prmD etc). Just out of being a bit lazy, is there away that each param can...
16
by: pereges | last post by:
ok so i have written a program in C where I am dealing with huge data(millions and lots of iterations involved) and for some reason the screen tends to freeze and I get no output every time I...
0
AmberJain
by: AmberJain | last post by:
Windows Autorun FAQs: Programs dealing with autoruns Linked from the Original article- "Windows Autorun FAQs: Description". Que: Can you list programs that help me to view/modify the autoruns...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.