VBA to split string then increment

[tmyers]

I have been reading on the Split function within Access, but having a hard time fully grasping how to correctly write it. I have read things like:

MS Access: Split Function

This MSAccess tutorial explains how to use the Access Split function with syntax and examples. The Microsoft Access Split function will split a string into substrings based on a delimiter. The result is returned as an array of substrings.

www.techonthenet.com

&

How to Select Everything Before/After a Certain Character in MySQL – SUBSTRING_INDEX()

In tblJobDetails, I have a field QuoteNum. QuoteNum contains values in a specific format such as Q12345-1. I have been trying to correctly use Split to delimit at "-" and then increase by one. So after a on click, the QuoteNum would become Q12345-2.

What is the correct way to split the QuoteNum into two values as an array then increment only the second portion of the number, then put them back together then set the newly created QuoteNum as the new value?

 

[theDBguy]

Maybe something like:

strArray = Split(QuoteNum)
strArray(1)=strArray(1)+1
NewQuoteNum = Join(strArray)

(untested)

 

[pbaldy]

How about:

?split("Q12345-1","-")(0) & "-" & split("Q12345-1","-")(1)+1
Q12345-2

 

[tmyers]

Maybe something like:

strArray = Split(QuoteNum)
strArray(1)=strArray(1)+1
NewQuoteNum = Join(strArray)

(untested)

Do I have to fully define QuoteNum in the split? Such as tblJobDetails.QuoteNum?

Edit:
I did. But I am getting "expected array" on the second line. I wrote it as such:

Dim strarray As String
Dim NewQuoteNum As String
Dim QuoteNum As String
    QuoteNum = Forms.jobquote.QuoteNum

    strarray = Split(QuoteNum, "-")
    strarray(1) = strarray(1) + 1
    NewQuoteNum = Join(strarray)

 

[plog]

The correct way is to not store it as you have done. If "Q12345-2" represents multiple discrete values (before and after the dash), then you need to use multiple fields to store that data, with the correct data type for the data it holds. In a database each piece of data goes into its own field, not crammed in with other pieces.

You do that and incrementing a numeric field that contains just the value 2 becomes trivial.

 

[tmyers]

The correct way is to not store it as you have done. If "Q12345-2" represents multiple discrete values (before and after the dash), then you need to use multiple fields to store that data, with the correct data type for the data it holds. In a database each piece of data goes into its own field, not crammed in with other pieces.

You do that and incrementing a numeric field that contains just the value 2 becomes trivial.

There is no quote unquote hidden value in the number. It is just our numbering system for quotes. All numbers start with Q followed by 5 digits. Due to how I am handling revisions in the app, I added the "-1" to keep track of each revision. The rest of the number honestly don't mean anything special other than a unique identifier for that particular quote.

Edit:
I think I misunderstood what you said. Are you saying to put the Q12345 in one field, then the "-1" in a separate field then use a query to combine the two?

 

[plog]

Yes, every discrete piece of data in its own field. If Q represents something, its own field; if 12345 represents something, its own field; if 1 represents something, its own field. Each piece of data that represent something gets its own field.

Then when you want to display a value to a user, either in a form or a report, you combine them as needed. Data presentation doesn't dictate data storage. Data does.

 

[pbaldy]

Plog's point is a good one. To clarify, you'd store 1 in the second field, not -1. You'd add the dash when you concatenate them together for the user.

 

[tmyers]

I see. Only thing Q represents is "Quote", but every number starts what it and there are no other "types" such as invoices etc. It is just the numbering system that was in place long before me.

I will try both methods and see where it gets me.

 

[Galaxiom]

Only thing Q represents is "Quote"

Then the Q would not be stored at all but also concatenated for display only. Definitely store the quote number and revision number as two separate integer fields.

 

[pbaldy]

Then the Q would not be stored at all but also concatenated for display only. Definitely store the quote number and revision number as two separate integer fields.

I agree, though I could make a case for storing the Q (separately). Too many times in my experience something that was never going to change...changed. Management will decide they're going to have regular quotes (Q) and super-duper special quotes (SQ). I might have a field for the Q, make "Q" that the default value and include that field in the concatenation now. That way if it ever does change, you're ready.

 

[CJ_London]

to answer this question

