Excel to Access Dates

[JOCoyle]

Hello there,

I'm having a problem with inputting dates via SQL commands to my access database as they are getting changed to peculiar values.

Background:
The tool is a counter for the amount of work a person has done, each time the button is clicked the updated values are sent over to the Access database and the time of last click is updated, furthermore when the first click that day for that user is clicked the database logs that time and date.

So essentially I have the following code attached to the click button:

Sub dbUpdate()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strsql As String
        'Setup DB connections
    Set cnn = New ADODB.Connection
    cnn.Open ConnectionString:=Cnct
    Set rst = New ADODB.Recordset
        'Declare variables
    Dim EDate As Date 'Current date
    Dim StartTime As Date 'Start time (time of first, initial ENTRY in database)
    Dim Time As Date 'Time of last entry entered into database
        'Error handling:
    'On Error GoTo closeDB
        'Set variables
    EDate = Format(Now(), "DD/MM/YYYY")
    UserName = Environ("Username")
    Time = Format(Now(), "hh:mm")
    StartTime = Time
        'Set string to send to DB
    strsql = "SELECT * FROM Clicker WHERE UserName = '" & UserName & "' AND EDate = " & EDate & ";"
        'Open DB using string
    rst.Open strsql, cnn, adOpenStatic
        'If end of file then create record
    If rst.EOF Then
        With cnn
            strsql = "INSERT INTO [Clicker] ( [EDate], [UserName], [WiB], [DD], [Refund], [StartTime], [Time] )" & _
                        " SELECT " & EDate & " AS Expr1, '" & UserName & "' AS Expr2, " & WiB & " AS Expr3, " & DD & _
                        " AS Expr4, " & Refund & " AS Expr5, '" & StartTime & "' AS Expr6, '" & Time & "' AS Expr7;"
            .Execute strsql
        End With
    Else
        With cnn
            strsql = "UPDATE [Clicker] SET [WiB] = " & WiB & ", [DD] = " & DD & ", [Refund] = " & Refund & _
                        ", [Time] = '" & Time & "'" & _
                        " WHERE [EDate] = " & EDate & " AND [Username] = '" & UserName & "';"
            .Execute strsql
        End With
    End If
'closeDB:
        'Close DB
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    'If Err.Description <> "" Then
    '    MsgBox "Problem with Database connection: " & vbCrLf & Err.Description
    'End If
End Sub

This code was working to a certain extent however now whenever I use it it seems to enter the date into the database as

00:00:17

On top of this I also have a database sub which fires when the workbook opens and basically checks whether there is already anything there for the current user on todays date:

Sub dbOpenUpdate()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strsql As String
        'Setup DB connections
    Set cnn = New ADODB.Connection
    cnn.Open ConnectionString:=Cnct
    Set rst = New ADODB.Recordset
        'Declare variables
    Dim EDate As Date 'Current date
    Dim StartTime As Date 'Start time (time of first, initial entry in database)
    Dim Time As Date 'Time of last entry entered into database
        'Error handling:
    'On Error GoTo closeDB
        'Set variables
    EDate = Format(Now(), "dd mmm yyyy")
    UserName = Environ("Username")
    Time = Format(Now(), "hh:mm")
    StartTime = Time
        'Set string to send to DB
    strsql = "SELECT [DD], [WiB], [Refund] FROM [Clicker] WHERE [UserName] = '" & [UserName] & "' AND [EDate] = " & EDate & ";"
        'Open DB using string
    rst.CursorLocation = adUseClient
    rst.Open strsql, cnn, adOpenStatic, adLockReadOnly
     If Not rst.EOF Then
        DD = rst.Fields("DD").Value
        WiB = rst.Fields("WiB").Value
        Refund = rst.Fields("Refund").Value
    End If
    Worksheets("Data").Range("A5").CopyFromRecordset rst
    With Worksheets("Data")
        DD = .Range("A5").Value
        WiB = .Range("B5").Value
    End With
'closeDB:
        'Close DB
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    'If Err.Description <> "" Then
    '    MsgBox "Problem with Database connection: " & vbCrLf & Err.Description
    'End If
End Sub

Any help with this would be greatly appreciated, let me know if you need to know any details of the DB but I believe it is a problem with the code as everything is working fine besides the date when the second code I pasted fires.

Thanks,
JC

 

[Norie]

Try enclosing the date(s)/time(s) in # and using the format yyyy/mm/dd for the dates.

By the way, why do you have AS Expr1, AS Expr2 etc?

 

[JOCoyle]

Hey,

So I am a rookie at this unfortunately and I actually took the AS Expr1, etc from someone else's current dashboard style excel vba. I wasn't entirely sure if I needed it or not.

You are an absolute legend, thank you so much, this has been pestering me all night.

Thanks,
JC

 

[Norie]

No problem.

PS You don't need the AS Expr1... etc but they won't do any harm, mind you when I first looked at the code they did confuse me a bit.