Hi Mark,
PMFBI
Its not so much a "bug" as it is
Access thinking it can write your
WHERE clause more efficiently
when it sees all those AND's and OR's
as you save the query (it has nothing to
do with the additional brackets).
You wanted to use the trick of
WHERE
(field 1= parameter1 OR parameter1 IS NULL)
AND
(field2=parameter2 OR parameter2 IS NULL)
AND
(field3=parameter3 OR parameter3 IS NULL);
If you try this with more than a couple of sets,
Access, in its infinite wisdom, thinks it would be
more efficient to say
WHERE
(field1=parameter1
AND
field2=parameter2
AND
field3=parameter3)
OR
(parameter1 IS NULL
AND
parameter2 IS NULL
AND
parameter3 IS NULL);
That isn't going to return *anything*
unless they *all* match their parameters
or the parameters are *all* NULL!
Go back into SQL view and rewrite
in original form above, then save it
from SQL View (I believe Access
won't mess with it then).
Or just rewrite the query using
values from your form in click
event of command button on your
form that runs the query (and forget
using form parameters in queries).
(quick and dirty air code
Dim strSQL As String
Dim strWhere As String
strSQL = "SELECT t.Type, t.tag, t.Name, " _
& "t.version, t.Description, t.Owner, " _
& "t.Status, t.Priority, t.uda_name, " _
& "t.uda_value " _
& "FROM tblReqsDetail AS t"
If Len(Trim(Me!cboType & "")) > 0 then
strWhere = " WHERE (t.[Type] = '" & me!cboType & "')"
Else
strWhere = " WHERE (1=1)"
End If
If Len(Trim(Me!cboOwner & "")) > 0 then
strWhere = strWhere & " AND (t.Owner = '" & me!cboOwner & "')"
Else
'no parameter
End If
If Len(Trim(Me!cboStatus & "")) > 0 then
strWhere = strWhere & " AND (t.Status =' " & me!cboStatus & "')"
Else
'no parameter
End If
If Len(Trim(Me!cboOriginator & "")) > 0 then
strWhere = strWhere & " AND (t.uda_value = '" & me!cboOriginator & "')"
Else
'no parameter
End If
strSQL = strSQL & strWhere
'view new sql in Immediate window
Debug.Print strSQL
'(must have reference set to DAO)
'redefine your query
CurrentDb.QueryDefs("nameofyourquery").SQL = strSQL
'now do something with your new query
Code above assumes all your WHERE fields were
type TEXT...if any were not, then delete the
single quotes surrounding me!cboxxxx
Apologies again for butting in,
gary
MarkK said:
Thanks very much for the responses:
To clarify, Access transforms Forms![frmSample]![cboSampleValue] to
[Forms]![frmSample]![cboSampleValue] .
Here is the SQL:
SELECT tblReqsDetail.Type, tblReqsDetail.tag, tblReqsDetail.Name,
tblReqsDetail.version, tblReqsDetail.Description, tblReqsDetail.Owner,
tblReqsDetail.Status, tblReqsDetail.Priority, tblReqsDetail.uda_name,
tblReqsDetail.uda_value
FROM tblReqsDetail
WHERE (((tblReqsDetail.Type)=[Forms]![frmQBF]![cboType]) AND
((tblReqsDetail.Owner)=[Forms]![frmQBF]![cboOwner]) AND
((tblReqsDetail.Status)=[Forms]![frmQBF]![cboStatus]) AND
((tblReqsDetail.uda_value)=[Forms]![frmQBF]![cboOriginator])) OR
((([Forms]![frmQBF]![cboType]) Is Null) AND (([Forms]![frmQBF]![cboOwner])
Is
Null) AND (([Forms]![frmQBF]![cboStatus]) Is Null) AND
(([Forms]![frmQBF]![cboOriginator]) Is Null));
Van T. Dinh said:
That shouldn't be the cause of the syntax error. The set [] is simply
name
delimiter and the syntax is valid.
The problem may be somewhere else.
Post the entire SQL and the error message you got.
--
HTH
Van T. Dinh
MVP (Access)
MarkK said:
When entering parameter criteria referencing value of form control (ex:
Forms![frmSample]![cboSampleValue]) in QBE grid, Access adds brackets
to
the
expression, which results in syntax errors (plus can't run the query),
and
I
can't stop it from happening?
What is going on???
