Embedded Variables

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

Hi, I'm trying to figure out if there is any way to build a
variable that has other variables embedded in it. I have a
listbox that I want to change the rowsource criteria for
when the user selects various checkboxes. The rowsource is
a very long SQL string and the parts I am dynamiclly is
spread out through the SQL. I know I can break the SQL into
parts around the criteria and re-build it everytime one of
the checkboxes changes, however it would be so much easier
and cleaner if I could build the SQL into some sort of
variable, and embed other variables where my dynamic
critera is (some are where clauses, others are Order By
clauses). Then I could define the main SQL once, and just
change to embedded variable tied to whatever checkbox was
modified. Is there a way to do that with VB? It's been a
while, but I thought in Java you could do it with
constants, though I know you can't do that in VB. Thanks
for any help

Tim
 
many ways to do it. You may want to setup a SQL as string with special
marker on the variable like $parm1$. An then use Replace function to change
it to whatever it is changing. It may take a couple of tries because the
quotes and stuffs like that. This is like Java prepare statement, but not as
good.
 
I'm trying to figure out if there is any way to build a
variable that has other variables embedded in it.
Click to expand...

The easiest way is to use parameters, which you can point at values in the
GUI. For example,

WHERE MyTable.Status = Forms("MyForm").Controls("chkStatus").Value
AND MyTable.Owner = Forms("MyForm").Controls("txtOwner").Value

etc

Remember to Requery the listbox whenever one of the referenced controls is
changed.

HTH


Tim F
 
Thank you both for the suggestions. I can see where both of
them would work, however in my case Tim Ferguson's doesn't
help. Here is a simplified example of what I would like to
achieve:
----------------------------
strWhere1 = ""
strOrder1 = ""

strSQL = "Select * FROM tbl WHERE field_1 = '1' " &
strWhere1 & "ORDER BY field_1 " & strOrder1 & ";"

If Active_CheckBox Then
strWhere1 = "AND Status = 'Active'"
Else
strWhere1 = ""
End If

If Order_CheckBox Then
strOrder1 = ", Field_2 DESC"
Else
strOrder1 = ""
End If
---------------------------

Obviously I can't do this with Strings
I am trying to put in or remove whole new sections of the
where and order by clauses. If the user does not select the
criteria checkboxes, I need to completely remove the
portion of the related clause in order for the query to
work right. (And this would be useful in places besides SQL
statements) Phil's suggestion would work, and I'll use it,
but I wanted to know if there is a variable or object
construct in VB that can do this. Replace just doesn't seem
very efficient, though I could be wrong.

Thanks again for the help
Tim
 
If you are working with SQL, this is it. There is no other contruct. You
basically want to manipulate the string to suite your need. What do have
shown here would work just as well. Use that if you have success with it.
Access is not nearly as fancy as java, so don't think too much about it.
 
How about reorganizing your query build. Build strWhere1 and StrOrder1 first,
then include them in your strSQL.

strWhere1 = ""
strOrder1 = ""

If Active_CheckBox Then
strWhere1 = " AND Status = 'Active' "
Else
strWhere1 = ""
End If

If Order_CheckBox Then
strOrder1 = ", Field_2 DESC "
Else
strOrder1 = ""
End If

strSQL = "Select * FROM tbl WHERE field_1 = '1' " &
strWhere1 & "ORDER BY field_1 " & strOrder1 & ";"
 
Back
Top