Count Postcodes Where Name Is Split

[DMT Dave]

Morning all, @WGM, orange and June7, apologies for the image, i never like attaching images filled with paint, always looks like a graffiti artist

this is a form Datasheet view, for data protection, i have managed to rule out anything sensitive so i have painted over:

Full company names (assure you the strFirstWord is the same for all 15 records), but managed to keep partial postcode there also closed up the town field

as you can see there are 15 records, 7 different postcodes



The target for me is in the recordset is to find out if postcode is > 1, whilst my own approach and orange approach does show 2 intPCQty as you can see there is 7 looking at the end part of postcode

will also take a look at June7 post,

I am not at PC much today but will come back to this, as always, thank you indeed

[Welshgasman]

Use this to obscure the data on a copy which you can upload. Just enough to see the issue.

Keep the link for the next time.

I would really like to know what is confidential about a postcode on it's own?

[Welshgasman]

Saying that, this works for me in one of my queries.

SELECT tblFoods.FoodID, tblFoods.FoodName
FROM tblFoods
WHERE (((tblFoods.FoodName) Like "*" & [Tempvars]![Food] & "*"));

However that was created in the query window and not in code, where you would need the text delimiters, or so I believe.

[jojowhite]

you may also like to test this:

Dim strFullName As Variant, strFirstWord As String, strSQL As String
Dim intPCQty As Integer
Dim dtShipWeek As Date
Dim rs As DAO.Recordset
Dim i As Integer


dtShipWeek = Forms!frmRoutes!txtShipmentDate




strFullName = Split(Me.DelTo)
strFirstWord = Trim$(strFullName(0))


i = Len(strFirstWord)


'strSQL = "SELECT COUNT(*) AS PostcodeCount" _
             & " FROM tblEdit2" _
             & " WHERE ShipmentDate = #" & Format(dtShipWeek, "mm/dd/yyyy") & "#" _
             & " AND DelTo Like ""*" & strFirstWord & "*"""
' Above strSQL count is 15 Correct




'Below strSQL count is 2, grouped to count how many branches with different postcodes, should be 7 in this case




strSQL = "SELECT Left(DelTo, " & i & ") As DelivTo, tblEdit2.PostCode, COUNT(*) AS PostcodeCount" _
             & " FROM tblEdit2" _
             & " WHERE ShipmentDate = #" & Format(dtShipWeek, "mm/dd/yyyy") & "#" _
             & " AND Left(DelTo, " & i & ") = '" & strFirstWord & "'" _
             & " GROUP BY Left(DelTo, " & i & "), tblEdit2.PostCode"




Set rs = CurrentDb.OpenRecordset(strSQL)
With rs
    If Not (.BOF And .EOF) Then
        .MoveFirst
    End If
    Do While Not .EOF
        intPCQty = intPCQty + rs!PostcodeCount
        rs.MoveNext
    Loop
    .Close
End With




Debug.Print strSQL & vbCrLf & _
            intPCQty




Stop

[DMT Dave]

@WGM, thank you, will try that link later to obsecure data, sounds handy

@jojowhite, thank you for your suggestion, mine is still showing 15 records (total records) and not 7 postcodes for those 15 records

strFirstWord is correct and Len is correct @ 9

SELECT Left(DelTo, 9) As DelivTo, tblEdit2.PostCode, COUNT(*) AS PostcodeCount FROM tblEdit2 WHERE ShipmentDate = #08/22/2025# AND Left(DelTo, 9) = 'CorrectName' GROUP BY Left(DelTo, 9), tblEdit2.PostCode
15

[orange]

Dave,

You mentioned 15 postcodes. You also mention "The target for me is in the recordset is to find out if postcode is > 1", and also "grouped to count how many branches with different postcodes".

I am not familiar with UK postcodes, but I think you are referring to incodes based on this diagram.


I have modified my test data as follows

recid	PostCode	shipmentDate	delTo
1	abc	2025-08-03	Dave here
2	abc	2025-08-03	Dave here
3	abc	2025-08-03	Dave here
4	abc	2025-08-03	Dave there
5	def	2025-08-03	Dave there
6	def	2025-08-03	Dave there
7	ghi	2025-08-03	Dave somewhere
8	abc	2025-08-03	Dave here
9	abc	2025-08-03	Dave here
10	abc	2025-08-03	Dave here
11	def	2025-08-03	Dave there
12	def	2025-08-03	Dave there
14	ghi	2025-08-03	Dave somewhere
15	ghi	2025-08-03	Dave somewhere
16	ghi	2025-08-03	Dave somewhere

