'Deconstructing' a field's data

[russi]

Hi.
Way back when, it was decided that 3 fields' data should be merged into 1 saved field. - VendorName, ContractNbr, and TrngPrg into TrngInfo.
And, so, dynamically this was done through a form.

Now, we are leaving Access for a different package and I have been asked to deconstruct, if you will, each client's TrngInfo, back into it's original 3 parts.

The VendorName is only letters. The ContractNbr begins with 'W' followed by numbers and hyphens. And, TrngPrg is only Letters.
They exist in that order in each TrngInfo record.

Is there a way to separate them into their 3 component parts for each client?
(Note there is a ClientTable with the client's IDnumber related to a TrainingTable with the client's IDnumber and the TrngInfo field, among other fields.)

Any ideas?

Russ

 

[Atomic Shrimp]

This (as I'm sure you're fully appreciating) is why people should never store more than one piece of data per field. It always comes back to bite you later on. Whoever did this needs slapping with a large trout.

I think it can still be done, however, but a few questions:
Are the lengths of ContractNbr, or TrngPrg consistent from one record to the next? (if so, it's going to be an easy string-slicing job using Left(), Right() and Mid())

Does every record contain all three pieces?

Do any vendornames contain 'W' (or numbers and hyphens)?

In any case, my strategy would be to write a function that does this:
--Starting at the end of the string, work backwards until you hit a number or a hyphen - that part is trngprg
--Starting from that point, work backwards until you hit a 'W' - that part is contractnbr
--The rest is vendorname

I'll have a go at writing a function for this later (no time right now).

 

[russi]

Hi, Mike.
1st, I am appreciating the fact, after almost 9 years of this practice.

As to your question regarding length: The 1st filed is of a varying length. The 2nd is of a fixed length. The 3rd is of a varying length.

Russ

 

[Alc]

VendorName is fairly straight forward:

Select Left([I]tablename.[/I]TrngInfo,3) From [I]tablename.[/I]TrngInfo As VendorName;

I'm sure there are many ways of doing this, so I stand ready to have somebody point out the inefficiencies of my method, but here goes.

Loop through the table.
For each record, Left(tablename.TrngInfo,3) will give you the VendorName.
To get the other two values, I would do a search for the first character in each TrngInfo value which isn't a number or a hyphen. You can use some combination of IsNumeric being false and InStr. I'm afraid I'm a bit rusty with the exact syntax but there are plenty of posts on the forum about both (it's where I learned it ).

If you start the Instr search after the "W" (character 5), the first character your search should hit is the start of TrngPrg. Everything to the right of that, including the found letter itself, will be the TrngPrg.

Whatever's left over is the ContractNbr.

Hope that helps.

 

[Atomic Shrimp]

VendorName is fairly straight forward:

Select Left([I]tablename.[/I]TrngInfo,3) From [I]tablename.[/I]TrngInfo As VendorName;

I'm sure there are many ways of doing this, so I stand ready to have somebody point out the inefficiencies of my method, but here goes.

Loop through the table.
For each record, Left(tablename.TrngInfo,3) will give you the VendorName.

Won't that only give you the first three characters of the (variable length) vendor name?

 

[Atomic Shrimp]

OK, here we go... This is a bit quick and dirty, and contains redundant code, but it works on some test data I created, matching your description.

First, create a new module and paste these two functions into it:

Public Function getTrngPrg(TrngInfo As String)
Dim intLoop As Integer
Dim intlastnumber As Integer
intlastnumber = 0
'get the position of the last numeric (or hyphen) character
For intLoop = 1 To Len(TrngInfo)
If InStr("1234567890-", Mid(TrngInfo, intLoop, 1)) > 0 Then
  intlastnumber = intLoop
End If
Next intLoop

'return the portion of the string after intLastNumber
getTrngPrg = Mid(TrngInfo, intlastnumber + 1)

End Function

Public Function getContractNbr(TrngInfo As String)
Dim intLoop As Integer
Dim intLastW As Integer
Dim intlastnumber As Integer
intlastnumber = 0
intLastW = 0

'get the position of the last letter W and the position of the last numeric/hyphen character
For intLoop = 1 To Len(TrngInfo)
If Mid(TrngInfo, intLoop, 1) = "W" Then
  intLastW = intLoop
End If
If InStr("1234567890-", Mid(TrngInfo, intLoop, 1)) > 0 Then
  intlastnumber = intLoop
End If
Next intLoop

getContractNbr = Mid(TrngInfo, intLastW, (intlastnumber - intLastW) + 1)

