WHERE in code to a query

  • Thread starter Thread starter Marc
  • Start date Start date
M

Marc

WHERE in code to a query

I would like to avoid adding a form expression in a query
such as =[Forms]![PopUp_Export]![CboN]
I've been advised that this can be done entering the WHERE
restriction by code, which looks something like this:

dim strWhere as string
if isnull(cboSalesRep) = false then
strWhere = "SalesRep = '" & cboSalesRep & "'"
end if

But I don't know how to make the Query to execute this
WHERE condition without entering the whole query using sql-
code. I would like to avoid this last option as it is a
very long query, with over 40 fields.

Thank you,

Marc
 
Dear Marc:

Am I to understand you object to the length of the string you must put
into the code?

We typically create and test the query, then simply paste the code
into the VBA editor. A few steps need to be followed. You must fix
up double quotes, and probably replace new lines with a space (so the
words don't just run together.) Retain some form of query so it is
readable (this assumes you know how to read SQL code and may have
developed some format that makes it easier to understand).

Without the ability to read and edit SQL you aren't going to go very
far in being able to create dynamic SQL. I suggest you need to get
accustomed to it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top