problems with union query

I have the following union query behind a dropdown in access2007.

SELECT "(All)" As Section FROM tblCoversheets UNION Select [Section] FROM tblCoversheets WHERE CoverSheetDate = #"  & Me.cmbDateTime & "#"

Me.cmbDateTime comes from a dropdown that list the coverheetdates

When I click on the combobox that should list the sections I get the following
error: the microsoft database engine could not find the object 'coversheetdate = #" & Me.cmdDateTime & "&".

I have no idea why I'm getting this error.
Any help is most appreciated.
BauwensERAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
Where exactly is this sql expression located ?  The expression does not make sense as it is posted here because the combination of " being used render it invalid.
0
BauwensERAuthor Commented:
It is in the rowsource of the 2nd drop-down on a form.
Basicaly first the user select the date of the coversheet (1st drop-down) then
the user select the section(2nd drop-down).
I hope this is clear enough..
0
Shiv_SgCommented:
I am not an Access Expert but I see this double-quote (if it is a double-quote not 2 single quotes) WHERE CoverSheetDate = #" hanging in the air without an end quote, I would guess it should be

WHERE CoverSheetDate= "#"
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

BauwensERAuthor Commented:
I have now this as rowsource:

SELECT "(All)" As Section FROM tblCoversheets UNION Select [Section] FROM tblCoversheets WHERE CoverSheetDate = "#" & Me!cmbDateTime & "#" ORDER BY Section;

but when I open the dropdown I get a pop-up with Enter Parameter ValueMe!cmbDateTime
0
als315Commented:
In rowsource you can't use Me!cmbDateTime. Use Combobox name without Me
Why you need there union query if both queries are from one table?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BauwensERAuthor Commented:
I just tried that but then I get the message that query is too complex to be evaluated.
(frustrating). I need the union because I need the string(All) so that users can print ALL coversheets if they need so.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.