I made a report in access with "Can grow" property in the Detail section. One field in detail section shows one or several invoice number.
EX:
TB-25886600
TB-25886601
TB-25886602
Ctrl+Enter is used to change to new line in this field. I want to get the number of rows in this field; for example, the number of rows in the example above is 3.
Can someone tell me how to calculate number of rows via vb?
Thanks for any suggestion.
A loop would be required in which you do an InStr starting in column 1 to find the vbCRLF sequence position. Then increment your counter and start the NEXT InStr search for vbCRLF by adding 2 to the place you got back from the previous InStr, and so on, until you can't find any more vbCRLF sequences. NOTE that the answer CAN be 0 and that is therefore a valid answer AND the proper test to end the loop.
The core of the loop MIGHT look like this, though you would need to use your own variables.
Code:
X = 1
Y = 0
Do While X > 0
X = InStr( X, TheString, vbCRLF )
If X > 0 Then
Y = Y + 1
End If
Loop
Now the TACTICAL answer...
From your description, it is possible that we are looking at the wrong question. Storing invoice data as multiple rows in a single control of a detail section suggests badly non-normalized data, which will lead to confusion and technical difficulties down the line. FURTHER, how did those multiple rows get there? If that is a detail section in a report, you cannot enter data that way anyway. It would have to come from a form data entry or a strange query or a strange bit of VBA. And if that is a normal detail section, are you sure that it is not in fact MULTIPLE detail sections stacked one on top of another? I see several procedural "red flags" in what you described.
Tell us in words what problem you are trying to solve INDEPENDENT of how it appears on forms or reports.
you can even have a function and it will count (regardless of how many Chr(13)+Chr(10) you may have on the field.
it will only count the "invoices":
Code:
Public Function invoiceCount(ByVal s As String) As Integer
Dim v, itm
Dim i As Integer
With CreateObject("vbscript.regexp")
.Global = True
.IgnoreCase = True
.pattern = "\w+"
Set v = .Execute(s)
i = v.Count
End With
invoiceCount = i
End Function
As I am searching for an increment VBA counter solution, preferably @yunhsuan to mention which one solves his issue, and mark the thread as solved, so any ones searching can easily follow it.
Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below
www.access-programmers.co.uk
Keep in mind that the sequence numbers are generated when the records are added. NOT after the fact. That is a different problem.