UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Separating Text String After 1st Space, Access 2016    
 
   
DAVEMT99
post Nov 3 2019, 04:18 AM
Post#1



Posts: 3
Joined: 3-November 19



I have a field with Mfg Part Number. The problem is the the P/N starts with the Manufacturer (Mfg) and then a space, the actual P/N may also have a space in the string

I have extracted the Mfg Name but am unable to extract the remaining characters after the 1st Space

Can anyone help, the field name is MPN (short for Mfgs P/N)

Thanks
Go to the top of the page
 
DanielPineault
post Nov 3 2019, 04:20 AM
Post#2


UtterAccess VIP
Posts: 7,011
Joined: 30-June 11



Some concrete examples would help.
Where are you wanting to do this Query, Form, VBA?


Assuming that the field is named P/N, then you can build query fields like
CODE
Mfg: Left([P/N],InStr([P/N]," ")-1)
PartNo: Mid([P/N],InStr([P/N]," ")+1)


You can just as easily do this in VBA
CODE
Function getMfg(vInput As Variant) As Variant
    If IsNull(vInput) = True Then
        getMfg = vInput
    Else
        getMfg = Split(vInput, " ")(0)
    End If
End Function

Function getPartNo(vInput As Variant) As Variant
    If IsNull(vInput) = True Then
        getPartNo = vInput
    Else
        getPartNo = Split(vInput, " ")(1)
    End If
End Function

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
DAVEMT99
post Nov 4 2019, 05:30 AM
Post#3



Posts: 3
Joined: 3-November 19



Thanks Daniel, just seen this now. Hadnt realised had to go into the forum

Will carry out your suggestions in a query. Dont understand VBA

Many thanks
David
Go to the top of the page
 
DAVEMT99
post Nov 4 2019, 05:37 AM
Post#4



Posts: 3
Joined: 3-November 19



Thanks Daniel
Works perfectly. I had tried google before posting but I didnt find anything that seemed to fit

Many Regards
David
Go to the top of the page
 
DanielPineault
post Nov 4 2019, 05:50 AM
Post#5


UtterAccess VIP
Posts: 7,011
Joined: 30-June 11



thumbup.gif

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2019 - 10:50 AM