AfterUpdate question

[BartK]

Hi everyone. Here is what I am wanting to do. I initially thought an input mask could accomplish this, but after some further reading on it what I am getting is only VBA can get it done. Here is what I am trying to figure out. I have taken over a DB where the original designer had a lot of extraneous info.

EX. Smith John Personal number 83748473 etc. etc. etc.
I would like to get to this:
Smith,John

Is there any way to get this done? Not all records are stored the same way, would it be easier for me to just manually correct the field in the table or could this be an easy fix.

 

[pr2-eugin]

Show a little more sample data.. If possible put it inside CODE tags to preserve Formatting like..

someID    equipmentID_FK    someData
1                1            Relationship is important
2                1            in an RDBM System.
3                2            Lookup Normalization.

 

[BartK]

Here is what I have. I have one record that has around 1,600 entries in the database. The entries look like this:

Smith John SSN 123-45-5678
Doe, John Social Security Number 123-44-5567
etc.
etc.
etc.
I would liketo go to this:
Smith, John
Doe, John
Ignoring all the other information. Hopefully I answered your question.

 

[pr2-eugin]

You mean you have one table tblNoName, that has only one column notNormalizedData that has all information as..

Smith John SSN 123-45-5678
Doe, John Social Security Number 123-44-5567
Francis, Paul SSN 024-12-7841
Paul Eugin Social Security Number 897-11-8211

Or do you have one table tblNoName, that has several columns like..

[B]userName        SSNTxt   [/B] 
Smith John       123-45-5678
Doe, John        123-44-5567
Francis, Paul    024-12-7841
Paul Eugin       897-11-8211

 

[BartK]

Sorry about the confusion, really early in the morning for me when I posted it. Yes I have 1 table with multiple columns, the one column that I would like to "shrink" down is our inspector field, essentially the data listed below.

Smith John SSN 123-45-5678
Doe, John Social Security Number 123-44-5567
Francis, Paul SSN 024-12-7841
Paul Eugin Social Security Number 897-11-8211

 

[WayneRyan]

BartK,

This looks weird but should do it:

It extracts all characters prior to the 2nd space.
I append two spaces to Inspector to account for value of something like "JOE".

Select Mid([Inspector] & "  ", 1, InStr(InStr(1, [Inspector] & "  ", " "), [Inspector] & "  ") - 1)
From   YourTable

Wayne

 

[BartK]

Wayne,
Where would you suggest I put the code at? I put it onload on the main form, however it came back syntax error and highlighted Select Mid etc. etc.

Sounds like it might be a little easier to manually correct the column. Thanks once again.

 

[pr2-eugin]

What do you want to do with this data?

 

[BartK]

I just want to shrink the data within the column that I have is all. Trying to get to some sort of input mask after that, first step, atleast what I think is to do away with all the extra info in the column.

 

[WayneRyan]

Bart,

After you've BACKED up your data, then just put the part starting with the Mid statement into the "Update To" part of an update query for the [Inspector] field.

Wayne