Compare Date/Time in Query (1 Viewer)

[Micha3lS]

Hello all,

I'm trying to compare a time in date/time format to another time and check if ist bigger. The time comes from a query in which one column calls the function. The Function looks like this:

Public Function getShifDate(shiftType As Integer, datex As Date, timex As Date, loc As Integer) As Date

Dim weekdaynum As Integer
Dim weekdaynum2 As Integer
Dim datetimex As Date
Dim inRange As Long
Dim strSQL As String
Dim strSQL2 As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb

datetimex = datex + timex
weekdaynum = weekday(datetimex, vbMonday)
If weekaynum = 7 Then
    weekdaynum2 = 1
 End If

strSQL = "SELECT Max(ID) " _
       & "FROM Shifts " _
       & "WHERE ShiftType = " & shiftType & " " _
       & "AND Weekdaynumber = " & weekdaynum & " " _
       & "AND Werk = " & loc & " " _
       & "AND Start < #" & timex & "# ;"
      

strSQL2 = "SELECT Max(ID) " _
       & "FROM Shifts " _
       & "WHERE ShiftType = " & shiftType & " " _
       & "AND Weekdaynumber = " & weekdaynum2 & " " _
       & "AND Werk = " & loc & ";"

Debug.Print strSQL
Debug.Print strSQL2
Set rs = db.OpenRecordset(strSQL)

If rs.Fields.Count = 0 Then
    Set rs = db.OpenRecordset(strSQL)
    getShifDate = datex
Else
    getShifDate = DateAdd("d", -1, datex)
End If

The first query saved in strSQL does never return a result. The query printed looks like this:

SELECT Max(ID) FROM Shifts WHERE ShiftType = 3 AND Weekdaynumber = 2 AND Werk = 2101 AND Start < #13:08:14# ;

The tables Shifts and the query looks like this. The "Buchungsdatum" and "Istende" column contain the date and the time.

I guess it's just a syntax error but i can't get my head around where it is.
Would appreciate any help

Best Michael

 

[arnelgp]

can you use TimeValue in your query:

SELECT Max(ID) FROM Shifts WHERE ShiftType = 3 AND Weekdaynumber = 2 AND Werk = 2101 AND TimeValue([Start]) < #13:08:14#

*this will strip the Date part from the date field and will return only the Time part.

 

[Micha3lS]

Didn't make any difference unfortunately, still no result.

 

[Micha3lS]

I also tried using datediff:

SELECT Max(ID) FROM Shifts WHERE ShiftType = 3 AND Weekdaynumber = 2 AND Werk = 2101 AND Datediff('n',Start, #13:08:14#) < 0;

And

SELECT Max(ID) FROM Shifts WHERE ShiftType = 3 AND Weekdaynumber = 2 AND Werk = 2101 AND Datediff('n',TimeValue([Start]), #13:08:14#) < 0;

But the same problem applies. Still no result.

 

[arnelgp]

one comment on your code:

If rs.Fields.Count = 0 Then

this will not happen, since there is at least a Field will be returned by the query.

 

[Micha3lS]

You are absolutely correct. That seems to be the provlem.

However I tired to change it to

If rs.eof then

or

If rs.recordCount = 0

But the resultset always contains atleast one entry, sometime a value, sometimes Null.
Also I can not access this valuev with rs!ID. This returns error 3265.

How can I check if the query didnt retunr any value?

 

[Minty]

Your code is very confusing - this below (even if you changed the code to rs.recordcount) is trying to open the same recordset twice.
Did you not mean to open strSQL2

Set rs = db.OpenRecordset(strSQL)

If rs.Fields.Count = 0 Then
    Set rs = db.OpenRecordset(strSQL)     ' <<<<< you are setting the rs to the same as it's already set?
    getShifDate = datex
Else
    getShifDate = DateAdd("d", -1, datex)
End If

 

[arnelgp]

you only wanted to know if there is an entry with MaxID?

Dim lngID As Long
lngID = Nz(DMax("ID", "Shirts", "ShiftType = " & shiftType & " And Weekdaynum = " & weekdaynum & " And Werk = " & lock & " And TimeValue([Start]) < #13:08:14#"), 0)

If lngID <> 0 Then
getShifDate = datex
Else
getShifDate = DateAdd("d", -1, datex)
End If

 

[Micha3lS]

Yeah thats correct, that was another Typo, it should be strSQL, but didnt matter since it was never called anyway.

IsNull(rs.Fields(0).Value)

Did work for me now

 

[Minty]

For your info - with a DAO recordset the record count won't be accurate (and as you discovered can return gibberish) until you issue an rs.movelast as it won't know until the entire recordset is loaded.

However rs.recordcount = 0 will always be correct when there are no records.

 

[Micha3lS]

Thank you very much vor your help. I got it working, although looking really ugly and Using 3 querys:

If anybody is interested and finds a similar problem (Note the day starts with a night shift, e.g. from 22:00 the previous day)

Public Function getShiftID(shiftType As Integer, datex As Date, timex As Date, loc As Integer) As Integer

Dim weekdaynum As Integer
Dim weekdaynum2 As Integer
Dim datetimex As Date
Dim strSQL As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Set db = CurrentDb

datetimex = datex + timex
weekdaynum = weekday(datetimex, vbMonday)
'
'Debug.Print "##########" & timex & "###########"
'Debug.Print "ShiftType: " & shiftType
'Debug.Print "Werk: " & loc
'Debug.Print "WeekdayNum: " & weekdaynum

strSQL = "SELECT Max(ID) " _
       & "FROM Shifts " _
       & "WHERE ShiftType = " & shiftType & " " _
       & "AND Weekdaynumber = " & weekdaynum & " " _
       & "AND Werk = " & loc & " " _
       & "AND TimeValue([Start]) < #" & timex & "#" _
       & "AND TimeValue([End]) > #" & timex & "# "
      
strSQL3 = "SELECT Max(ID) " _
       & "FROM Shifts " _
       & "WHERE ShiftType = " & shiftType & " " _
       & "AND Weekdaynumber = " & weekdaynum & " " _
       & "AND Werk = " & loc & " "
      
Set rs = db.OpenRecordset(strSQL)
If shiftType = 2 Then

    If IsNull(rs.Fields(0).Value) Then
        Set rs3 = db.OpenRecordset(strSQL3)
        getShiftID = rs3.Fields(0).Value
    Else
        getShiftID = rs.Fields(0).Value
    End If
    
Else

    'Debug.Print "ShiftID: " & rs.Fields(0).Value
    If IsNull(rs.Fields(0).Value) Then
        If weekdaynum = 7 Then
            weekdaynum2 = 1
        Else
            weekdaynum2 = weekdaynum + 1
        End If
    
        strSQL2 = "SELECT Max(ID) " _
           & "FROM Shifts " _
           & "WHERE ShiftType = " & shiftType & " " _
           & "AND Weekdaynumber = " & weekdaynum2 & " " _
           & "AND Werk = " & loc & " " _
           & "AND TimeValue([Start]) < #" & timex & "# "
          
        Set rs2 = db.OpenRecordset(strSQL2)
        If IsNull(rs2.Fields(0).Value) Then
            Set rs3 = db.OpenRecordset(strSQL3)
            getShiftID = rs3.Fields(0).Value
        Else
            getShiftID = rs2.Fields(0).Value
        End If
    Else
        getShiftID = rs.Fields(0).Value
    End If
End If
End Function

It does take like 15 seconds to compute for my import, but that is not too bad and does what its supposed to.
Thanks for your help

 

[Minty]

That's a long time unless you have millions of records.
Have you got indexes on the Start and End time stamps, the locations and weekday numbers?

Also, it's good practice to close your recordset objects when you have finished with them.

 

[Micha3lS]

It's 10.000 maybe 20.000 records max for each import.

I added the indexes and tried again nothing changed. The hwole process includes importing the data from an excel file (7000 rows), calculating shift date and shiftID, then adding the result to the ProductionData table:

 DoCmd.TransferSpreadsheet acImport, , "Grunddaten", Directory, True, "Grunddaten!"
 
 strSQL = "INSERT INTO ProductionDataDaily " _
        & "SELECT Arbeitsplatz AS AP, Bestandskategorie, Auftrag, AuschussUrsache, Sum(Ausschuss2) As Ausschuss, Materialnummer, Sum(Gutmenge2) As Gutmenge, Materialkurztext as Artikel, ShiftDate AS Schichtdatum, ShiftID As SchichtID " _
        & "FROM (SELECT ProductionLines.ShiftType, ProductionLines.Standort, getShiftDate([ShiftType],[Buchungsdatum],[Istende],[Standort]) AS Shiftdate, getShiftID([ShiftType],[Buchungsdatum],[Istende],[Standort]) AS ShiftID, Grunddaten.Arbeitsplatz, Grunddaten.Buchungsdatum, Grunddaten.Istende, Grunddaten.Auftrag, Grunddaten.Materialnummer, Grunddaten.Materialkurztext, Grunddaten.Gutmenge As Gutmenge2, Grunddaten.Ausschuss As Ausschuss2, Grunddaten.AuschussUrsache, Grunddaten.Bestandskategorie, Grunddaten.ID " _
                & "FROM ProductionLines INNER JOIN Grunddaten ON ProductionLines.AP = Grunddaten.Arbeitsplatz) a " _
        & "WHERE  Arbeitsplatz Not Like  '*[!0-9]*' " _
        & "GROUP BY Arbeitsplatz, Auftrag, Materialkurztext, Materialnummer, ShiftID, Shiftdate, Bestandskategorie, AuschussUrsache "
 
 DoCmd.RunSQL (strSQL)
 DoCmd.RunSQL ("DELETE FROM Grunddaten")

In total, it takes 20 seconds (timed it)

 

[Minty]

If you are calling the function for every record in your import then it will get slow.

The more efficient way would be to create a query or temporary table that generates that result set and join it to your core data.

 

[Micha3lS]

Isn't that what I'm doing at the moment?

I import into the table Grunddaten, call the function to calculate the shift, then INSERT INTO the production data tabel and I delete the Grunddaten table afterwards, to be ready for the next import.

Or am I not understanding what you mean?