you could try to create a Public function to a Module to convert it to Access Date and Time:
' arnelgpPublic Function MSSQLDateTime2ToDate(ByVal e As Variant) As Variant
    Dim i As Integer
    If IsNull(e) Then
        MSSQLDateTime2ToDate = Null
        Exit Function
    End If
    i = InStr(1, e, ".")
    If i <> 0 Then
        e = Left$(e, i - 1)
    End If
    MSSQLDateTime2ToDate = DateValue(e) + TimeValue(e) End Function
then on your query, you could use the function to create another Expression:

Select * From yourTableName Where DateVaue(MSSQLDateTime2ToDate([theDateFieldName])) >= Date()-1;