syntax error in date in query expression

[Gusman_nz]

Hi all

I am getting Run time error (80040e07)
Syntax error in date in query expression '#23/09/2005 12:34:49 p.m.#'.

I get this message when using windows xp and access 2002, it does not happen when I use w2k and access 2000.

I am just a beginner at this stuff, any help would be appreciated

When I go to debug it takes me to the last CurrentProject.Connection.Execute strQuery in the code.

****code is as follows****

Option Compare Database

Global glbCurUser As Integer


Public Function main()
    DoCmd.OpenForm "frm_Login"
    
    Call Application.SetOption("Confirm Record Changes", False)
    Call Application.SetOption("Confirm Action Queries", False)
End Function

Public Function Login(UserName As String, Password As String) As Boolean
    Dim strQuery As String, rsResult As ADODB.Recordset
    
    glbCurUser = 0
    
    strQuery = "SELECT uid, ChangePwd FROM tbl_Users WHERE UserName = '" & UserName & "' AND Password = '" & Password & "'"
    
    Set rsResult = CurrentProject.Connection.Execute(strQuery)
    
    If Not rsResult.EOF Then
        glbCurUser = rsResult("uid")
        If Not GetTmp("IsLoggedIn", True) = False Then
            If MsgBox("You logged in elsewhere [" + CStr(GetTmp("IsLoggedIn", True)) + "] Would you like to log yourself out from there?", vbYesNo, "User Logged In Elsewhere") = vbYes Then
                SetTmp True, "LogMeOut"
                DoCmd.OpenForm "frm_LoginWait", , , , , , UserName + "|" + Password
                Login = True
                Exit Function
            Else
                DoCmd.Quit
            End If
        Else
            DoCmd.OpenForm "frm_Background", acNormal, , , , acHidden
            If rsResult("changepwd") = True Then
                MsgBox "Your password has expired, you must change it before you can use the database", , "Password Change"
                DoCmd.OpenForm "frm_PwdChange", , , , , , 1
            Else
                Form_frm_Background.CurUsr.Value = glbCurUser
                MsgBox ("You are now logged in")
            End If
            SetTmp Now(), "IsLoggedIn"
            Login = True
        End If
    Else
        Login = False
    End If
    
End Function

Public Function CheckLogin() As Boolean
    If glbCurUser = 0 Then
        If Form_frm_Background.CurUsr.Value > 0 Then
            glbCurUser = CInt(Form_frm_Background.CurUsr.Value)
        End If
    End If
End Function

Public Function GetTmp(TempID As Variant, Optional Persist As Boolean = False) As Variant
    Dim strQuery As String, rsResult As ADODB.Recordset, strLookup As String, varOut As Variant
    
    Select Case VarType(TempID)
        Case vbInteger, vbLong
            strLookup = "TempID=" + CStr(TempID)
        Case vbString
            strLookup = "Label='" + LCase(TempID) + "'"
        Case Else
            MsgBox ("Invalid Data Type in GetTmp()--> TempID argument")
            Exit Function
    End Select
    
    strQuery = "SELECT * FROM tbl_Temp WHERE UID=" + CStr(glbCurUser) + " AND " + strLookup
    
    Set rsResult = CurrentProject.Connection.Execute(strQuery)
    
    If rsResult.EOF Then
        GetTmp = False
        Exit Function
    End If
    
    Select Case rsResult("DataType")
    '1=int, 2=bit, 3=varchar, 4=bigint, 5=real, 6=datetime
        Case 1
            varOut = CInt(rsResult("int"))
        Case 2
            varOut = CBool(rsResult("bit"))
        Case 3
            varOut = CStr(rsResult("varchar"))
        Case 4
            varOut = CLng(rsResult("bigint"))
        Case 5
            varOut = CDbl(rsResult("real"))
        Case 6
            varOut = CDate(rsResult("datetime"))
    End Select
    
    If Not Persist Then
        strQuery = "DELETE FROM tbl_Temp WHERE TempID=" + CStr(rsResult("TempID"))
        CurrentProject.Connection.Execute strQuery
    End If
    
    GetTmp = varOut
    
End Function

Public Sub SetTmp(ByVal TempVal As Variant, Optional ByVal Label As String, Optional ByRef ID_OUT As Integer, Optional ByRef LABEL_OUT As String)
    Dim strQuery As String, rsResult As ADODB.Recordset, intTopRec As Integer, intType As Integer, strField As String, strMarker As String
    Dim strLbl As String
    '1=int, 2=bit, 3=varchar, 4=bigint, 5=real, 6=datetime
    strMarker = ""
    Select Case VarType(TempVal)
        Case vbInteger
            intType = 1
            strField = "int"
        Case vbBoolean
            intType = 2
            strField = "bit"
            If TempVal = True Then
                TempVal = 1
            Else
                TempVal = 0
            End If
        Case vbString
            intType = 3
            strField = "varchar"
            strMarker = "'"
        Case vbLong
            intType = 4
            strField = "bigint"
        Case vbDouble
            intType = 5
            strField = "real"
        Case vbDate
            intType = 6
            strField = "datetime"
            strMarker = "#"
    End Select
    
    If Not IsNull(Label) And Label <> "" Then
        strQuery = "SELECT TempID FROM tbl_Temp WHERE UID=" + CStr(glbCurUser) + " AND Label='" + LCase(Label) + "'"
        Set rsResult = CurrentProject.Connection.Execute(strQuery)
        strLbl = Label
        If rsResult.EOF Then
            strQuery = "INSERT INTO tbl_Temp (UID, DataType, Label, [" + strField + "]) VALUES (" + CStr(glbCurUser) + ", "
            strQuery = strQuery + CStr(intType) + ", '" + strLbl + "', " + (strMarker + CStr(TempVal) + strMarker) + ")"
        Else
            strQuery = "UPDATE tbl_Temp SET [" + strField + "]=" + (strMarker + CStr(TempVal) + strMarker) + " WHERE TempID=" + CStr(rsResult("TempID"))
        End If
    Else
        'if you are too lazy to make a label, this makes a unique one for you
        strLbl = "jk" + Right(CStr(DateDiff("s", #1/8/1983#, Now())), 8)
        strQuery = "INSERT INTO tbl_Temp (UID, DataType, Label,[" + strField + "]) VALUES (" + CStr(glbCurUser) + ", "
        strQuery = strQuery + CStr(intType) + ", '" + strLbl + "', " + (strMarker + TempVal + strMarker) + ")"
    End If
    
    CurrentProject.Connection.Execute strQuery
    
    strQuery = "SELECT TempID, Label FROM tbl_Temp WHERE Label='" + strLbl + "' AND UID=" + CStr(glbCurUser)
    Set rsResult = CurrentProject.Connection.Execute(strQuery)
    
    If Not rsResult.EOF Then
        ID_OUT = rsResult("TempID")
        LABEL_OUT = rsResult("Label")
    End If
    
    
End Sub