-1

I have an ODBC linked table in MS Access to SQL Server. In a query in MS Access, I have the following expression on a field:

DATE() - [DATE] (a field).

I need to change MS Access function DATE(), i.e. current date to SQL Server GETDATE(). How can I cast this in the expression builder in MS Access?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Robleh100
  • 1
  • You can't. `GETDATE()` is _T-SQL_ which you, in Access, only can use in a pass-through query, as Access SQL knows nothing about it. – Gustav Oct 10 '21 at 12:17
  • 1
    I don't have a clue what you mean. Access automatically converts function calls in the backend when it leads to performance benefits, what is your goal with this "casting"? Does something not work? – Erik A Oct 10 '21 at 14:20

1 Answers1

0

You can't really use the GetDate() t-sql server side value.

However, what you could do, is in place of using linked table to the sql server table?

You could create a view and have this

SELECT *, GETDATE() as MyServerDate FROM tblInvoices.

Now, in your client side query, you have a column called MyServerDate.

And thus you could do this:

SELECT *, (DATE() - [MyServerDate] as MydueDate from MyView

Of course the other way would be to use a pass-though query, but they are read-only. So if the sql is only for a report, or some screen display, then you could consider using a 100% server side query. So, you create the query in Access, but set it as a PT query. As a result, the raw SQL syntax in that Access query you build will have to be t-sql sql, not access sql.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51