MS Access SQL Server DB - Query Syntax for CAST Function

Question

I have converted an Access db (.mdb) to SQL Server. In the meantime I still need to use Access as a front end until new application forms are constructed. Can someone tell me what I might do to fix the situation where:

In Access 2007, a query such as:

SELECT *
FROM TransactionTotals
WHERE TransactionTotals.[Date]= Date()
ORDER BY TransactionTotals.EntryID DESC;

worked, however since the Date() function will not work with SQL Server, with help in a previous post the correct syntax is:

SELECT *
FROM TransactionTotals
WHERE TransactionTotals.[Date]= CAST(GETDATE() AS DATE)
ORDER BY TransactionTotals.EntryID DESC;

BUT! Although the code above will work in a direct SQL Server query (SQL Management Studio), it will be tossed out in Access with a Syntax Error response on the WHERE clause.

Can something be done in Access so I can still run my query bound forms.

chris_techno25 · Accepted Answer · 2014-02-10 00:08:50Z

0

I usually do exactly what you do, Access first before migration to SQL server. Access has some really weird syntax compared to server type databases especially when it comes to JOIN clauses and dates, GETDATE() only works on SQL Server, in Access, try this...

SELECT *
FROM TransactionTotals
WHERE TransactionTotals.[Date]= Format(NOW(),"General Date")
ORDER BY TransactionTotals.EntryID DESC;

Feel free to change the format of the date, with or without time.

answered Feb 10, 2014 at 0:08

chris_techno25

2,5176 gold badges22 silver badges35 bronze badges

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

7 Comments

user1457613 Over a year ago

That works agains SQL Server from Acces as: Format(NOW(),"yyyy-mm-dd 00:00:00"). However, what may work in a Access query such as WHERE TransactionTotals.[Date] Between Date()-7 AND Date()

chris_techno25 Over a year ago

Uhmmm, Can you explain a bit more about what you need? From what I understood, you need to query against a database with a condition comparing the current date, and your SQL statement that works on SQL Server doesn't work on Access right? Are you trying to find a function that works on both?

user1457613 Over a year ago

Access (.mdb) recently converted MDB to SQL Server - date fields in datetime2. Linked tables (odbc) Just trying to get a couple of Access query bound forms to still pull records from the SQL Server, until can build new .NET application.

chris_techno25 Over a year ago

So your current SQL statement does not work on Access right? And you're trying to convert the statement to something that's SQL server friendly?

chris_techno25 Over a year ago

What problem do you see when using my suggested SQL statement above?

|

Collectives™ on Stack Overflow

MS Access SQL Server DB - Query Syntax for CAST Function

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related