Complex Query (was: Refresh vs. requery vs. Remove Filter in FormsCoding NG)

[rgrantz]

I retitled this for future searches, as the problem and solution seem to be
getting away from the original post title. OK, I've been trying to apply
query logic using the SQL text editor rather than the query grid, as it
seems Access adds a whole bunch of opening and closing parenthesis and the
like, which messes up the order of criteria checked, etc. All criteria are
provided via unbound controls on the form. I have the following as the
Where Clause (my own comments for this post are preceded by the typical '
comment statement in VBA), and Access returns a "query is too complex"
message:

WHERE
(
(ProductionData.EnteredBy =
[Forms]![frmMainReport]![JREnteredBy])
OR
([Forms]![frmMainReport]![JREnteredBy] Is Null)
)
' the above returns records matching control value or all records if control
is null
AND
(
(ProductionData.DesBy =
[Forms]![frmMainReport]![JRDesBy])
OR
([Forms]![frmMainReport]![JRDesBy] Is Null)
)
' the above returns records matching control value or all records if control
is null
AND
(
(ProductionData.CustNum =
[Forms]![frmMainReport]![JRCustNum])
OR
([Forms]![frmMainReport]![JRCustNum] Is Null)
)
' the above returns records matching control value or all records if control
is null
AND
(
(([DateEntered]-[DateRec]) >=
[Forms]![frmMainReport]![JREntryTurn])
OR
(([DateEntered] Is Null) and (Date()-[DateRec]) >=
[Forms]![frmMainReport]![JREntryTurn])
)
' the above returns all records where the number of days between DateRec and
DateEntered is more than or equal to the number put in the JREntryTurn
field. ALSO, if for any record the DateEntered field has not been entered
yet, it will show records where the number of days between today's date and
DateRec is more than or equal to the JREntryTurn field value (I don't need
to check for null value with this). This is because turnaround for the
entry of orders received should still be shown for orders that are received
but haven't been entered yet. The same principal applies to the remaining
criteria below, which are all about the turnaround time between one phase of
the order process and the next (except the last one, which is total
turnaround from receipt to production).
AND
(
(([DateDes]-[DateEntered]) >=
[Forms]![frmMainReport]![JRDesTurn])
OR
(([DateDes] Is Null) and (Date()-[DateEntered]) >=
[Forms]![frmMainReport]![JRDesTurn])
)
AND
(
(([DateProd]-[DateDes]) >=
[Forms]![frmMainReport]![JRProdTurn])
OR
(([DateProd] Is Null) and (Date()-[DateDes]) >=
[Forms]![frmMainReport]![JRProdTurn])
)
AND
(
(([DateProd]-[DateEntered]) >=
[Forms]![frmMainReport]![JRTotalTurn])
OR
(([DateProd] Is Null) and (Date()-[DateRec]) >=
[Forms]![frmMainReport]![JRTotalTurn])
)

Now, the query can't be opened in SQL view (text too long to be edited), but
I DID notice that before I added a couple additional criteria, Access did
replace the original SQL that I typed in myself with a HUGE increase in text
after the query was run using the form (ie. I typed in the SQL string seen
above, saved and closed the query, opened the form whose recordsource is
this query, changed a fiew criteria and refreshed, and then came back to the
query and opened it in SQL view, and saw that there was at least 5 times the
amount of text and parenthetical phrases as what I had originally typed). I
don't know if Access automatically changing the logic to longer strings is
part of the problem or not.

I can make this DB available for DL if someone wants to see this behaviour
duplicated.

Can anyone tell me a way to accomplish the SQL string above without a "too
complex" error?

 

[Dale Fye]

rGrantz:

This is getting way too complicated, and you are right, Access may be
responsible. My recommendation is that you build the SQL in code. Then run
this code in the afterupdate of each of your text boxes.

The subroutine will build your query at run time, whenever you make a change
in one of the appropriate fields. I tried to avoid using ISNULL() with
regards to controls, because if you fill in a control, then delete the value
from it, it will no longer be null, but be an empty string.

Since it is not clear whether some of these fields are text or numeric, I'll
make some assumptions and document them in the code, to give you examples of
how to handle different types of data. You will notice that I use a variant
as my data type for varCriteria. This is so I can add (+) a Null and a text
string and still get a Null. This is useful when you want to separate parts
of a WHERE clause with "AND" but don't know whether there is anything in the
criteria to start with.

HTH

Dale

Private Sub ComplexQuery:

Dim strSQL as string
Dim varCriteria as variant

strSQL = "SELECT * FROM yourTable "

varCriteria = NULL
'Assume this field is numeric
if LEN(me.JREnteredBy & "") > 0 THEN
varCriteria = varCriteria & "ProductionData.EnteredBy = " &
me.JREnteredBy
End if

'I'll assume this is a date value
If LEN(me.JRDesBy & "") > 0 then
varCriteria = (varCriteria + " AND ") _
& "ProductionData.DesBy = #" & me.JRDesBy & "#"
Endif

'I'll assume this one is text
IF LEN(me.JRCustNum & "") > 0 Then
varCriteria = (varCriteria + " AND ") _
& "ProductionData.CustNum = '" & Me.JRCustNum & "'"
Endif

'
IF ISNULL(DateEntered) THEN
varCriteria = (varCriteria + " AND ") _
& "[DateEntered] - [DateRec] >= " & me.JREntryTurn
Else
varCriteria = (varCriteria + " AND ") _
& "Date()-[DateRec] >= " & me.JREntryTurn
Endif

strSQL = strSQL & (" WHERE " + varCriteria)
currentdb.querydef("YourQueryName").sql = strSQL

me.requery

End Sub