Access 2013 VBA Editor Changes Date() to date

1
votes

When I type strDate = Date() it switches to strDate = date, and then the code does not run correctly because it does not know what "date" refers to. This is in a brand new Access 2013 database with only a single form with a button to run the code and a table with only one field to insert the date.

References that are checked for this database are:

  • Visual Basic For Applications
  • Microsoft Access 15.0 Object Library
  • OLE Automation

  • Microsoft Office 15.0 Access database engine Object Library

    Dim strDate As String
strDate = Date()

DoCmd.SetWarnings False

Dim strSQL As String
strSQL = "INSERT INTO Table1 VALUES ('" & strDate & "');"
DoCmd.RunSQL strSQL

DoCmd.SetWarnings True
1
ms-accessvba

1 Answers

0
votes

Since you are inserting a date value into your table, the value should not be enclosed with single quotes, else the data will be interpreted as a string.

Similarly, the Date function will return a date value, hence your variable strDate should be of type Date as opposed to String.

The automatic conversion of Date() to Date is to be expected and will still result in the Date function being evaluated with no arguments; I believe this conversion occurs because the symbol Date also corresponds to a data type.

However, an easier way to achieve this is to simply evaluate the Date function directly in the SQL code, for example:

DoCmd.RunSQL "INSERT INTO Table1 VALUES (Date());"