Referencing a control in an SQL criteria statment??

[JennyPeters]

I'm working on an Access .adp and am making an SQL statment query (not a stored procedure) that needs to reference a control on the form. Of course the good old 'builder' no longer seems to exist to help.

In the criteria column, I have entered:
= Forms![NewProducts]![CategoryID]

...which gives me an error stating "Invalid or missing expression".

Can you not reference a control in an Access .adp query?

Thanks,

jenny

 

[drctx]

i'm having this problem too.

 

[dschomburg]

Have you tried passing the value of the control to a global var. and then pass the value of the global to a function. You can then use the function in the crit.

 

[Norris68]

You can either:

Create your view (as we are working on MSDE/SQL - Access would call it a Query) without the criteria, but then open the query with a criteria e.g. "SELECT * FROM qryMyQuery WHERE CategoryID=" & Forms("NewProducts&quot.[CategoryID] (that is adding the current value as a literal to the SQL statement). This will mean dragging all of the matching records to the client then filtering them client-side.

Create a parameterized view - see "Using parameters in the SQL statement of a form or report" in the help. This will only return the records we want (more efficient, as filtered server-side), but is a little more awkward to use.

 

[JeremyNYC]

Just get rid of the "=" in the criteria box. That's already assumed.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.