Converting an SQL query into access

Question

I have the following SQL Query:

 SELECT Clients.ClientRef as 'ID', Clients.Title + ' ' + Clients.Forename + ' ' + Clients.Surname as 'Name', 
                                                                    CONVERT(nvarchar(10), Clients.Dob, 103) as 'Date of Birth', 
                                                                    CASE WHEN Clients.IsMale = 1 THEN 'Male' WHEN Clients.IsMale = 0 THEN 'Female' END As 'Gender', 
                                                                    Convert(nvarchar(10), Max(Assessments.TestDate),103) as 'Last Visit', 
                                                                    CASE WHEN Max(Convert(integer,Assessments.Submitted)) = 1 Then 'Submitted' ELSE '' END AS 'Submission' 
                                                                    FROM Clients Left JOIN Assessments ON Clients.Id = Assessments.ClientId 

I want to convert it to be compatible with MSAccess.

I'm struggling with translating the 'Submission' column.

I've come up with the following access query, but it doesn't return anything when the corresponding SQL query returns some data.

SELECT   Clients.ClientRef AS [ID] , 
Clients.Title & ' ' & Clients.Forename & ' ' & Clients.Surname AS [Name] , 
Format(Clients.Dob,'dd/mm/yy') AS [Date of Birth] , 
IIF(Clients.IsMale,'Male','Female') AS [Gender] , 
Format(MAX(Assessments.TestDate),'dd/mm/yy') AS [Last Visit] ,

IIF( Max( IIF(Assessments.Submitted,1,0)) >= 1 ,'Submitted',' ') AS [Submission] 

FROM Clients LEFT JOIN Assessments ON Clients.Id = Assessments.ClientId 
WHERE
(ClientRef LIKE '%%' OR Forename LIKE '%%' OR Surname LIKE '%%') 
AND 
ClientRef NOT LIKE 'QA%' AND ClientRef NOT LIKE 'EHC%' 
GROUP BY 
Clients.ClientRef, Clients.Title, Clients.Forename, Clients.Surname, Clients.Dob, Clients.IsMale ORDER BY ClientRef

Any suggestions welcome.

Answer 1

Hi d347hm4n,

actually in access sql the wildcard is *, not the % as in standard sql so use that in your query and I think you'll get some result.

HTH Paolo

Answer 2

Thanks, I was copy and pasting the query from my application into Access, as I am using the Jetoledb engine. That want's % instead of * for the wildcard. I have been caught out by this in the past and I had left myself a comment to overcome this issue but I have been foiled again....

Thanks alot!