Invalid Bracketing of Name Weirdness

[Genimuse]

I have a query that runs great in Access itself, but when running it via ADO (via ASP) I get this unusual error:

Invalid bracketing of name '[Dr. ]'.

This is pretty strange, since "Dr. " is a string literal in the query, not a name.

Two tables, one full of TimeOff events, and one full of Employees. They link in two ways: TimeOff.EmployeeID links to Employee.ID, and TimeOff.CoveringID links to Employee.ID (two different employees). Here's the query that runs fine in Access but hangs via ADO:

SELECT 
    TimeOff.ID, 
    TimeOff.StartDate, 
    TimeOff.EndDate, 
    TimeOff.Holiday, 
    TimeOff.Description, 
    (IIF(IsNull(TimeOff.EmployeeID), "", IIF(Employee.Doctor, "Dr. ", "") & IIF(Employee.FirstNameBasis, Employee.FirstName, Employee.LastName))) AS EmployeeName, 
    (IIF(IsNull(TimeOff.CoveringID), "", (SELECT IIF(Doctor, "Dr. ", "") & IIF(FirstNameBasis, FirstName, LastName) FROM Employee WHERE ID = TimeOff.CoveringID))) AS CoverName 

FROM TimeOff LEFT JOIN Employee ON Employee.ID = TimeOff.EmployeeID 

WHERE TimeOff.EndDate > #9/30/2005# AND TimeOff.StartDate < #10/22/2005# 

ORDER BY TimeOff.EndDate DESC;

That first bit of complexity just concatenates a name if there is an EmployeeID associated with this TimeOff event.

That second bit of complexity does the same thing via a subquery if there's a CoveringID associated with this TimeOff event.

I thought perhaps there was a bit of ambiguity from the subquery, so I gave that Employee table an alias (E) and referred to its references as E., but it had no effect.

What might Access not like about my use of "Dr. " as a literal string like that, something that maybe it's willing to forgive in Access (2000) but not via an ADO request? Or is that just a red herring, and the problem is elsewhere?

Any hints or thoughts are appreciated, even if you're not certain you know the answer. Thanks!

 

[Genimuse]

Hmm. Well, interestingly, for some reason it's thinking that the "." in "Dr. " is dot notation separating "Dr" and " ", which it's unhappy with. Removing the "." results in a different error entirely, ADO claiming I'm missing a parameter.

So... through further experimentation I discovered (well, I think I knew this but spaced it tonight) that will the Access query analyzer loves double-quotes, ADO much prefers single. Changing all the double-quotes to singles in my SQL above solves the problem nicely