'Like' query not working as expected

[RubberChicken]

Hi all

I have a like statement in my VBA that is not returning all the possible results.

There are 3 segments AMS, AMS Red & AMS Blue but the code below is only returning values for AMS.
Any help really appreciated!

Set rst = dbs.OpenRecordset("SELECT tblSummaryValues.MonthEndDate, " _    
    & "Sum([Current]+[30]+[60]+[90]+[120]+[180]) AS [Total Outstanding], " _
    & "Sum(tblSummaryValues.Current) AS SumOfCurrent, Sum(tblSummaryValues.[30]) AS SumOf30, " _
    & "Sum(tblSummaryValues.[60]) AS SumOf60, Sum(tblSummaryValues.[90]) AS SumOf90, " _
    & "Sum(tblSummaryValues.[120]) AS SumOf120, Sum(tblSummaryValues.[180]) AS SumOf180, " _
    & "Sum([30]+[60]+[90]+[120]+[180]) AS Collectable " _
    & "FROM tblSummaryValues " _
    & "GROUP BY tblSummaryValues.MonthEndDate, tblSummaryValues.Segment " _
    & "HAVING (((tblSummaryValues.MonthEndDate)=#" & sDate & "#) " _
    & "AND ((tblSummaryValues.Segment) like 'AMS*'));")

 

[CJ_London]

not quite sure what your query is supposed to be doing, you are grouping by segment, but not selecting it, and then applying criteria to the grouped values (in the HAVING part), would be better in the WHERE part. So how do you know it is only returning AMS since you never display it?

Have you tried running the query as a query to see what you get there? Also always better to assign your sql to a string and then the string to openrecordset - then you can insert a debug.print sqlstr and copy/paste into a new query to see what errors you get.

 

[Cronk]

I wonder if there are red, blue values with month end of sDate. One way to work out why a query is not returning values as expected is to remove/relax some of the criteria.

 

[RubberChicken]

Hi CJ, Cronk
Thanks for responding.

CJ, the code has always preformed as expected. I have been using it for about 2 years without issue. It groups the data and extracts it to Excel. I will post the rest of the code below to see if that makes more sense.

This issue has arisen because I have had to separate AMS into 3 groups now, AMS, AMS Red and AMS Blue. I would like to report the 3 groups as one, which is why I am trying to use the Like. Is there another way to join the 3 groups, with and OR, maybe? I already tried this and it didn't work, but there could be an issue with how I wrote it.

Conk, yes. I checked the tables and there is relevant data for all 3 segments.

Full code relating to this extract...

iRow = iRow + 3
 Do Until Cells(iRow, 1) = dLastPeriodEnd
    Cells(iRow, 1).Activate
    iRow = iRow + 1
Loop
 iRow = iRow + 1
 Cells(iRow, 1).Activate
 ActiveCell.Offset(1).EntireRow.Insert
    
Set rst = dbs.OpenRecordset("SELECT tblSummaryValues.MonthEndDate, " _
    & "Sum([Current]+[30]+[60]+[90]+[120]+[180]) AS [Total Outstanding], " _
    & "Sum(tblSummaryValues.Current) AS SumOfCurrent, Sum(tblSummaryValues.[30]) AS SumOf30, " _
    & "Sum(tblSummaryValues.[60]) AS SumOf60, Sum(tblSummaryValues.[90]) AS SumOf90, " _
    & "Sum(tblSummaryValues.[120]) AS SumOf120, Sum(tblSummaryValues.[180]) AS SumOf180, " _
    & "Sum([30]+[60]+[90]+[120]+[180]) AS Collectable " _
    & "FROM tblSummaryValues " _
    & "GROUP BY tblSummaryValues.MonthEndDate, tblSummaryValues.Segment " _
    & "HAVING (((tblSummaryValues.MonthEndDate)=#" & sDate & "#) " _
    & "AND ((tblSummaryValues.Segment) like 'AMS*'));")
    
 