I did. But I am getting "expected array" on the second line. I wrote it as such:
Code:Copy to clipboard
Dim strarray As String

you should dim it as
Dim strarray() As String

 

[arnelgp]

creating a 2 field for your quote number breaks the normalization rule (repeated values on each row).
for your Original idea to work, you create a user-defined function.
pass to the function the "sample" quote string, the tablename and the fieldname where to validate the "sample" string.

'arnelgp
Public Function fncNextSerial(ByVal strSerial As String, _
            ByVal strTable As String, _
            ByVal strField As String) As String
Dim strTextPart As String
Dim strNumPart As String
Dim strDelim As String
Dim intLen As Integer
intLen = Len(strSerial)
If intLen = 0 Then Exit Function
'get only the text part, delimiter and numeric part from the serial
Call fncSplit(strSerial, strTextPart, strNumPart, strDelim)
'get the maximum from the table
strReturn = DMax("[" & strField & "]", "[" & strTable & "]", "[" & strField & "] Like '" & strTextPart & "*'") & vbNullString
If Len(strReturn) = 0 Then
    strReturn = strTextPart & strDelim & "1"
Else
    strTextPart = vbNullString
    strNumPart = vbNullString
    strDelim = vbNullString
    Call fncSplit(strReturn, strTextPart, strNumPart, strDelim)
    strReturn = strTextPart & strDelim & (Val(strNumPart) + 1)
End If
fncNextSerial = strReturn
End Function


'arnelgp
'helper function
Private Function fncSplit(ByVal strText As String, ByRef TextPart As String, ByRef NumericPart As String, ByRef Delimiter As String)
Dim intLen As Integer
Dim i As Integer
Dim strChar As String
intLen = Len(strText)
i = 1
Do Until i > intLen
    strChar = Mid(strText, i, 1)
    Select Case True
    Case IsNumeric(strChar)
        NumericPart = NumericPart & strChar
    Case strChar Like "[a-z]"
        TextPart = TextPart & strChar
    Case Else
        Delimiter = Delimiter & strChar
    End Select
    i = i + 1
Loop
End Function

 

[Galaxiom]

creating a 2 field for your quote number breaks the normalization rule (repeated values on each row).

Incorrect. Read Plog's posts

 

[arnelgp]

Incorrect. Read Plog's posts

it's correct. for your reading glass:

Database normalization description - Microsoft 365 Apps

Describe the method to normalize the database and gives several alternatives to normalize forms. You need to master the database principles to understand them or you can follow the steps listed in the article.

docs.microsoft.com

if you a million of "textpart" (ie "Quotation"), then all million field will have same "Quotation" value.
what a waste of storage.

 

[Galaxiom]

it's correct. for your reading glass:

Database normalization description - Microsoft 365 Apps

Describe the method to normalize the database and gives several alternatives to normalize forms. You need to master the database principles to understand them or you can follow the steps listed in the article.

docs.microsoft.com

No it isn't. You are misunderstanding normalization. In this structure, the versions of quotes are in fact different quotes that share part of their name. As Plog originally said, and Paul and I concurred, the discrete pieces of data are better kept apart. It has nothing to do with normalization.

Complete normalization would have a single quote number with another table many to many related the QuoteItems to indicate which versions of the quote they were included in. This would prevent the repetition of the identical QuoteItems that are in multiple versions of the quote. Such a structure would require a very complex form.

 

[arnelgp]

Such a structure would require a very complex form.

why then make it harder. the OP obviously is a novice in MSA. if he is not, he would have come up of a solution without going here.

 

[Galaxiom]

why then make it harder. the OP obviously is a novice in MSA. if he is not, he would have come up of a solution without going here.

I'm not suggesting they do it that way. That is why I said it would require a very complex form.

I was simply pointing out what could actually be normalized as an aside to my comment that you were misunderstanding normalization with your incorrect claim that having the base quote number on multiple records was a normalization issue.

 

[arnelgp]

ok.
my suggestion is use 1 field.
with the rest suggesting 2 field + another master table (with at least 2 fields).
both will work.

 

[pbaldy]

my suggestion is use 1 field.

You're suggesting 1 field to store 2 distinct pieces of data, quote number and revision number. We're suggesting storing them separately. Much the same as I'd suggest storing first and last names separately. It's easier to put them together than break them apart.