Creating Views in Access 2000 thru the GUI I have not found a way to use a "variable", eg referencing a text box, or a user defined function. This could be done if the view could be created programmatically from a sql string.
I can find no reference to this in help. Can this be done ?
Or should I go to stored procedures with parameters ?
I am creating a complex select statement, using nested subselects for aggregates, & could make it much more readable & testable if the subselects could be views.
Could you post an example of how you would like to use this? I'm not sure I have a handle on how the variables would be used for the view.
Usually in a scenario where I need to use a subselect, I filter the data using the where clause. You can reference values from the "main" select statement in your subselect, therefore "passing" the value.
Say I have a table with codes & descriptions, and a table with codes & amounts
SELECT t1.code, t1.description, (SELECT SUM(amount) FROM view WHERE view.code=t1.code) AS amount
Basically this is passing the t1.code as a variable to the subselect for each record.