Unanswered: WHERE Argument of OpenReport Needs Alias?
I have a report based on a query something along the lines of
SELECT ..., [tblName].[fldName] AS [AliasName], ...
ORDER BY... '
I have a form that builds a WHERE string based on input from the user. I build this WHERE string into the above query in VBA (so it's all one SQL statement) and use this successfully to create a query recordset (so I know the WHERE string works).
I then try to open a report using this WHERE string like:
... ([tblName].[fldName] IN ('CaseA', 'CaseB')) AND ...
At this point it gives me that annoying "Enter Parameter Value" pop-up, basically it can't figure out what [tblName].[fldName] is (even though it's in the source query).
When I try
DoCmd.OpenReport "rptName", acViewPreview, , "([AliasName] IN ('CaseA', 'CaseB'))"
that works fine. But that's not the way I already have the WHERE string built, unfortunately.
And here's the part I really can't figure out, I took this same setup from an Access database with the tables inside of it, and it worked fine. Now I have an Access FE with a SQL Server BE and it's giving me these headaches.
Anyone seen this behavior before? Know how to fix it?
For anyone with the same problem, couldn't figure out a direct solution to the problem. So I created a DAO query, and based the report of that. I modify the query's SQL and then open the report. It works.