I have no idea where your postcode >1 shows up in your SQL or code?? You hid the outcodes so I can only use my abc, def, ghi to test.

Revised sql result showing branches/incodes within postcode

branches
PostcodeCount

abc  
7

def  
4

ghi  
4

This is the sql used to get the 7 branches/incodes within postcode abc.

SELECT
    tblEdit2.PostCode AS branches,
COUNT(*) AS PostcodeCount
FROM
    tblEdit2
WHERE
    DelTo LIKE'DAVE'& "*"
GROUPBY
    tblEdit2.PostCode

My version for testing your vba code.

Sub DaveAug2025()
    Dim strFullName As Variant, strFirstWord As String, strSQL As String
    Dim intPCQty As Integer
    Dim dtShipWeek As Date
    Dim rs As DAO.Recordset
    Dim i As Integer
    Dim result As String

    dtShipWeek = #8/3/2025#  'Forms!frmRoutes!txtShipmentDate

    'strFullName = Split(Me.DelTo)
    strFirstWord = "DAVE" 'Trim$(strFullName(0))


    i = Len(strFirstWord)


    'strSQL = "SELECT COUNT(*) AS PostcodeCount" _
     & " FROM tblEdit2" _
     & " WHERE ShipmentDate = #" & Format(dtShipWeek, "mm/dd/yyyy") & "#" _
     & " AND DelTo Like ""*" & strFirstWord & "*"""
    ' Above strSQL count is 15 Correct

    'Below strSQL count is 2, grouped to count how many branches with different postcodes, should be 7 in this case

    strSQL = "SELECT  tblEdit2.PostCode as Branches, COUNT(*) AS PostcodeCount FROM tblEdit2" _
        & " WHERE  DelTo LIKE 'DAVE' & '*'" _
        & " Group BY  tblEdit2.PostCode "

    Set rs = CurrentDb.OpenRecordset(strSQL)
    With rs
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Debug.Print "PostCode Branches/incodes within"
        Do While Not .EOF
            result = !branches & "  " & intPCQty + rs!PostcodeCount
            Debug.Print result
            rs.MoveNext
        Loop
        .Close
    End With

    Debug.Print strSQL
 
    'Stop
End Sub

Result:

 PostCode Branches/incodes within
abc    7
def    4
ghi    4

Hope this is helpful.

[DMT Dave]

Yes, thank you guys, a mixture of all of your suggestions combined

added prefix and intTotal to give me the answer as well as all 7 displayed

 
strSQL = "SELECT tblEdit2.PostCode as Branches, COUNT(*) AS PostcodeCount FROM tblEdit2" _          
          & " WHERE ShipmentDate = #" & Format(dtShipWeek, "mm/dd/yyyy") & "#" _
          & " AND DelTo Like '" & strFirstWord & "*'" _
          & " GROUP BY tblEdit2.PostCode"


    Set rs = CurrentDb.OpenRecordset(strSQL)
    With rs
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Debug.Print "PostCode Branches/incodes within"
        Do While Not .EOF
            strResult = !branches & "  " & intPCQty + rs!PostcodeCount
            Debug.Print strResult
            rs.MoveNext
        Loop
        intTotal = rs.RecordCount
        .Close
    End With


    Debug.Print "You Have Returned " & intTotal & " " & "Postcodes" & vbCrLf & _
                 strSQL
 
    Stop

PostCode Branches/incodes within
CH5   2
CW2   3
DE14  1
GL51  3
LN2  2
ST16  2
SY1  2
You Have Returned 7 Postcodes
SELECT tblEdit2.PostCode as Branches, COUNT(*) AS PostcodeCount FROM tblEdit2 WHERE ShipmentDate = #08/22/2025# AND DelTo Like 'DAVE*' GROUP BY tblEdit2.PostCode

As always, thank you all so much indeed........

wish you all a good day wherever you are in the world