Question Splitting a field depending on its values?

[kevincole]

Hi all,

I wonder if someone can help me with what should be a simple task.

I have a table of user details that has been imported from an Oracle database.

Within this table are two fields Personnel Number and ID.

I would like each field to reflect the correct value for this field and if required split the personnel field across personnel and ID.

For example.

In the personnel field might currently be AB1234, this would be correct, however it may read AB1234 - 1234567, in this particular instance personnel should read AB1234 and ID should read 1234567, thus stripping out the - and writing the value to ID.

If the personnel field was to read just 1234567 I would want this to be blanked out and the value of 1234567 written to the ID field.

Can anyone offer any suggestions with this? I am aware that Excel has a Text To Columns facility that, does to some degree what I am trying to achieve, but surely there is an easier way of achieving this in Access? The table I am manipulation has in excess of 7.5k records.

Any help that anyone can offer is greatly appreciated.

Kindest regards,
Kev

 

[JamesMcS]

Probably the easiest way would be to export these columns as a text file, and import into Access using " - " as a field separator.

Otherwise you're going to have to get into select cases and all sorts of VBA fun.

 

[kevincole]

Thanks James I thought that would probably be the case.

Thanks for your help.

Cheers,
Kev

 

[JamesMcS]

Good luck!

 

[kevincole]

Just had another look at this.

If I use - as a field seperator any other fields that use this seperator will become corrupted, i.e. team name field where by users team names sometimes have a - within them.

I am now thinking that maybe I will need to import into Excel carry out the Text to Columns on the Pid and Personnel field only and then re-import into Access.

I'm not convinced that this is the easiest way - surely there must be an easier way of achieving this ?

Thanks in advance.

 

[JamesMcS]

Beats me - I was suggesting just the two columns that you know are separated by " - "... but I'm not too skilled at this kind of thing, sorry!

 

[kevincole]

If only it were that easy James.

Thanks again for yours asssitance.

Regards,

 

[Brianwarnock]

You can probably do this with an update query, my example assumes that the personnel code is a fixed 6 characters, if not more work needs to be done.

Add 2 new fields to the table and update these as follows
the code uses pers for your original
newpers field to
IIf(IsNumeric(Left([pers],1)),"",IIf(InStr([pers],"-")=0,[pers],Left([pers],6)))
newpersid field to
IIf(IsNumeric(Left([pers],1)),[pers],IIf(InStr([pers],"-")<>0,Mid([pers],10)))

you can then clean up your table after a visual check.

Brian

 

[vbaInet]

You could possibly try this approach:

1. Use the split function by using the "-" and the delimeter
2. Save the items from the split function into an array
3. Count how many items there are in the array (UBound function), if it's 1 then save both values, else save the only array item to the ID field.

 

[kevincole]

Thanks everyone for your suggestions. I'm going to take a look at this tomorrow now. I will report back.

Thanks again,
Kind regards,

 

[kevincole]

Thank you all for your help. With a little jigging about with the source data, I managed to achieve what was required with the method suggested by Brianwarnock, so special thanks to him.

Thanks again all - no doubt I will be back in the future pestering for help, and obviously if I can help out at all I will.

Kindest regards,