iCol = 1
 iFld = 0
 For I = cStartColumn To cStartColumn + (rst.Fields.Count - 1)  ' 1 to 9
   
   wks.Cells(iRow, iCol) = rst.Fields(iFld)
   
   If InStr(1, rst.Fields(iFld).Name, "MonthEndDate") > 0 Then
      wks.Cells(iRow, iCol).NumberFormat = "dd/mm/yyyy"
   End If
   
   wks.Cells(iRow, iCol).WrapText = False
   iFld = iFld + 1
    If iCol = 9 Then  '   % Movement
      wks.Cells(iRow, iCol + 1) = (wks.Cells(iRow, iCol) - wks.Cells(iRow - 1, iCol)) / wks.Cells(iRow, iCol)                   '"=(RC[-1]-R[-1]C[-1])/RC[-1]"
   End If
         
   iCol = iCol + 1
 Next
 Range("A" & iRow - 1 & ":J" & iRow - 1).Select
Selection.Copy
Range("A" & iRow & ":J" & iRow).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

 

[CJ_London]

suggest try something like

"SELECT tblSummaryValues.MonthEndDate, " _
....
....
& " FROM tblSummaryValues " _
& " WHERE tblSummaryValues.MonthEndDate=#" & sDate & "# " _
& " AND tblSummaryValues.Segment like 'AMS*'" _
& " GROUP BY tblSummaryValues.MonthEndDate

 

[Cronk]

RC, did you take CJ's advice and paste the recordset's SQL into a query?

CJ, if MonthEndDate is to be included in the Select part of the Group query, you can't use WHERE for it in a condition
It has to be
.... & " FROM tblSummaryValues " _ & " WHERE " _ & " tblSummaryValues.Segment like 'AMS*'" _ & " GROUP BY tblSummaryValues.MonthEndDate HAVING tblSummaryValues.MonthEndDate=#" & sDate & "#"

 

[RubberChicken]

Gents, once again, thank you.

So, I pretty much just used CJ's code as he had it written and it seems to now be applying the Like as the results are exactly as expected.

CJ, would you mind taking a minute to explain how the code you wrote is working and what in my previous code wasn't?

Conk, It seems to be working without the HAVING as per below. I don't know why.

Set rst = dbs.OpenRecordset("SELECT tblSummaryValues.MonthEndDate, " _
    & "Sum([Current]+[30]+[60]+[90]+[120]+[180]) AS [Total Outstanding], " _
    & "Sum(tblSummaryValues.Current) AS SumOfCurrent, Sum(tblSummaryValues.[30]) AS SumOf30, " _
    & "Sum(tblSummaryValues.[60]) AS SumOf60, Sum(tblSummaryValues.[90]) AS SumOf90, " _
    & "Sum(tblSummaryValues.[120]) AS SumOf120, Sum(tblSummaryValues.[180]) AS SumOf180, " _
    & "Sum([30]+[60]+[90]+[120]+[180]) AS Collectable " _
    & "FROM tblSummaryValues " _
    & "WHERE tblSummaryValues.MonthEndDate=#" & sDate & "# " _
    & "AND tblSummaryValues.Segment like 'AMS*'" _
    & "GROUP BY tblSummaryValues.MonthEndDate;")

 

[CJ_London]

consider the way the query is constructed

SELECT
FROM
WHERE
GROUP BY
HAVING

WHERE works on the FROM recordset - as with any ordinary select query whatever is in WHERE does not need to be in SELECT

GROUP BY then groups the SELECT fields limited by the WHERE statement

HAVING then applies criteria based on the grouped data - which is only fields included in the SELECT data

wherever possible, you should use WHERE for your criteria because the GROUP BY will then be working on a smaller dataset and therefore be quicker.

HAVING should really only be used when the criteria is based on a summed/counted etc value - using as per the OP's original query means the whole dataset needs to be loaded and grouped before applying the criteria. e.g.

 SELECT fld1, sum(fid2) as ttl
 FROM my Table
 WHERE fld1 like "abc*"
 GROUP BY fld1
 HAVING sum(fid2)>0

will only return rows where ttl's is positive

written as

 SELECT fld1, sum(fid2) as ttl
 FROM my Table
 WHERE fld1 like "abc*" and sum(fld2)>0
 GROUP BY fld1

will fail because fld2 hasn't been summed yet

written as

 SELECT fld1, sum(fid2) as ttl
 FROM my Table
 GROUP BY fld1
 HAVING fld1 like "abc*" and sum(fid2)>0

will work - but you will have selected all records and grouped them before applying the criteria - makes a big difference to performance on large datasets.

 

[RubberChicken]

Thanks, CJ. That is really appreciated.

Thanks for your input, Cronk.