Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-13-2019, 01:19 PM   #1
miacino
Newly Registered User
 
Join Date: Jun 2007
Posts: 68
Thanks: 16
Thanked 3 Times in 3 Posts
miacino is on a distinguished road
Extracting data between two commas

I've used left and mid strings before, but someone can't get a handle on this one!

DOE, JANE, MD

I'm trying to just get the first name (Jane) (the text between the two commas).

Any help would be appreciated!

miacino is offline   Reply With Quote
Old 02-13-2019, 01:28 PM   #2
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,273
Thanks: 0
Thanked 307 Times in 307 Posts
June7 will become famous soon enough
Re: Extracting data between two commas

Will there always be 2 commas?

Mid(x, InStr(x,",")+2, InStrRev(x,",")-InStr(x,",")-2)
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
June7 is online now   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
miacino (02-14-2019)
Old 02-13-2019, 01:32 PM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 919
Thanks: 8
Thanked 201 Times in 197 Posts
theDBguy will become famous soon enough
Re: Extracting data between two commas

Hi. You could also try this custom function: GetMember([FieldName],2)

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
miacino (02-14-2019)
Old 02-13-2019, 02:24 PM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,926
Thanks: 65
Thanked 1,316 Times in 1,213 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Extracting data between two commas

Or, for that matter, use SPLIT to pull the second string out of the implied array:

Code:
TheName = Split(TheString)(1)
Note that SPLIT returns its answers as a 0-based array, so the 2nd element is array(1).

https://www.techonthenet.com/access/...ring/split.php

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
miacino (02-14-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Append query: data conversion error not triggered by commas rsf Queries 2 06-27-2016 05:53 AM
Dealing with commas in data eacollie Modules & VBA 3 02-18-2014 12:20 PM
Sending email to data in multiple records (extracting data table/query ) Georgebochra Forms 0 01-23-2012 03:47 PM
Ignoring commas when users enter data shabbaranks Modules & VBA 6 01-23-2012 03:36 AM
Split Data In One Field Into Two -- With Commas databasedonr Queries 8 06-24-2003 01:27 PM




All times are GMT -8. The time now is 05:32 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World