(Solved) – Second part of where clause in sql/vba has

(solved)-–-second-part-of-where-clause-in-sql/vba-has

I’m using vba, to combine entries in an sql database into 5 minute ranges into excel, ive noticed oddly that the code below would always pull in entries only until approx 9:15pm (from 12:00:0am) Checking the database there are plenty of entries after (9:15pm)

I’ve narrowed this down to the second part of the WHERE clause “AND [DateTime]<= DateAdd(Hour, DateDiff(Hour, 0, '" & DateYMD & "') 28, 0)" which doesn't seem to work, at all, separating it out or running it on the SQL server directly doesn't work

but just replacing the <= with a >= (without the other half of the where query) and it runs the query as expected

so I’m obviously missing something simple but not seeing it, thanks for the help 😡

selectCmd.ActiveConnection = adoDbConn
   selectCmd.CommandText = "SELECT DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', DateTime) / 5)*5, '20000101') as Date_Time " & _
    " ,max(Part_Count)-min(Part_Count) as PartsMade" & _
    ", max(Alarm_Light) as Alarmlight" & _
    ", max(PV_Alarm) as Alarm" & _
    " FROM [SMP].[dbo].[33_TestImport] " & _
    " Where [DateTime]>= DateAdd(Hour, DateDiff(Hour, 0, '" & DateYMD & "')-0, 0) AND [DateTime]<= DateAdd(Hour, DateDiff(Hour, 0, '" & DateYMD & "') 28, 0) " & _
    " AND Machine_Number = " & Machvar & " " & _
    " Group BY DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', DateTime) / 5)*5, '20000101') "

Leave a Reply

Your email address will not be published. Required fields are marked *