Count Postcodes Where Name Is Split

[DMT Dave]

MOrning all, this is something i shoild be able to do in my sleep but results are wrong



So i have 15 records for a Name (DelTo), in those 15 records there are 7 btanches where the 2nd word of the DelTo Name is different such as

Field 1 Dave Here / PostCode Field ABC (6 records)
Field 1 Dave There / PostCode Field DEF (5 records)
Field 1 Dave Somewhere / PostCode Field GHI (4 records)

etc...
my plan was to split field 1 to get first word (Dave) then count how many postcde are Like Dave

The first SQL result is 15 Not gropued (correct amount of records)

The second SQL result is 2 when i know there are 7 different postcodes

is it my "LIKE" operator that is wrong ? but works Not Gropued for total records ?

Immediate window looks correct but Obvs not

Dim strFullName () as String, strFirstWord as String, strSQL as String
Dim intPCQty as Integer
Dim dtShipWeek As Date
Dim rs as DAO.Recordset


dtShipWeek = Forms!frmRoutes!txtShipmentDate


strFullName = Split(Me.DelTo)
strFirstWord = strFullName(0)
  
'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, 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
    Do While Not rs.EOF
        intPCQty = rs!PostcodeCount
        rs.MoveNext
    Loop
End With


Debug.Print strSQL & vbCrLf & _
            intPCQty


Stop

[Welshgasman]

Could be spaces causing the issue?
Use Trim() to get rid of any extra ones.
You only need Like strFirstWord & "*" if you want all the Daves.

Again walk your code, what exactly is in strFirstWord ? What is it's length?

[orange]

Dave,
I agree with Paul. Also,How about showing us 2 or 3 records of your tblEdit2 so we can see sample data?

[DMT Dave]

Hi orange, Paul, tried changing to Pauls suggestion and getting syntax error, i always though an extra Asterix is required to close the criteria ?

So this is the result after trying to change to Pauls

I will need to add some dummy records to show if that was needed as what i have is real data, I will replace the strFirstWord with my user name

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


Debug.Print strFirstWord


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


Set rs = CurrentDb.OpenRecordset(strSQL)
With rs
    Do While Not rs.EOF
        intPCQty = rs!PostcodeCount
        rs.MoveNext
    Loop
End With


Debug.Print strSQL & vbCrLf & _
            intPCQty


Stop

Immediate Window

DMTDave
SELECT tblEdit2.PostCode, COUNT(*) AS PostcodeCount FROM tblEdit2 WHERE ShipmentDate = #08/22/2025# AND DelTo Like DMTDave* GROUP BY tblEdit2.PostCode, tblEdit2.DelTo

[DMT Dave]

but definite 15 records for 7 branches so intPCQty should be 7 not 2 as per original post

[orange]

Dave, Try adjusting your sql as in this sample--note single quotes -- to remove syntax error

Sub daveAug24()
    Dim dtShipWeek As Date
    Dim strFirstWord As String
    strFirstWord = "DAVE"
    Dim strsql As String
    dtShipWeek = Date
    strsql = "SELECT tblEdit2.PostCode, COUNT(*) AS PostcodeCount" _
        & " FROM tblEdit2" _
        & " WHERE ShipmentDate = #" & Format(dtShipWeek, "mm/dd/yyyy") & "#" _
        & " AND DelTo Like '" & strFirstWord & "*'" _
        & " GROUP BY tblEdit2.PostCode, tblEdit2.DelTo"
    Debug.Print strsql
End Sub

Immediate window:

SELECT tblEdit2.PostCode, COUNT(*) AS PostcodeCount FROM tblEdit2 WHERE ShipmentDate = #08-24-2025# AND DelTo Like 'DAVE*' GROUP BY tblEdit2.PostCode, tblEdit2.DelTo

[orange]

Dave, Tell us/show us what postCode branches are and their representation.

[Welshgasman]

No single quotes around the text

[DMT Dave]

hi guyts sorry, just come back to this, i think what you are referring to is what i would call an absolute return

If so, i have done this elsewhere Such as WHERE DeliveryDay = 'Monday'' (when is needs to return Monday)

Ill look agian now and compare with your suggestions

[DMT Dave]

Ahh sorry, just realized your single quote string delimiter

Whenever i have used a Like operator, i have never added single quotes

always used LIKE ""*" & string & "***

i will adjust and come back

[Welshgasman]

Fair point, single or double will work, but you have neither?

SELECT tblFoods.FoodName
FROM tblFoods
WHERE (((tblFoods.FoodName) Like "Giannis*"));

tsql = "SELECT tblFoods.FoodName FROM tblFoods WHERE tblFoods.FoodName Like '" & [Tempvars]![tt].[value] & "*'"
? tsql
SELECT tblFoods.FoodName FROM tblFoods WHERE tblFoods.FoodName Like 'Giannis*'

[DMT Dave]

@WGM the only reason i haven't used Single quote delimiter in a LIKE operator is if it was written in a select query, then copied the Query SQL, always just add the extra quotes in to compensate for the start and end quotes otherwise a gap appears around Asterix

but again, i guess if you are starting with the absolute word and looking for anything after it OR before and after and the absolute word is in the middle of string

adding the string delimiters (oranges) does show still 2 postcodes in the imm window so i think i need to get my glasses on and check correct data to return ie walk the other 5 postcodes

[Welshgasman]

I amended #11

[orange]

Dave,
Show us a sample record that shows what the/a record looks like. What is it with the Branches?

Tell me about your 15 total records and then the
"but definite 15 records for 7 branches so intPCQty should be 7 not 2 as per original post"
and how this relates
"Field 1 Dave Here / PostCode Field ABC (6 records)
Field 1 Dave There / PostCode Field DEF (5 records)
Field 1 Dave Somewhere / PostCode Field GHI (4 records)"

Here is my test table to help with the question

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-23	Dave there
7	ghi	2025-08-03	Dave somewhere

But I'm not following branches or some breakdown.

Need some data with explicit description of your issue.

[June7]

Apostrophe delimiters are fine if your data does not have apostrophes (think T'Pol or D'jon). Doubled quotes can handle that situation. Now if data has quote marks, that's another matter.