WHERE Clause in Rowsource code

[Wayne Emminizer]

I am trying to set a combo box's row source in VBA. When
I define that rowsource with no where clause (ex.1) or
with a where class but with no variable (ex.2) the proper
data is displayed. When I add a variable that is defined
through another function in the form though the where
clause does not "expand" but just shows the variable name
(ex.3)

ex.1 (No Where Clause) - strSQL = "SELECT DISTINCT
vueItemMaster.SubSpecies FROM vueItemMaster;"

ex.2 (Defined Where Clause) - strSQL = "SELECT DISTINCT
vueItemMaster.SubSpecies FROM vueItemMaster WHERE
[vueItemMaster.Species]='Abalone';"

ex.3 (With Variable) - strSQL = "SELECT DISTINCT
vueItemMaster.SubSpecies FROM vueItemMaster WHERE &
sqlFilter &"";"""

In example 3 sqlFilter is defined as a string value that
is built in code after each choise from a drop down. The
result that diaplys in the property sheet using ex.3 is
as follows:

SELECT DISTINCT vueItemMaster.SubSpecies FROM
vueItemMaster WHERE & sqlFilter &";"

As you can see, sqlFilter is not expanding to
[vueItemMaster.Species]='Abalone'

What am I missing?

 

[John Vinson]

I am trying to set a combo box's row source in VBA. When
I define that rowsource with no where clause (ex.1) or
with a where class but with no variable (ex.2) the proper
data is displayed. When I add a variable that is defined
through another function in the form though the where
clause does not "expand" but just shows the variable name
(ex.3)

Right. VBA is one language; SQL is another. SQL knows nothing about
VBA variables; you'll need to concatenate the variable (with, if need
be, the desired quote marks) with string constants to build up the
desired SQL string. E.g.

ex.3 (With Variable) - strSQL = "SELECT DISTINCT
vueItemMaster.SubSpecies FROM vueItemMaster WHERE &
sqlFilter &"";"""

strSQL = "SELECT DISTINCT vueItemMaster.SubSpecies " _
& "FROM vueItemMaster WHERE [vueItemMaster.Species]='" _
& sqlFilter & "' ORDER BY SubSpecies;"

If sqlFilter contains the text string Abalone (with no quotes) you'll
get the desired SQL expression.

 

[Wayne Emminizer]

Thanks John but the problem there is that the sqlFilter
needs to and does provide the entire filter. There are
40 or so possible fields that by themselves or in
conbination with other can be the filter. So Species
could be part of the filter or it could not. It would
depend on what is selected in the combo boxes on the
form. Once a choice is made I have code written for the
subform that builds the sqlFilter on the fly. That full
filter is what I need to pass to the SQL Where statement.

-----Original Message-----

I am trying to set a combo box's row source in VBA. When
I define that rowsource with no where clause (ex.1) or
with a where class but with no variable (ex.2) the proper
data is displayed. When I add a variable that is defined
through another function in the form though the where
clause does not "expand" but just shows the variable name
(ex.3)

Right. VBA is one language; SQL is another. SQL knows nothing about
VBA variables; you'll need to concatenate the variable (with, if need
be, the desired quote marks) with string constants to build up the
desired SQL string. E.g.

ex.3 (With Variable) - strSQL = "SELECT DISTINCT
vueItemMaster.SubSpecies FROM vueItemMaster WHERE &
sqlFilter &"";"""

strSQL = "SELECT DISTINCT vueItemMaster.SubSpecies " _
& "FROM vueItemMaster WHERE [vueItemMaster.Species]='" _
& sqlFilter & "' ORDER BY SubSpecies;"

If sqlFilter contains the text string Abalone (with no quotes) you'll
get the desired SQL expression.




.

 

[Wayne Emminizer]

Got it....used the same logic but made some changes to
accomodate what I needed.....thanks

-----Original Message-----

I am trying to set a combo box's row source in VBA. When
I define that rowsource with no where clause (ex.1) or
with a where class but with no variable (ex.2) the proper
data is displayed. When I add a variable that is defined
through another function in the form though the where
clause does not "expand" but just shows the variable name
(ex.3)

Right. VBA is one language; SQL is another. SQL knows nothing about
VBA variables; you'll need to concatenate the variable (with, if need
be, the desired quote marks) with string constants to build up the
desired SQL string. E.g.

ex.3 (With Variable) - strSQL = "SELECT DISTINCT
vueItemMaster.SubSpecies FROM vueItemMaster WHERE &
sqlFilter &"";"""

strSQL = "SELECT DISTINCT vueItemMaster.SubSpecies " _
& "FROM vueItemMaster WHERE [vueItemMaster.Species]='" _
& sqlFilter & "' ORDER BY SubSpecies;"

If sqlFilter contains the text string Abalone (with no quotes) you'll
get the desired SQL expression.




.