0

I have found several posts on using the GETDATE() function for SQL Server linked table while in an Access front-end VBA procedure. Those posts are focused on the WHERE clause of the query, but I have been unable to find corresponding information on use of GETDATE() for column assignment.

For example, I understand that in the WHERE clause, I would use something like this:

WHERE MyDate = CAST(GETDATE() AS DATE)

However, I am getting syntax errors in VBA when I try to assign the current date to a column, like this:

INSERT INTO MyTable ( SomeValue, TheDate ) SELECT 'Widget' AS Expr1, CAST(GETDATE() AS DATE) AS Expr2;

In this example, TheDate is defined as DateTime in SQL Server. Written like this, VBA reports "Syntax error (missing operator) in query expression 'CAST(GETDATE() AS DATE)'. I tried to surround the expression with Access-friendly # date delimiters, but no luck there.

After spending about 30 minutes searching stackexchange.com various ways for MS Access Date() in SQL, I have been unable to find this. However it is so simple I am sure it was already answered somewhere.

Improve this question
4
  • 1
    In MS Access, you likely (not 100% sure for linked SQL, but you can try) can use Now() and Date() functions. First one is equivalent to getdate() in SQL, the second one returns current date without time - exactly what you need. Commented Sep 22, 2020 at 16:15
  • Yes! This works in my case. Thank you. I think it is the difference in column types. Date() works for a Date column, and Now() works for a DateTime column. When I import an Access table to SQL Server, the process will create the destination column as DateTime by default. Submit this as an answer and I will mark it as the solution. Commented Sep 22, 2020 at 17:08
  • This is not true. The only difference between Date() and Now() is, that Now() includes the time of the day. And now you mention import, but that is not what your question is about. Commented Sep 23, 2020 at 6:53
  • I only mention import to state that I am not choosing the destination column type, it is chosen for me. What I am seeing is that Now() returns both date and time, and that seems to work better than Date() for a destination column of type DateTime. Commented Sep 23, 2020 at 15:47

3 Answers 3

Reset to default
1

In MS Access you likely (not 100% sure for linked SQL, you have to experiment) should use Now() and Date() functions. First one is equivalent to getdate() in SQL, the second one returns current date without time.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This solved the problem for me, by changing the function Date() in my T-SQL statement instead to Now(). I changed that one thing and now the VBA code works as intended. I believe this is because the destination column has type DateTime, and the Date() function is therefore not working because it excludes a time component. It is unfortunate that MS Access flagged this as a "syntax" error when instead it really is a data type mismatch error. The "(missing operator)" part really sent me in the wrong direction.
0

If you run this in Access on a linked table (not a PT query), it should read:

INSERT INTO MyTable ( SomeValue, TheDate ) 
VALUES ('Widget', Date());
Improve this answer

2 Comments

Sadly, this fails when I try it in MS Access. I was able to get it working when I changed the column type from DateTime to Date, but that is not the result I need.
You must be mixing up things. DateTime (not DateTime2) is per definition the data type to use for date and time that should be read and written from Access.
0

There seems to be some confusing here. If you building a Access query, then ZERO ZERO of the SQL server date functions and syntax matter. Your SQL MUST continue to be written to Access standards unless you using a pass-though query.

However, I seen this 100x times here. What is the data type on sql server side? Is it datetime, or datetime2?

And double, triple, qudadropes, (and more) check the linked table in desing mode.

If you link to SQL server using the standard legacy "SQL Server" driver. The one that been shipped for 20 years since windows 98SE?

You MUST check if Access is seeing those columns as text, or as date columns (which in Access always allow a time part if you want).

Access code, queries, forms and EVERYTHING should require ZERO changes if you migrate that data from Access to SQL server and link the table. Again: ZERO ZERO changes.

However, if you used datetime2 on the SQL server side? Then you CAN NOT use the legacy "SQL server driver" when linking table. The reason is they don't support the newer datetime2 format. As a result, Access will actually see, use, and process that column as a text column. You REALLY, but REALLY do not want that to occur. Why? Becuase then you spend the next week asking questions on SO about how some date code or column or query does not work. again: ZERO ZERO changes are required in Access. If your dates are starting to break, then the issue is not date formats, but that column is now being seen by access as a TEXT data type.

Soltuion: Either change the sql side datetime2 columns to datetime, and re-link.

or re-link your tables using a newer native 11 (or later - up to 18 now). that way, access will see/use/process the datetime2 as a correct date format in Access.

So, before you do anything? Open one of the Access tables linked to SQL server in design mode. (ignore the read only prmompt). Now, look at the data type assigned to the date columns. If they are text, then you have a royal mess.

You need to re-link using the newer ODBC drivers.

Zero of your existing code, sql and quires should be touched or even changed if you using a linked table to sql server. But then again, if you linked using the wrong SQL ODBC driver, then Access cannot see nor process those datetime2 columns as date - it will be using text, and you beyond really don't want to allow that to occur.

In summary: Any date code, SQL updates, sorting, query, VBA code, form code, reports should continue to work with ZERO changes. If you are making changes to dates after a migration, then you done this all wrong, and those date columns are not being seen by access as date columns.

Either get rid of all datetime2 columns and then re-link (change them server side to datetime). Or re-link the tables using a native 11 or later ODBC driver. Either of these choices will fix this issue.

This is a fix that requires ZERO code, and zero changes to Access dealing with dates.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.