Split field into several fields... with SQL?
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
Split field into several fields... with SQL?
Hello everyone!
Hey, I'm getting a lab report from a lab, but they're putting city, county, state and zip all in one field. Each item is separated with a comma: Nashville, Davidson, TN, 37243 for example. All the zips are 5 digit, none are the longer 9 digit ones, which is fine.
Is there an easy way to split this field (Pt_City in TblSolstasProcessing), into fields called Pt_City (Nashville), Pt_ST (TN), PT_Zip (37243) and PT_County (Davidson)?
Thanks
MishMish3000
Hey, I'm getting a lab report from a lab, but they're putting city, county, state and zip all in one field. Each item is separated with a comma: Nashville, Davidson, TN, 37243 for example. All the zips are 5 digit, none are the longer 9 digit ones, which is fine.
Is there an easy way to split this field (Pt_City in TblSolstasProcessing), into fields called Pt_City (Nashville), Pt_ST (TN), PT_Zip (37243) and PT_County (Davidson)?
Thanks
MishMish3000
Anne
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Split field into several fields... with SQL?
If all records contain city, county, state and zip, you can use the following expressions in a query:
Sorry - scrap my original reply, it doesn't work.
Sorry - scrap my original reply, it doesn't work.
Last edited by HansV on 12 Sep 2011, 19:34, edited 2 times in total.
Reason: to correct mistakes
Reason: to correct mistakes
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
Re: Split field into several fields... with SQL?
Excellent! I'll do that now.
Thanks, Hans! I was getting all snarled up in Left(), Mid() and Right() stuff.
This looks much more elegant.
MishMish3000
Thanks, Hans! I was getting all snarled up in Left(), Mid() and Right() stuff.
This looks much more elegant.
MishMish3000
Anne
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
Re: Split field into several fields... with SQL?
When I put the code in an update query, I get an error message. I'll attach a Word document with a screen shot.
Thanks
MishMish3000
Thanks
MishMish3000
You do not have the required permissions to view the files attached to this post.
Anne
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Split field into several fields... with SQL?
Oops, I'm sorry. Split doesn't work in queries. Create the following custom function in a module:
Now you should be able to use the following expressions in a query:
Pt_City: MySplit([TblSolstasProcessing].[Pt_City], 1)
Pt_County: MySplit([TblSolstasProcessing].[Pt_City], 2)
Pt_ST: MySplit([TblSolstasProcessing].[Pt_City], 3)
Pt_Zip: MySplit([TblSolstasProcessing].[Pt_City], 4)
Code: Select all
Function MySplit(MyString As String, n As Long) As String
Dim arrParts
arrParts = Split(MyString, ",")
If n > 0 And n - 1 <= UBound(arrParts) Then
MySplit = arrParts(n - 1)
End If
End Function
Pt_City: MySplit([TblSolstasProcessing].[Pt_City], 1)
Pt_County: MySplit([TblSolstasProcessing].[Pt_City], 2)
Pt_ST: MySplit([TblSolstasProcessing].[Pt_City], 3)
Pt_Zip: MySplit([TblSolstasProcessing].[Pt_City], 4)
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
Re: Split field into several fields... with SQL?
Yay! Many thanks! I'll try this out and let you know!
MishMish3000
Anne
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
Re: Split field into several fields... with SQL?
I tried to get this to work but I must be going about it the wrong way. I made the module...
I've de-identified all information in this little version of the lab report database, and zipped it... if anyone would like to take a look at it, I'd appreciate it. I think the "MySplit" module is the way to go; it looks really elegant. I'm just not sure how to use a query with it to split the patient address field and the doctor address field.
Let me know what you think.
Thanks
MishMish3000
You do not have the required permissions to view the files attached to this post.
Anne
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Split field into several fields... with SQL?
You have given the module the same name as the function (MySplit). That will confuse Visual Basic. Please rename the module, for example to basSplit (or whatever you want, as long as it's different from MySplit).
In the attached version I have added a query that will update TblSolstasProcessing. You should run this query only once!
In the attached version I have added a query that will update TblSolstasProcessing. You should run this query only once!
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
Re: Split field into several fields... with SQL?
Sorry, I didn't know that about naming the module! My bad!
Thanks!
MishMish3000
Anne
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
Re: Split field into several fields... with SQL?
Hans, it works like a charm! Thanks so much! The split module is so cool! I know other folks out there can use it too.
Thanks again
MishMish3000
Anne
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
Re: Split field into several fields... with SQL?
Hmm. Perhaps it's something I've done, or something one of the end users has done, but I'm getting an odd error message now. It had been working great! I'll attach screen shots. I also had a second question to do with counting specific types of tests in a footer... but that's secondary to getting the report database back to health again!
Thanks, MishMish3000
Thanks, MishMish3000
You do not have the required permissions to view the files attached to this post.
Anne
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Split field into several fields... with SQL?
For the first question, I'd have to see the database.
For the second question:
Place a text box with control source =Count(*) in the group header for Region. (If you prefer, you can specify that Region has a group footer as well as a group header, and place the text box there)
For the second question:
Place a text box with control source =Count(*) in the group header for Region. (If you prefer, you can specify that Region has a group footer as well as a group header, and place the text box there)
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
Re: Split field into several fields... with SQL?
I figured out the first problem---the end user had deleted some of the address information in the import file, so the module didn't have anything to work with!
The second solution is GREAT! It does just the thing. THANKS, Hans!!!!!! Again, if I'm ever in your neck of the woods, I'll treat you to a beverage of your choice! LOL
MishMish3000
The second solution is GREAT! It does just the thing. THANKS, Hans!!!!!! Again, if I'm ever in your neck of the woods, I'll treat you to a beverage of your choice! LOL
MishMish3000
Anne
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Split field into several fields... with SQL?
Glad you found the solution for the first problem - it's hard to troubleshoot such things from a distance.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN