need help comparing dates in access via vba

Question

I have following problem: Can't get sql query to work with dates. Following query:

strRowSource = "SELECT [ID],[TaskType],[TaskName],[StartDate],[EndDate],[isFinished] " & _
"FROM tblTasks " & "WHERE [Person] = " & TempVars("CurrentUser") & " AND [EndDate] < #" & Format(Now(), "short date") & "#"

Doesn't return any value. what im trying to get is task list which are overdue.

Thanks in advance

nope doesn't work. im pulling my hairs off and already tried tens of different approaches — globster
– globster, Commented Aug 18, 2021 at 16:23

Olivier Jacot-Descombes · Accepted Answer · 2021-08-18 16:31:49Z

2

I am using this handy function to format dates:

Public Function JetSqlDate(ByVal d As Variant) As String
    If IsNull(d) Then
        JetSqlDate = "NULL"
    Else
        JetSqlDate = Format$(d, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
    End If
End Function

Usage:

strRowSource = "SELECT ID, TaskType, TaskName, StartDate, EndDate, isFinished " & _
  "FROM tblTasks WHERE Person = " & TempVars("CurrentUser") & _
  " AND EndDate < " &  JetSqlDate(Now())

Note that escaping column names ([]) is only required when they contain invalid characters like spaces or when they conflict with keywords. E.g. if you had a column named From you would have to write it as [From].

for SQL-Server pass-through queries I use:

Public Function TSqlDate(ByVal d As Variant) As String
    If IsNull(d) Then
        TSqlDate = "NULL"
    Else
        TSqlDate = "{ ts '" & Format$(d, "yyyy\-mm\-dd hh\:nn\:ss") & "' }"
    End If
End Function

For strings:

Public Function SqlStr(ByVal s As String) As String
'Input: s=""      Returns: NULL
'Input: s="abc"   Returns: 'abc'
'Input: s="x'y"   Returns: 'x''y'

    If s = "" Then
        SqlStr = "NULL"
    Else
        SqlStr = "'" & Replace(s, "'", "''") & "'"
    End If
End Function

Note that this functions adds the delimiters and escapes any delimiters contained in the string value. This makes queries more reliable and also prevents SQL Injection.

edited Aug 18, 2021 at 16:31

answered Aug 18, 2021 at 16:17

Olivier Jacot-Descombes

114k14 gold badges149 silver badges202 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

globster Over a year ago

Perfect Olivier - it does indeed work :) tyvm

globster Over a year ago

I wanted to upvote the answer but for that at least 15 rep is needed. Accepted the answer with checkmark though. Thanks for explaining me how things works here

Gustav · Accepted Answer · 2021-08-18 19:54:20Z

0

You could reduce it to:

strRowSource = "SELECT ID, TaskType, TaskName, StartDate, EndDate, isFinished " & _
    "FROM tblTasks " & _
    "WHERE Person = " & TempVars("CurrentUser") & " AND EndDate < Now()"

answered Aug 18, 2021 at 19:54

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Collectives™ on Stack Overflow

need help comparing dates in access via vba

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related