By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

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

P: 3
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:::::

Expand|Select|Wrap|Line Numbers
  1.     Private Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
  2.  
  3.         'This is the code to submit a record to the database
  4.         'Process Flow
  5.  
  6.  
  7.         'Declaration of variables
  8.         Dim strNTUser As String
  9.         Dim MyDate As datetime
  10.         'Dim MyTime As String
  11.         Dim strUserID As String
  12.         Dim strStatus As String
  13.         Dim strMySQL As String
  14.         Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""C:\My Database.mdb"""
  15.         Dim DB As System.Data.OleDb.OleDbConnection
  16.         DB = New OleDb.OleDbConnection(ConnectionString)
  17.  
  18.  
  19.  
  20.  
  21.         'Data Validation
  22.         If Me.txtUserID.Text = "" Or Not (IsNumeric(Me.txtUserID.Text)) Then
  23.             MsgBox("Please enter a valid User ID - Data Not Saved")
  24.             GoTo ExitMe
  25.         End If
  26.         If Me.cmbStatus.Text <> "Login" And Me.cmbStatus.Text <> "Logout" Then
  27.             MsgBox("Please select a valid Login/Logout status from the drop-down - Data Not Saved")
  28.             GoTo ExitMe
  29.         End If
  30.  
  31.  
  32.         'Assign values to variables
  33.         strNTUser = Environment.UserName
  34.         MyDate = Now
  35.         strUserID = Me.txtUserID.Text
  36.         strStatus = Me.cmbStatus.Text
  37.  
  38.  
  39.         'Move the data to the database
  40.         strMySQL = "Insert INTO [Raw Data]([Login ID], [Entry Type], [Entry Recorder], [Date Stamp]) VALUES (" & strUserID & ", " & strStatus & "," & strNTUser & ", " & MyDate & ")"
  41.         Dim DBCommand As OleDb.OleDbCommand
  42.  
  43.         DB.Open()
  44.         DBCommand = New OleDb.OleDbCommand(strMySQL, DB)
  45.         DBCommand.ExecuteNonQuery()
  46.         'DB.Close()
  47.  
  48. ExitMe:
  49.     End Sub


Any help you could give me on this would be greatly appreciated. Thanks in advance.
Jul 6 '07 #1
Share this Question
Share on Google+
4 Replies


kenobewan
Expert 2.5K+
P: 4,871
Welcome to TSDN. I suggest finding what the General Date format is and use now, date or whatever is compatabile. HTH.
Jul 7 '07 #2

P: 3
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".
Jul 11 '07 #3

P: 3
Anyone have any suggestions?
Jul 13 '07 #4

Plater
Expert 5K+
P: 7,872
When entering dates into a Date field, try treating them like a string. As in, put " around the value you are inserting.
Jul 13 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.