SQL Query problem (1 Viewer)

[racolunga]

Hi everyone,
I am reading a SQL book and there is an example I am trying to use in access but it does not work. I know some key works are different in Access, can someone please help with the conversion, here is the example first in words and them in SQL.
How many years has each staff member been with the school?
SELECT StfLastName || ',' || StfFistName AS Staff, DateHired, CAST (('1999-10-01' -DateHired) / 365 AS INTEGER) AS YearsWithSchool
From Staff
ORDER BY StfLastName, StrFirstName

I am able to covert the example in Access if the two fields that are being subtracted were both date fields like this.
SELECT LastName & ',' & FirstName AS Staff, Hires.HireDate, CLng((CurrentDate-HireDate)/365) AS YearOfService
FROM Hires
ORDER BY Hires.LastName, Hires.FirstName;
But in this example one is a string and the other a date field. I know CAST will not work in access but I can't figure it out any suggestions I .

 

[Pat Hartman]

In T-SQL, single quotes are used for everything. Access SQL uses different characters to enclose dates and strings. Dates use the pound sign - #1999-10-01# and strings use single or double quotes.

 

[racolunga]

Thank you Pat Hartman that was easy I got it to work with your help.