End Function

Save the module, then try running this query:

SELECT
TrainingTable.TrngInfo, 
gettrngprg([trnginfo]) AS Expr1, 
getContractNbr([trnginfo]) AS Expr2, 
Left([trnginfo],Len([trnginfo])-(Len(getContractNbr([trnginfo]))+Len(gettrngprg([trnginfo])))) AS Expr3 
FROM TrainingTable;

How it works:
The function getTrngPrg looks for the last numeric or hyphen character in the string you pass it, then returns the end bit of the string, starting at that position.

The function getContractNbr looks for the last W in the passed string, then returns everything from that position to the position of the last number/hyphen.

EXPR3 (in the query) calculates the sum of the lengths of the other two bits, then lops that off trnginfo, to leave vendorname.

Hope this (works and) helps...

 

[russi]

Thanks, Mike (and Alc).

I will try your method, Mike. Although, I am uncertain as to what will happen in the instances where there is a 'W' in either the VendorName or TrngPrg fields.

I'll let you know. (As probably with you, i am juggling many balls in the air right now.)

Russ

 

[Atomic Shrimp]

Argh!
It's going to go wrong if there is one in the TrngPrg, and since vendorname is dependent on that, it will go wrong there too.

I'll have to revise the code to look for the first number/hyphen (then back up one space from there) instead of the last W...

You're sure there are no numbers (i.e. 'One2One Training PLC') in the vendor name?

 

[russi]

Mike,

1st, thank you for your time.
2nd, there are no numbers in the vendor's name.

But, is there perhaps a 'simpler method'? There is also a table, Vendor. Which has a field called VendorName. (Actually, this is originally where the TrngInfo field in the other table gets that part of the value from.)

Can we have a query with the 2 tables and selecting from the TrngInfo field, those records with criteria, such as: like [Vendor].[VendorName]*
Then, I could create a Make Table query with the client's IDnumber and the [Vendor].[VendorName] field?

I truly am sorry for any confusion on my end in describing the problem.

Russ

 

[Atomic Shrimp]

Hi there

If all the original fields existed separately elsewhere, then it would be worth going looking for them.

Anyway... I have a different, simpler approach...

First, two multi-purpose functions - these are a bit like the InStr function, except that they search for any of a collection of characters, and they return the position of the first or last instance in the passed string.

Public Function FindLastInstance(SearchWithinString, SearchFor As String) As Integer
Dim intLoop As Integer
FindLastInstance = 0
'get the position of the last numeric (or hyphen) character
For intLoop = 1 To Len(SearchWithinString)
If InStr(SearchFor, Mid(SearchWithinString, intLoop, 1)) > 0 Then
  FindLastInstance = intLoop
End If
Next intLoop

End Function

Public Function FindFirstInstance(SearchWithinString, SearchFor As String) As Integer
Dim intLoop As Integer
FindFirstInstance = 0
'get the position of the last numeric (or hyphen) character
For intLoop = Len(SearchWithinString) To 1 Step -1
If InStr(SearchFor, Mid(SearchWithinString, intLoop, 1)) > 0 Then
  FindFirstInstance = intLoop
End If
Next intLoop

End Function

Then the query...

SELECT TrainingTable.TrngInfo, 
Left([trnginfo],findfirstinstance([trnginfo],"0123456789-")-2) AS VendorName, 
Mid([trnginfo],findfirstinstance([trnginfo],"0123456789-")-1,(findlastinstance([trnginfo],"0123456789-")-(findfirstinstance([trnginfo],"0123456789-")-2))) AS ContractNbr, 
Right([trnginfo],Len([trnginfo])-findlastinstance([trnginfo],"0123456789-")) AS TrngPrg
FROM TrainingTable;

How it works this time...

Find the position of the first numeric or hyphen character, back up one space (to jump over the W) and that's the cutoff point between the first and second items

Find the position of the last numeric or hyphen character - after that is the cutoff point between the second and third items.

The rest is just string slicing (admittedly a bit messy)

Tested with W in vendor name and training program and it works fine... for me - query results come out thusly:

TrngInfo			VendorName		ContractNbr	TrngPrg
Mr BeanPieW-1--32-3banana	Mr BeanPie		W-1--32-3	banana
JimbobW11676-1applepie		Jimbob			W11676-1	applepie
Frankie McDonkeyW1134-xyz	Frankie McDonkey	W1134-		xyz
Willy WonkaW9879847325--wibble	Willy Wonka		W9879847325--	wibble

Let me know how you get on - and no need to apologise... keeps the little grey cells busy.