By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,300 Members | 1,682 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,300 IT Pros & Developers. It's quick & easy.

Extracting data from a string/field

P: 76
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!!!
Apr 28 '09 #1
Share this Question
Share on Google+
7 Replies


Expert 100+
P: 1,287
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.
Apr 28 '09 #2

ADezii
Expert 5K+
P: 8,679
@artemetis
Assuming a precise, 21-digit Format, a code-based approach would look something like this:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstOriginal As DAO.Recordset
  3. Dim rstMaster As DAO.Recordset
  4.  
  5. Set MyDB = CurrentDb
  6. Set rstOriginal = MyDB.OpenRecordset("tblOriginal", dbOpenForwardOnly)
  7. Set rstMaster = MyDB.OpenRecordset("tblMaster", dbOpenDynaset)
  8.  
  9. With rstOriginal
  10.   Do While Not .EOF
  11.     rstMaster.AddNew
  12.       rstMaster![GroupMaster] = ![GroupMaster]
  13.       rstMaster![Group4] = Right(![GroupMaster], 3)
  14.       rstMaster![Group3] = Mid(![GroupMaster], 16, 3)
  15.       rstMaster![Group2] = Mid(![GroupMaster], 13, 3)
  16.       rstMaster![Group1] = Mid(![GroupMaster], 10, 3)
  17.     rstMaster.Update
  18.     .MoveNext
  19.   Loop
  20. End With
  21.  
  22. RefreshDatabaseWindow
  23.  
  24. rstOriginal.Close
  25. rstMaster.Close
  26. Set rstOriginal = Nothing
  27. Set rstMaster = Nothing
Apr 28 '09 #3

P: 76
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!!!
Apr 28 '09 #4

Expert 100+
P: 1,287
Edited this out so you don't accidentally do it before reading subsequent posts.
Apr 28 '09 #5

Expert 100+
P: 1,287
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.
Apr 28 '09 #6

P: 76
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!
Apr 28 '09 #7

NeoPa
Expert Mod 15k+
P: 31,707
This should be an easy SQL job :
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblMaster
  2.       (uid,
  3.        firstName,
  4.        lastName,
  5.        groupMaster,
  6.        group1,
  7.        group2,
  8.        group3)
  9.  
  10. SELECT uid,
  11.        firstName,
  12.        lastName,
  13.        groupMaster,
  14.        Mid([groupMaster],10,3) AS group1,
  15.        Mid([groupMaster],13,3) AS group2,
  16.        Mid([groupMaster],19,3) AS group3
Apr 29 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.