VB Code to modify Query SQL Statement

[orbojeff]

Is it possible to use VB Code to modify the SQL Statement in a Query?

 

[Rick Brandt]

Is it possible to use VB Code to modify the SQL Statement in a Query?

CurrentDB.QueryDefs("QueryName").SQL = "Your new SQL"

 

[orbojeff]

Rick

Thanks for the quick reply
That didn't seem to work because of the SQL value I'm trying to enter

I'm looking to change my SQL Statement from
SELECT tbl_DATA.ROUTING, tbl_DATA.[PAYOR SCORE], tbl_DATA.[DOCUMENT
TYPE], tbl_DATA.AMOUNT, tbl_DATA.NAME1, tbl_DATA.STREET, tbl_DATA.CITY,
tbl_DATA.STATE, tbl_DATA.ZIP
FROM tbl_DATA;

to:
SELECT tbl_DATA.ROUTING, tbl_DATA.[PAYOR SCORE], tbl_DATA.[DOCUMENT
TYPE], tbl_DATA.AMOUNT, tbl_DATA.NAME1, tbl_DATA.STREET, tbl_DATA.CITY,
tbl_DATA.STATE, tbl_DATA.ZIP
FROM tbl_DATA
WHERE (((tbl_DATA.STATE)="MA"));

This is bound to a Command Button which I want to change the query
based on fields from a Form

Thanks
Jeff

 

[Rick Brandt]

Rick

Thanks for the quick reply
That didn't seem to work because of the SQL value I'm trying to enter

I'm looking to change my SQL Statement from
SELECT tbl_DATA.ROUTING, tbl_DATA.[PAYOR SCORE], tbl_DATA.[DOCUMENT
TYPE], tbl_DATA.AMOUNT, tbl_DATA.NAME1, tbl_DATA.STREET,
tbl_DATA.CITY, tbl_DATA.STATE, tbl_DATA.ZIP
FROM tbl_DATA;

to:
SELECT tbl_DATA.ROUTING, tbl_DATA.[PAYOR SCORE], tbl_DATA.[DOCUMENT
TYPE], tbl_DATA.AMOUNT, tbl_DATA.NAME1, tbl_DATA.STREET,
tbl_DATA.CITY, tbl_DATA.STATE, tbl_DATA.ZIP
FROM tbl_DATA
WHERE (((tbl_DATA.STATE)="MA"));

This is bound to a Command Button which I want to change the query
based on fields from a Form

The fact that the new string contains some double quotes likely "broke" the
code. Try using single quotes around MA.

 

[orbojeff]

what would the function look like is I wanted to just append the query
with WHERE (((tbl_DATA.STATE)="MA"));
Also I need a return carraige before this.
Is that possible?

 

[Rick Brandt]

what would the function look like is I wanted to just append the query
with WHERE (((tbl_DATA.STATE)="MA"));
Also I need a return carraige before this.
Is that possible?

Yes, you could use...

Dim db as Database
Dim qry as QueryDef

Set db = CurrentDB
Set qry = db.QueryDefs("QueryName")

qry.SQL = qry.SQL & "WHERE (((tbl_DATA.STATE)='MA'))"

....but there would be two problems. One is that the current SQL (if built from
the query designer) will have a semi-colon at the end so the above will result
in invalid SQL unless you strip that off first. Two is that this code would
only work as desired once. After that you would be adding a WHERE clause to a
query that already has a WHERE clause.

You need some source to use as the "base" SQL that you can then add the WHERE
clause to and use that combination as a new SQL property in its entirety. This
could be a variable, constant, or you could pull it from a utility table. I do
the latter pretty often. I have a table with an ID field and a memo field and I
store SQL snippets in the memo field and retrieve them with a function that uses
the ID number.

 

[orbojeff]

Rick

Thanks so much!
I was able to get that to work

One final question..
The command button is part of my main form frm_SEARCH
I've used the above to change the SQL statement on my Query qry_SEARCH
On the main form I have a sub form sfrm_SEARCH bound to this Query

What command can I use to refresh the Query in the Sub Form after
changing the SQL?

Jeff