Trying to count records in a field CUST. These records will be separated by a comma and sometimes the field will be blank.

i.e.
CUST REF
80908766, 723767676, 787987987
23432434, 342343432

I want to show that there is 3 entries in that field for the first record and 2 entries in that field for the second record.

Any suggestions?

Trying to count records in a field CUST. These records will be separated by a comma and sometimes the field will be blank.

i.e.
CUST REF
80908766, 723767676, 787987987
23432434, 342343432

I want to show that there is 3 entries in that field for the first record and 2 entries in that field for the second record.

Any suggestions?

I am not sure of any way (easy or otherwise) that this task can be done using Queries. It can be done with a VB Loop that counts commas and verifies that there is content between each comma and after the last.

A far more important point may be that if this is a requirement, then the design for the CustRef Table may not be the most efficient. If the Table had one entry for each CustRef that also included the Cust ID, then a simple query that selects a Count of all records Grouped By Cust ID would provice what you are looking for without needing to use any VB Code.

Let us know if you have any additional questions.

Try this expression in a blank query field that has your table/query as the record source:

MyCount: IIf(IsNull([CUST REF]),0,Len([CUST REF])-Len(Replace([CUST REF],",",""))+1)

i think this will work for the Lines, coz i realy couldnt; fine the Chr(?) value for a comma,, this takes the new lines chr(3) whicjh can be sometimes used for multi-lining a masgbox.. the idea here is to count the separe packets of data and store this counter in a variable or use it to determin how many records to add.. use a FOR loope so you can loop n times while adding the data by reading the contents of the textbox or memo once each time you find the separater

Public Function CountLines(InString As String) As Integer
Dim Counter As Integer
CountLines = 1
For Counter = 1 To Len(InString)
If Mid(InString, Counter, 1) = Chr(13) Then
CountLines = CountLines + 1
End If
Next
End Function

i think this will work for the Lines, coz i realy couldnt; fine the Chr(?) value for a comma,, this takes the new lines chr(3) whicjh can be sometimes used for multi-lining a masgbox.. the idea here is to count the separe packets of data and store this counter in a variable or use it to determin how many records to add.. use a FOR loope so you can loop n times while adding the data by reading the contents of the textbox or memo once each time you find the separater

Public Function CountLines(InString As String) As Integer
Dim Counter As Integer
CountLines = 1
For Counter = 1 To Len(InString)
If Mid(InString, Counter, 1) = Chr(13) Then
CountLines = CountLines + 1
End If
Next
End Function



In case anyone is interested, the Chr() value for a comma is 44.

Not good database design in the least but this would work too:


Function GetCountInField(strValue As String)
Dim varSplit As Variant

If len(strValue)= 0 Then
GetCountInField = 0
Else
varSplit = Split(strValue, ",")
GetCountInField = UBound(varSplit) + 1
End If
(air code - untested)