Extracting data from a string/field

[artemetis]

Ok, I'm back again.
I've got the following table.


TableName = tblOriginal
uid - self explanatory
firstName - self explanatory
lastName - self explanatory
groupMaster - contains a numeric string containing 21 digits.

My question surrounds the 'groupMaster' field.
I need the first four groups of three, from the right to be inserted into another existing table, along with the other corresponding fields.

sample string from groupMaster: 333444555666777888999

Looking at the above string:
999 will be inserted into tblMaster.group3
777 will be inserted into tblMaster.group2
666 will be inserted into tblMaster.group1
*all other fields will insert into corresponding names.

TableName = tblMaster
uid
firstName
lastName
groupMaster
group1
group2
group3


As always............thanks in advance!!!

[ChipR]

Hi artemetis,
You want to use the Mid Function (http://office.microsoft.com/en-us/ac...288811033.aspx)
See if you have any specific problems with that.

[ADezii]

@artemetis
Assuming a precise, 21-digit Format, a code-based approach would look something like this:

Dim MyDB As DAO.Database
Dim rstOriginal As DAO.Recordset
Dim rstMaster As DAO.Recordset
 
Set MyDB = CurrentDb
Set rstOriginal = MyDB.OpenRecordset("tblOriginal", dbOpenForwardOnly)
Set rstMaster = MyDB.OpenRecordset("tblMaster", dbOpenDynaset)
 
With rstOriginal
  Do While Not .EOF
    rstMaster.AddNew
      rstMaster![GroupMaster] = ![GroupMaster]
      rstMaster![Group4] = Right(![GroupMaster], 3)
      rstMaster![Group3] = Mid(![GroupMaster], 16, 3)
      rstMaster![Group2] = Mid(![GroupMaster], 13, 3)
      rstMaster![Group1] = Mid(![GroupMaster], 10, 3)
    rstMaster.Update
    .MoveNext
  Loop
End With
 
RefreshDatabaseWindow
 
rstOriginal.Close
rstMaster.Close
Set rstOriginal = Nothing
Set rstMaster = Nothing

[artemetis]

Thanks Folks!

ADezii - how does your code process a blank location, or would it?
For example(s):
"777 999"
"777888 "
where each block of missing values has three null spaces?
I'm seeing that in many of my records.

Thanks again in advance!!!

[ChipR]

Edited this out so you don't accidentally do it before reading subsequent posts.

[ChipR]

Sorry, ignore that last suggestion. I though from your example that the items had spaces between them, but a null value is a different story. If you don't use [ code ] tags, your multiple spaces get turned into single spaces.
ADezii's code will still work fine in this case. Blanks are treated much differently from NULLs.

[artemetis]

Thanks ChipR.

Yeah, the data that I've been given is a MESS.
In that field of 21, if there is no three digit value, there are three spaces.
It's like 7 fields of three in one field. Grrrrrrrrrrr!

[NeoPa]

This should be an easy SQL job :

INSERT INTO tblMaster
      (uid,
       firstName,
       lastName,
       groupMaster,
       group1,
       group2,
       group3)
 
SELECT uid,
       firstName,
       lastName,
       groupMaster,
       Mid([groupMaster],10,3) AS group1,
       Mid([groupMaster],13,3) AS group2,
       Mid([groupMaster],19,3) AS group3