Access has that nice feature of integrating the query engine with the forms,
but SQL Server can't do it. You can create a user-defined function or a
stored procedure with a parameter, and then execute the stored procedure via
VBA code, passing it the parameter value. I'm not sure but I seem to
remember a form may automatically pass the parameter value if a control name
exactly matches the parameter name.
The SQL standards do not allow sorting in Views, which is what an Access
Select query represents. Access does allow the sort, but depending on which
SQL Server version you're using, SQL Server does not. User-defined functions
and stored procedures both support parameters and sorting.
Donna Brooks said:
Thanks Paul. I found my queries upsized as User-Defined Functions in SQL,
any
that had ORDER BY or parameters. I need to be able to pull a value from
the
active form, which in Access would be [form]![cmbModel]... I tried pasting
the sql view into a new query in sql and its giving me a syntax error..
Any
suggestions?
Paul Shapiro said:
Access query syntax is pretty far from the SQL standard. For example,
Access
allows VBA functions in the sql, which SQL Server can't process because
it
doesn't know those functions. Access has non-standard aggregate functions
like first() and last(), which are not available in SQL Server. Etc.
The last I knew (a few years back) the upsizing wizard ignores queries if
the sql is not legitimate SQL Server syntax. The only option is to
re-create
the queries in SQL Server, using the Access sql as a guide.
You should look for some Microsoft guidance on the query upsizing. One
pitfall I remember is that True/False are -1/0 in Access (and the Jet
database), but are 1/0 in SQL Server. If you used true and false in
Access,
the query might compile fine in SQL Server, but give the wrong result.