not sure how to do this any advice appricated

[cybertyk]

ok i have 3 fields in my database

~Contact Title
~Contact FName
~Contact LName

how ever when i ported data using a ammend quary from the old database the name was in one feild now as this database was in action for a bit there is a mix of data in the feilds

for example it could be
~Contact Title~Contact FName~Contact LName
--null----------Mr. J Bloggs------Null----------
--Mr-----------J----------------Smith-----------
--Mrs------------Hordwell---------Null-----------
--null-------------mr j-------------cybertyk


i was thinking about using something like

title: Trim(Left([Contact FName],InStr([contact FName]," ")-1))

to pull the title out how ever it would so take the first name or last name

is there away around this or do i have to alter all 2500 records

Ta

 

[cybertyk]

sorry forgot to say that i need to make the feilds say all

is there a way to add paramiters to the
title: Trim(Left([Contact FName],InStr([contact FName]," ")-1))

to say something like

if contact fname has 2 X " " then its has "MR J Blogs"
move MR to TITLE Move J to FNAME and Move Blogs to Last name

else if F Name has 1 x " " then it has J Blogs
Move J to Fname to Fname and Blogs to LASt Name

end if

?

 

[vbaInet]

Well your spreadsheet (which I believe it is) has irregularities so writing code for just 2500 records isn't worth the hassle. Use an Excel statement to merge two columns at a time checking for BLANK (on the first column) and using IF(). Then use Text To columns to split that merged row using space as a delimeter.

 

[cybertyk]

nope its a accsses database

 

[vbaInet]

There you go then, export it to Excel and play with it there. As long as the CustomerID isn't an autonumber field you should be fine importing it back when it's done.

It worries me as to how you had those irregularities in the first place. Something must be wrong with the design.

 

[cybertyk]

whats happend is that the old database is a ACT one, ive been asked to replace it with a new one to handel things diffently. so i designed the database and added 200 records by hand to test it making sure the database worked ok, then i had to import the act records over, how ever act only has a name feild wher as mine has a Title Fname and Lname, this is so that when i use th group mail merge the letters are formal, so after importing 2300 records which have been abused for the last 10 years or so the data ported in ok but there where irregularities with the data from act,

so all im gonna do is export the contacts table, and have a crack at some speedsheet fiddiling havent done that in a while lol.

i just hate this ACT Database more and more day by day lol,

 

[vbaInet]

Yes it would be much easier to correct the data there then import it. It's easier to see the whole data and manage it in Excel.

Didn't you notice this before importing it into your database? Something I thought you would have checked before importing?

 

[cybertyk]

to be honist i did notive it and i designed the database so it had 1 feild but the boss wanted first and last name separate which made life a bit intresting lol.

 

[vbaInet]

The boss is great isn't he?

 

[cybertyk]

Hes alright just wants it how he needs it lol

 

[vbaInet]

If you're ever in a position like that just get the data right in the tables, but in the control represent it the way he wants it