Split field into several fields... with SQL?

[mishmish3000]

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

[HansV]

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.

[mishmish3000]

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

[mishmish3000]

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

[HansV]

Oops, I'm sorry. Split doesn't work in queries. Create the following custom function in a module:

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

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)

[mishmish3000]

Yay! Many thanks! I'll try this out and let you know!
MishMish3000

[mishmish3000]

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

[HansV]

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!

TEST Lab Reports.zip

[mishmish3000]

Sorry, I didn't know that about naming the module! My bad!
Thanks!
MishMish3000

[mishmish3000]

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

[HansV]

You're welcome!

[mishmish3000]

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

[HansV]

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)

[mishmish3000]

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

[HansV]

Glad you found the solution for the first problem - it's hard to troubleshoot such things from a distance.

[mishmish3000]

Thanks for all the help and encouragement!!!!