access vba insert date into table

[Tupacmoche]

I have googled this all day and tried many version and none have worked. The only part that does not work is the date portion. All, I want to do is insert the current time into a field called timeStamp to keep track of the rows. Can anyone see what is wrong? I just need the correct syntax so that I can insert a date.:banghead:
The now() function is inserting the date into a datetime column formated as a short date, Much thanks.

strSQL = "Insert into tblAssignments (Caller, HowMany,TimeStamp) VALUES ('" & Me.CboCaller.Value & "', '" & _
Me.CboHowMany.Value & "', #" & Now() & "#);"

 

[MarkK]

Can you edit the table? For a timestamp field I would set the default value of the field to Now() in the table. Then you don't have to bother with this when you insert a row because the value is automatically inserted by the table.

Steps are...
1) put the table in design view
2) select the field for which you'd the like the table to insert the current date/time automatically
3) on the general tab, below, set the Default Value property to Now()

 

[Tupacmoche]

Hi Mark,

Thanks for your suggestion, I will use it but after all the research, I would still like to know how to fix the script. What needs to be changed?

 

[sneuberg]

I was initially getting a syntax error with the SQL as it is. When I change TimeStamp, which is a reserve word, to TimeStump it started working and inserts the full date/time. You can see this in the attached database.

Edit: If I put the field in brackets like

strSQL = "Insert into tblAssignments (Caller, HowMany,[COLOR="Red"][[/COLOR]TimeStamp[COLOR="red"]][/COLOR]) VALUES ('" & Me.CboCaller.Value & "', '" & _
Me.CboHowMany.Value & "', #" & Now() & "#);"

it works with the field name as TimeStamp.

 

[The_Doc_Man]

The # syntax presages that you are inputting text to be converted to a date/time. Now() is a binary date and time.

You might have had success with

strSQL = "INSERT INTO tblAssignments ( Caller, HowMany, TimeStmp ) " & _
             "VALUES ('" & Me.CboCaller & "', " & _
                            CStr( " & Me.CboHowMany & " ) , " & _
                           "#" & FormatDateTime( Now(), "General Date" ) & "# ) ;"

Which presumes that cboHowMany is a combo box that contains a numeric value string. You might also have to look up the correct argument for the FormatDateTime function (q.v.) to include or exclude whatever parts as you need.

Since TimeStamp is a reserved word, I used TimeStmp simply because Access gets all whiney and uncooperative if you use keywords as variable names.

 

[sneuberg]

Here's a method that avoids the concatenation and the problems that it causes

Const SQL = "Insert into tblAssignments ([Caller], [HowMany],[TimeStamp]) VALUES (p0,p1,p2);"
With CurrentDb.CreateQueryDef("", SQL)
    .Parameters(0) = Me.CboCaller
    .Parameters(1) = Me.CboHowMany
    .Parameters(2) = Now()
    .Execute dbFailOnError
    .Close
End With

but even with this method I had to put TimeStamp in brackets.

As an aside TimeStamp is on this list of reserve words but it doesn't seem to be either an Access function or type. Maybe it's because it's used in SQL Server as a data type.

 

[Tupacmoche]

Thank you all for your observations MarkK worked fine but, I really wanted a programmatic why of solving this. The first one, I tried was from Sneuberg which worked perfectly. I'm sure the other coding methods would work but I'm done. Many thanks!

 

[The_Doc_Man]

MarkK's method (default value for that field) is the EASIEST to perform and, since it does not involve data type conversions but rather provides the right data in the right format without extra "handling" is least error-prone. Which is probably why it is such a good choice.

 

[MarkK]

If I wasn't able to set the default value property of the field at table level, I would use a temp QueryDef, as Steve suggests in post #6.

 

[isladogs]

As Steve correctly points out, timestamp is a datatype used in SQLServer.

It can be very useful to have a timestamp field in SQL Server linked tables used by Access. For example, it can prevent the dreaded write conflict error message.

 

[sonic8]

strSQL = "Insert into tblAssignments (Caller, HowMany,[COLOR=Red][[/COLOR]TimeStamp[COLOR=red]][/COLOR]) VALUES ('" & Me.CboCaller.Value & "', '" & _
Me.CboHowMany.Value & "', #" & Now() & "#);"

It is important to note that a straight string concatenation with a date as shown above with #" & Now() & "# only works if you have got the US date format configured for your computer. Otherwise it will result in a syntax error, because the database engine will not understand the date format used there.
You should explicitely format your dates using the Format-Function either with US- or ISO-date format.

I just recorded a video tutorial on SQL Strings in VBA, which is covering this issue with dates in SQL-Strings as well.

 

[sneuberg]

It is important to note that a straight string concatenation with a date as shown above with #" & Now() & "# only works if you have got the US date format configured for your computer. Otherwise it will result in a syntax error, because the database engine will not understand the date format used there.
You should explicitely format your dates using the Format-Function either with US- or ISO-date format.

I just recorded a video tutorial on SQL Strings in VBA, which is covering this issue with dates in SQL-Strings as well.

I tried that with my regional settings set to United Kingdom in the attached database and I didn't get a syntax error. However I then changed the date/time from Now() to a textbox that defaults to Now() so I could test other dates. 3/22/2017 or 22/03/2017 will be entered correctly; however, a date like 10/03/2017 will not if concatenated in. The method I posted in post #6 does handle dates correctly.

 

[sonic8]

3/22/2017 or 22/03/2017 will be entered correctly; however, a date like 10/03/2017 will not if concatenated in. The method I posted in post #6 does handle dates correctly.

If your local date format uses slashes to separate the date parts, you'll not get a syntax error. If there is only one way to interpret the date regarding day and month it will probably work.
However, if the date string entered is a valid mm/dd/yyyy pattern, then this is what the db engine will assume, even if you meant it to be dd/mm/yyyy. - No error, but a wrong date in your db. - Even worse!

Parameter-Queries solve the issue, because the dates do not need to be converted to strings, hence no ambiguity and no issues with the date format.
I recommend to use Parameter-Queries whenever possible, but for Rowsource- and Recordsource-Properties they require some extra effort.