Run Time Error 3075

[HRE]

I am attempting to edit the criteria of a query via code. The user will
provide the name of the query to edit. The query will contain a common
field called CtyNum. On command button click event I want to change the
criteria for the CtyNum field to reference a textbox value on my form.
The data type for the CtyNum field is Number. My code:

Dim DB As DAO.Database
Dim QD As QueryDef
Dim QN As String
Dim strSQL As String
Dim strSQL1 As String

Me.UFQryName.SetFocus
QN = Me.UFQryName.Text
Set DB = CurrentDb()
Set QD = DB.QueryDefs(QN)
With QD
strSQL = .SQL
strSQL1 = Left(strSQL, InStr(1, strSQL, "Where") - 1)
strSQL = strSQL1 & "Where CtyNum=" & Me.UFCtyNum
.SQL = strSQL
End With

I receive this error message:

Run-time error '3075':

Syntax error (missing operator) in query expression 'CtyNum='.

The debugger goes to this line of code (right before the End With):

.SQL = strSQL

Any help in figuring this out would be appreciated.

 

[Allen Browne]

It might be helpful to know where you are going with this query. I find it a
bit scary that you are trying to parse it like that. Unless you can be
certain of the contents, it's possible that the query might contain:
Select "SomeWhere" As Location, ...
in which case the Instr() would not be finding the correct clause.

It might help to search for for a WHERE that is immediately preceeded and
followed by a word demarker (CR, LF, space, bracket, etc) but that's messy.

I'm guessing there is an easier way to do this. Most things in Access can be
done by building the entire SQL statement dynamically instead of relying on
a saved query. Typically the stub (the SELECT and FROM clauses) and tail
(the ORDER BY clause) don't change, so you can put them into constants, and
patch the WHERE clause in between like this:
Dim strWhere As String
Const strcStub = "SELECT * FROM Table1 "
Const strcTail = "ORDER BY Field1;"
If Not IsNull(Me.UFCtyNum) Then
strWhere = "WHERE (CtyNum = " & Me.UFCtyNum & ") "
End If
Debug.Print strcStub & strWhere & strcTail

 

[HRE]

Allen:

Thanks for the quick reply. What I'm trying to accomplish is the
ability for any user within our organization to import a form
containing this code and select one of their queries in their DB that
contains the CtyNum field then change the Where clause of that criteria
to reference a text box (the value increments from 1 to 88 via code) on
the imported form. Ultimately, after this takes place, i will export
their query (which I have just edited via code) and create up to 88
spreadsheets for use by folks outside our organization.

So, while I like your suggestion, it wont' work for me in it's current
form because I need to change a saved query of which I won't know the
structure except for the fact that it will contain the CtyNum field.

Does this make any sense?

Your help is greatly appreciated.

 

[Allen Browne]

You can still build the entire query statement in code, and assign to to a
saved query if needed.

Just change the last line from the previous reply to:
CurrentDb.QueryDefs("Query1").SQL = strcStub & strWhere & strcTail

Now you still have the saved query to export, but you can start with the
known code rather than trying to parse the unknown query.

 

[Klatuu]

I believe the problem is that there is no space before the WHERE in his code.
The way it is coded will result in

SomeFieldWHERE

The code should be
strSQL1 = Left(strSQL, InStr(1, strSQL, " Where") - 1)

As to constructing in code, I agree; however, for lengthly queries that I
may need to modify at some point in the future, I will build the query in the
query builder with no WHERE and store it in a "template" query. I know
because the names always end with "Template". I will then copy that to the
name I will use. Then I start with the template, modify it and change the
query I will actually use. Here is an example:

strSQL = CurrentDb.QueryDefs("qselLaborPVAXLTemplate").SQL
strWhere = BuildWhere()
If Len(strWhere) > 0 Then
strSQL = Replace(strSQL, "ORDER BY", "HAVING " & strWhere & " ORDER
BY")
End If

CurrentDb.QueryDefs("qselLaborPVAXL").SQL = strSQL