Saving a Date/Time Stamp from VB 2005 to MS Access

[SilentThunderer]

Hey folks,
Let me start out by letting you know what I'm working with. I'm building an application in VB 2005 that is basically a userform that employees can use to "Clock in". The form allows the employee to enter their UserID and select "Login" or "Logout" and then click a submit. When the submit button is clicked, I want the application to dum the NT Userename, UserID, status (Login or Logout) and a date/time stamp into an MS Access database. The Access database (MyDatabase.mdb) only has a single table ([Raw Data]), and the field in the table that is supposed to take the date/time stamp ([Date Stamp]) is a "Date/Time" field that is formatted to "General Date". The problem that I'm having is (I think) in the format of the date/time stamp that is being transfered to the Access database.

I know that I could convert it to a string format, change the format of the feild in the table to Text and save it that way, but I need to be able to report off of it so I need it to be an actual date/time stamp in the database.

The error message that I'm getting is :::
Syntax error (missing operator) in query expression '7/6/2007 4:43:21 PM'.



The code that I'm using to get to this point is:::::

    Private Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
 
        'This is the code to submit a record to the database
        'Process Flow
 
 
        'Declaration of variables
        Dim strNTUser As String
        Dim MyDate As datetime
        'Dim MyTime As String
        Dim strUserID As String
        Dim strStatus As String
        Dim strMySQL As String
        Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""C:\My Database.mdb"""
        Dim DB As System.Data.OleDb.OleDbConnection
        DB = New OleDb.OleDbConnection(ConnectionString)
 
 
 
 
        'Data Validation
        If Me.txtUserID.Text = "" Or Not (IsNumeric(Me.txtUserID.Text)) Then
            MsgBox("Please enter a valid User ID - Data Not Saved")
            GoTo ExitMe
        End If
        If Me.cmbStatus.Text <> "Login" And Me.cmbStatus.Text <> "Logout" Then
            MsgBox("Please select a valid Login/Logout status from the drop-down - Data Not Saved")
            GoTo ExitMe
        End If
 
 
        'Assign values to variables
        strNTUser = Environment.UserName
        MyDate = Now
        strUserID = Me.txtUserID.Text
        strStatus = Me.cmbStatus.Text
 
 
        'Move the data to the database
        strMySQL = "Insert INTO [Raw Data]([Login ID], [Entry Type], [Entry Recorder], [Date Stamp]) VALUES (" & strUserID & ", " & strStatus & "," & strNTUser & ", " & MyDate & ")"
        Dim DBCommand As OleDb.OleDbCommand
 
        DB.Open()
        DBCommand = New OleDb.OleDbCommand(strMySQL, DB)
        DBCommand.ExecuteNonQuery()
        'DB.Close()
 
ExitMe:
    End Sub

Any help you could give me on this would be greatly appreciated. Thanks in advance.

[kenobewan]

Welcome to TSDN. I suggest finding what the General Date format is and use now, date or whatever is compatabile. HTH.

[SilentThunderer]

That's where my problem is.

When I enter sample data into the Access file it shows up in the field in the format "1/2/2007 12:00:00 PM".

When I go into my VB 2005 project and use a msgbox to output Now I get the exact same format.... "1/2/2007 12:00:00 PM".

[SilentThunderer]

Anyone have any suggestions?

[Plater]

When entering dates into a Date field, try treating them like a string. As in, put " around the value you are inserting.