SQL error in Access query (1 Viewer)

ritco · May 16, 2012

I need to determine the last transaction for an item. I have a transaction table that has a date column and a time column. Because I need the max date and then the max time within the max date, a single Access query did not work. I can do it with multiple queries but was trying to keep it to one.
I have the following SQL script which works beautifully in Management Studio. I thought I could copy and paste it into Access query/SQL View but it doesn’t work:

SELECT hbt.PartNum, hbt.TranType, hbt.TranQty, (CAST(Cast(PostDate as DATE) as DateTime) + CAST(PostTime as Time))

FROM [SysproCompany1].[dbo].[HB_TRANSHISTORY] as hbt,

(SELECT max(CAST(Cast(PostDate as DATE) as DateTime) + CAST(PostTime as Time)) as maxPostDate, PartNum
from HB_TRANSHISTORY
Group by PartNum) maxresults

where hbt.PartNum = maxresults.PartNum and (CAST(Cast(PostDate as DATE) as DateTime) + CAST(PostTime as Time)) = maxresults.maxPostDate and hbt.TranType = 1
order by hbt.PartNum
I get the following error:
“Syntax error (missing operator) in query expression ‘(CAST(Cast(PostDate as DATE) as DateTime) + CAST(PostTime as Time))’

Does anyone know the proper syntax for this or am I trying to do something that can’t be done in Access in one query?

VilaRestal · May 16, 2012

Access doesn't have a Date and Time data types. It just has DateTime (and it's a very different DateTime to T-SQL).

And it doesn't have a CAST function. Just various data type conversion functions.

These are linked tables I presume, to the SQL server?

If so, why not save the view in SQL server and link to that as a table?

ritco · May 16, 2012

I thought the CAST function might be my problem.
Yes, I am running this on a SQL table. I have it linked through an ODBC connection.
I tried to run the query by creating new query (design), closing the show table window without picking any tables and then clicking on SQL View. I then pasted the code in the SQL window and clicked Run...and then, of course, I got the error.
I need to somehow concatenate the date & time columns so that I can get the true 'most recent' transaction.
I may have an item that has two transactions (same transaction type) on the same day but at different times. I need to pick up the latest one.

VilaRestal · May 16, 2012

Can you create the SQL into a view in Management Studio? (Do you have permission?) Then link to it as a new table in the database (in effect a query but access will call it a table).

That would save a lot of effort I think and would be server-side processing so more efficient.

ritco · May 16, 2012

I can do this as a SQL view and that is probably what my solution will end up being. I am trying to keep as much of the programming logic in Access as I can and keep my links minimal, that is why I did not just go with the view to begin with. I wanted to see if there was a better/more efficient way to do this directly in Access.

ritco · May 16, 2012

I do have permissions, I can create the view. It sounds like that's my most efficient solution. Thanks so much for your help!

VilaRestal · May 16, 2012

OK, but almost always more efficient to let the server do the querying (unless it's badly short on processor or memory).

If you're used to T-SQL you'll be in for a rude shock when you hit the limitations of Access SQL. It is quite horrible in comparison.

If I were you I'd do all queries in T-SQL wherever possible and would even create an ADO connection in VBA so you can use T-SQL syntax there too.

SQL error in Access query (1 Viewer)

ritco

Registered User.

VilaRestal

';drop database master;--

ritco

Registered User.

VilaRestal

';drop database master;--

ritco

Registered User.

ritco

Registered User.

VilaRestal

';drop database master;--

Similar threads

Users who are viewing this thread