Changing Query Criteria via Code 2

[joatmofn]

I have a query (created in design view) where the criteria field references a control on a form such as [Forms]![frmCreateWorkOrder10]![WorkOrderID]. By default the SQL uses this with the equal operator.

I need the same query, however, I need to change the operator. e.g., <, <>, etc.

Any ideas on how to accomplish this would be great.

Thanks.

 

[flyover789]

you can edit the SQL of a saved query via VBA as follows:

Dim strSQL as String
Dim db as DAO.Database
Dim qdf as DAO.Querydef
Set db = Application.CurrentDB
set qdf = db.Querydefs("yourSavedQueryName")

strSQL = "SELECT ... FROM ... WHERE ....;"

qdf.SQL = strSQL

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Database_Systems and _Applications shared across all Business_Areas[/blue]

 

[joatmofn]

I thought about using a qrydef, but this particular query is a rather large crosstab. I thought that I could make a function to send the query to that (the function) would search for the operator to change, change it to whatever I want, then return the edited query for execution. Is this a sound idea, or is there another way to locate the operator and change it? thanks.

 

[flyover789]

you only have to build the WHERE clause of your query via code ... should not be so difficult ...

have you thought about splitting the sql of the qdf via Instr and just insert the modified WHERE clause between the left and right part of the existing SQL ...

something like

strSQL = Left(qdf.Sql, InStr(qdf.Sql, "WHERE") - 1) & _
" WHERE yourCritField Like ""*yourCritVal*"" " & _
Right(qdf.Sql, Len(qdf.Sql) - InStr(qdf.Sql, "GROUP") + 1)

Just a thought,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Database_Systems and _Applications shared across all Business_Areas[/blue]

 

[joatmofn]

Yes, I get the idea. But one thing though, how does your code know how many characters are in the existing where clause?

 

[joatmofn]

Here's a thought. It seems that I could remove the where clause from the crosstab alltogether. Then use your code to insert it by locating the "Group By" portion of the sql.

 

[flyover789]

i've tested the a.m. code, and it worked ...

Good luck,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Database_Systems and _Applications shared across all Business_Areas[/blue]

 

[joatmofn]

Got it. here's a star. Thanks.