Query code for Access 2007

[grenee]

Good day All.

I have developed my access program in 2013 ver. but has to use it in a 2007 environment. so obviously some things don't work.

This query in vba works fine in 2013 but Although it does not trigger an error message it simple give no results in 2007. Can anyone assist? Thank you.

strSQL = "Select * from [tblSignin] WHERE tblSignin.[OfficerName]='" & Form_frmSignin.txtUser _
& "' AND tblSignin.[Auto Date and Time Entry]=" & Format(Form_frmSignin.Text24, "\#mm\/dd\/yyyy\#")

 

[JHB]

If [Auto Date and Time Entry] also have a time value in it, then the problem is here.

 

[arnelgp]

aside of jhb's response, you can also query using the date portion only:

strSQL = "Select * from [tblSignin] WHERE tblSignin.[OfficerName]='" & Form_frmSignin.txtUser _
& "' AND DateValue(tblSignin.[Auto Date and Time Entry])=" & Format(Form_frmSignin.Text24, "\#mm\/dd\/yyyy\#")

 

[Simon_MT]

I always in use this:

SearchDate= format(WhateeverDate),"mm/dd/yyyy")

Function GetDateConvert(SearchDate As Variant) As String
    GetDateConvert = Format(SearchDate, "mm/dd/yyyy")
End Function

OR

Function GetPeriodVariant(SearchDate As Variant) As String
    On Error GoTo Err_GetPeriodVariant
  
Dim dbPeriod As DAO.Database
Dim rsPeriod As DAO.Recordset
Dim rsSqlPeriod As String

    With CodeContextObject
        
        Set dbPeriod = CurrentDb
        rsSqlPeriod = "SELECT Periods.[Period No] FROM Periods WHERE Periods.[Period Start Date] <=#" & SearchDate & "# AND Periods.[Period End Date] >=#" & SearchDate & "#;"
        Set rsPeriod = dbPeriod.OpenRecordset(rsSqlPeriod)
        rsPeriod.MoveFirst
        GetPeriodVariant = rsPeriod![Period No]
    End With

Exit_GetPeriodVariant:
    rsPeriod.Close
    Set rsPeriod = Nothing
    Set dbPeriod = Nothing
    Exit Function

Err_GetPeriodVariant:
     Resume Exit_GetPeriodVariant

End Function

Simon

 

[grenee]

Thanks for your response.

It does have a time value included

 

[grenee]

Thanks. That is the case.

 

[grenee]

Thanks.

I tried this and it works.