Sanguineus
New member
- Local time
- Today, 10:26
- Joined
- May 5, 2020
- Messages
- 13
Hi All,
Really stuck on something that after a couple of daysis doing nothing more than giving me a headache.
I have been asked to look after/maintain as DB from a colleague who is moving on. I am going through updating code, forms and so on, but have come cross a problem I just can't crack.
For some reason I just can't fathom it was decided to take 3 inputs from a form and concatonate them into a string and then storethat in a table. (really annoying I know - changing this is on my to do list, but its a few months away as I also have to change some forms and some reporting outputs) for example: Input1-Input2-Input3
To give me time to make the adjustments I am building a few "admin" forms that will allow a couple of "select" colleagues the ability to make some "simple" adjustments.
What I am working on is a form that will allow the above users to "edit" the values in the controls (i.e. spelling and termiology corrections). I have sorted the part that goes through the selectable values and updates them, but now want to go through the historic records and make the changes there as well. If the recrds werestored correcltythis wouldn't be an issue; but due to the concacanation its rather difficult.
I have this udf:
which I can use in a select statement to successfully split the field
The bit I can't get my head around is how do I then update one of the sections in the main table. I'm sure the answer is 'releatively' simple but can't quite get there
Really stuck on something that after a couple of daysis doing nothing more than giving me a headache.
I have been asked to look after/maintain as DB from a colleague who is moving on. I am going through updating code, forms and so on, but have come cross a problem I just can't crack.
For some reason I just can't fathom it was decided to take 3 inputs from a form and concatonate them into a string and then storethat in a table. (really annoying I know - changing this is on my to do list, but its a few months away as I also have to change some forms and some reporting outputs) for example: Input1-Input2-Input3
To give me time to make the adjustments I am building a few "admin" forms that will allow a couple of "select" colleagues the ability to make some "simple" adjustments.
What I am working on is a form that will allow the above users to "edit" the values in the controls (i.e. spelling and termiology corrections). I have sorted the part that goes through the selectable values and updates them, but now want to go through the historic records and make the changes there as well. If the recrds werestored correcltythis wouldn't be an issue; but due to the concacanation its rather difficult.
I have this udf:
Code:
Public Function SplitString(str As String, delimiter As String, count As Integer) As String
Dim strArr() As String
strArr = Split(str, delimiter, count + 1)
count = count - 1 'zero-based
If UBound(strArr) >= count Then
SplitString = strArr(count)
End If
End Function
which I can use in a select statement to successfully split the field
Code:
SELECT SplitString(FieldName,"-",1) AS SectionOne,
SplitString(FieldName,"-",2) AS SectionTwo,
SplitString(FieldName,"-",3) AS SectionThree
FROM TableOne
The bit I can't get my head around is how do I then update one of the sections in the main table. I'm sure the answer is 'releatively' simple but can't quite get there