How to calculate number of rows in field? (1 Viewer)

yunhsuan

Member
Local time
Tomorrow, 06:47
Joined
Sep 10, 2021
Messages
52
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

Enthusiastic Amateur
Local time
Today, 23:47
Joined
Sep 21, 2011
Messages
10,544
Can't you calculate as you construct it?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:47
Joined
Feb 28, 2001
Messages
22,789
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.

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.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:47
Joined
Feb 19, 2013
Messages
14,364
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

I’m here to help
Staff member
Local time
Today, 15:47
Joined
Oct 29, 2018
Messages
18,972
Can someone tell me how to calculate number of rows via vb?
Hi. You could also try:
Code:
1 + Len([FieldName]) - Len(Replace([FieldName], Chr(13) & Chr(10), ""))
Hope that helps...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:47
Joined
May 7, 2009
Messages
16,394
Code:
Len([fieldName]) - Len(Replace$([fieldName], Chr(13) & Chr(10), "")) - 1
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:47
Joined
May 7, 2009
Messages
16,394
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:47
Joined
Feb 19, 2002
Messages
36,296
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.
 

Users who are viewing this thread

Top Bottom