Referring to a field in a form

David Lelièvre
David Lelièvre used Ask the Experts™
on
I'm trying to create a query in which I'm referring to a text field in a form, but I'm getting an error:

Microsoft Access database engine does not recognize 'forms!Stats!txtFromDT' as a valid field name or expression

Why is this happening?

SELECT *
FROM tblEmployee
WHERE [Date] between forms!frmStats!txtFromDT and forms!frmStats!txtThruDT and [WorkerID] = forms!frmStats!lstWorkID;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
That should be correct....is it on a subform within a main form?  and the form is open when the query executes?

Also, you'll want to use the Parameters dialog in the query to indicate to Access that the control reference contains a date/time value.   If you don't do this, Access will guess at the data type and may get it wrong.

Jim.

Author

Commented:
Right, the form wasn't open thanks! It was on the main form.

Author

Commented:
Sorry about this, but just a quick question related to this..

I've made a crosstab from the query and I'm getting the error:

Microsoft Access database engine does not recognize 'forms!Stats!lstSelect' as a valid field name or expression

Do I also have to the Parameters Dialog for this? (It's a multi selection listbox)

Thanks.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
You can't point to a multi-select list box.

Jim.

Author

Commented:
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial