I would like to pass a text string into a query

  • Thread starter Thread starter robkiolbasa
  • Start date Start date
R

robkiolbasa

Hello, I have a rather complex form that is creating a text string
that I would like to use for the WHERE clause in a query that I already
have designed. Right now I have it stored in just a text field, named
"txtWhere". Can someone help me with how to pass just this value into
the Where clause? Any help is appreciated, thanks

This is what I would like:

SELECT Data.chg_ref_num, Data.summary, Data.description, Data.priority,
Data.need_by, Data.Analyst_Analyst, Data.sym, Data.Short,
Data.Groups_Analyst, Data.l_details, Data.Desciption
FROM Data
WHERE ( **My String I have Created (txtWhere)** );

I have tested the syntax of the string and it works.
 
Remove the WHERE from your query
Then in your VB code run "SELECT * FROM MyQuery WHERE " & MyWhere

If you don't know how tio run a query from VB, look in Access Help

Dorian
 
create two queries. The first should be the SELECT part. The second, to
begin with can be a copy of the first. It is the one you will modify and
use. My normal practice is to name the first one I usually name something
like "qselXyzTemplate" and the second "qselXyz"

Now, here is how you do it:

strSQL = CurrentDb.QueryDefs("qselXyzTemplate").SQL
strSQL = Replace(strSQL,";", strWhere)
CurrentDb.QueryDefs("qselXyz").SQL = strSQL

Now it is ready to use
But, a couple of notes.
I used strWhere instead of txtWhere, because txt is a prefix for a text box
control and str is a prefix for a String variable. Also, it will need to
include a leading space, the word WHERE, and the terminating semicolon:

strWhere = " WHERE " & string you created & ";"
 
Back
Top