How to calculate number of rows in field?

[yunhsuan]

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.

 

[Gasman]

Can't you calculate as you construct it?

 

[The_Doc_Man]

First, the technical answer:

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.

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.

 

[CJ_London]

Agree with docs comments regards data structure but as an alternative to looping you could split the string and get the ubound value

ubound(split(myfield,chr(10)))+1

 

[theDBguy]

Can someone tell me how to calculate number of rows via vb?

Hi. You could also try:

1 + Len([FieldName]) - Len(Replace([FieldName], Chr(13) & Chr(10), ""))

Hope that helps...

 

[arnelgp]

Len([fieldName]) - Len(Replace$([fieldName], Chr(13) & Chr(10), "")) - 1

 

[arnelgp]

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":

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

 

[yunhsuan]

Thank you! It works successfully in my database.

 

[theDBguy]

Thank you! It works successfully in my database.

Hi. Glad we could assist. Good luck with your project.

 

[Pat Hartman]

Thank you! It works successfully in my database.

So you're going to ignore the expert advice to resolve the problem correctly and use the band aid instead? OK. Good luck.

 

[IbrBadri]

Thank you! It works successfully in my database.

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.

 

[Pat Hartman]

Good luck with that.
Here's a working example with two different types of custom sequence numbers:

Custom Sequence Numbers

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.