Expression too complex in query expression

[gmazza via AccessMonster.com]

Hi there,
I have 5 combo boxes and I need to be able to choose 1 or all, and the
relevant info show up on my form.
So if I pick 1 and nothing in the rest, show results for just that 1 combo.
If I choose combo 1, then combo 2 will show results from combo 1 and null the
rest. Hope you know what I mean.
This was the only way I could figure to do it and I am getting an error when
I try and view the query in Design view. Anyone know of a better way to do it
or see why I am getting this error? Thanks!

SELECT DISTINCT QCRecord.SampleID
FROM QCRecord
WHERE (((QCRecord.QCDate)=[Forms]![QCRecords]![cboDate])
AND ((QCRecord.AssayCode)=[Forms]![QCRecords]![cboAssay])
AND ((QCRecord.LotNo)=[Forms]![QCRecords]![cboLot])
AND ((QCRecord.BinderNo)=[Forms]![QCRecords]![cboBinder].[column](1)))

OR ((([Forms]![QCRecords]![cboDate] Is Null)
AND (([Forms]![QCRecords]![cboAssay] Is Null)
AND (([Forms]![QCRecords]![cboLot] Is Null)
AND (([Forms]![QCRecords]![cboBinder] Is Null))

OR (((QCRecord.QCDate)=[Forms]![QCRecords]![cboDate])
AND ((QCRecord.AssayCode)=[Forms]![QCRecords]![cboAssay])
AND ((QCRecord.LotNo)=[Forms]![QCRecords]![cboLot])
AND (([Forms]![QCRecords]![cboBinder] Is Null))

OR (((QCRecord.QCDate)=[Forms]![QCRecords]![cboDate])
AND ((QCRecord.AssayCode)=[Forms]![QCRecords]![cboAssay])
AND (([Forms]![QCRecords]![cboLot] Is Null)
AND (([Forms]![QCRecords]![cboBinder] Is Null))

OR (((QCRecord.QCDate)=[Forms]![QCRecords]![cboDate])
AND (([Forms]![QCRecords]![cboAssay] Is Null)
AND (([Forms]![QCRecords]![cboLot] Is Null)
AND (([Forms]![QCRecords]![cboBinder] Is Null))

OR (((QCRecord.QCDate)=[Forms]![QCRecords]![cboDate])
AND (([Forms]![QCRecords]![cboAssay] Is Null)
AND ((QCRecord.LotNo)=[Forms]![QCRecords]![cboLot])
AND (([Forms]![QCRecords]![cboBinder] Is Null))

OR (((QCRecord.QCDate)=[Forms]![QCRecords]![cboDate])
AND ((QCRecord.AssayCode)=[Forms]![QCRecords]![cboAssay])
AND (([Forms]![QCRecords]![cboLot] Is Null)
AND ((QCRecord.BinderNo)=[Forms]![QCRecords]![cboBinder].[column](1)))

OR (((QCRecord.QCDate)=[Forms]![QCRecords]![cboDate])
AND (([Forms]![QCRecords]![cboAssay] Is Null)
AND (([Forms]![QCRecords]![cboLot] Is Null)
AND ((QCRecord.BinderNo)=[Forms]![QCRecords]![cboBinder].[column](1)))

OR (((QCRecord.QCDate)=[Forms]![QCRecords]![cboDate])
AND (([Forms]![QCRecords]![cboAssay] Is Null)
AND ((QCRecord.LotNo)=[Forms]![QCRecords]![cboLot])
AND ((QCRecord.BinderNo)=[Forms]![QCRecords]![cboBinder].[column](1)))

OR ((([Forms]![QCRecords]![cboDate] Is Null)
AND ((QCRecord.AssayCode)=[Forms]![QCRecords]![cboAssay])
AND ((QCRecord.LotNo)=[Forms]![QCRecords]![cboLot])
AND ((QCRecord.BinderNo)=[Forms]![QCRecords]![cboBinder].[column](1)))

OR ((([Forms]![QCRecords]![cboDate] Is Null)
AND (([Forms]![QCRecords]![cboAssay] Is Null)
AND ((QCRecord.LotNo)=[Forms]![QCRecords]![cboLot])
AND ((QCRecord.BinderNo)=[Forms]![QCRecords]![cboBinder].[column](1)))

OR ((([Forms]![QCRecords]![cboDate] Is Null)
AND (([Forms]![QCRecords]![cboAssay] Is Null)
AND (([Forms]![QCRecords]![cboLot] Is Null)
AND ((QCRecord.BinderNo)=[Forms]![QCRecords]![cboBinder].[column](1)))

OR ((([Forms]![QCRecords]![cboDate] Is Null)
AND ((QCRecord.AssayCode)=[Forms]![QCRecords]![cboAssay])
AND (([Forms]![QCRecords]![cboLot] Is Null)
AND ((QCRecord.BinderNo)=[Forms]![QCRecords]![cboBinder].[column](1)))

OR ((([Forms]![QCRecords]![cboDate] Is Null)
AND ((QCRecord.AssayCode)=[Forms]![QCRecords]![cboAssay])
AND ((QCRecord.LotNo)=[Forms]![QCRecords]![cboLot])
AND (([Forms]![QCRecords]![cboBinder] Is Null))

OR ((([Forms]![QCRecords]![cboDate] Is Null)
AND (([Forms]![QCRecords]![cboAssay] Is Null)
AND ((QCRecord.LotNo)=[Forms]![QCRecords]![cboLot])
AND ((QCRecord.BinderNo)=[Forms]![QCRecords]![cboBinder].[column](1)))

OR ((([Forms]![QCRecords]![cboDate] Is Null)
AND ((QCRecord.AssayCode)=[Forms]![QCRecords]![cboAssay])
AND (([Forms]![QCRecords]![cboLot] Is Null)
AND (([Forms]![QCRecords]![cboBinder] Is Null));

 

[NG]

Hi,

I think that it will be much easier to build the SQL expression using a
little VBA, just based on the filled in boxes.

Someting like:

Dim strFilter As String

If Len(Me.cboMyCombo1) > 0 Then 'this for combo with text values
strFilter = strFilter & " and MyField1 = """ & Me.cboMyCombo1 & """"
End If
If Me.cboMyCombo > 0 Then 'this for combo with number values
strFilter = strFilter & " and MyField2= " & Me.cboMyCombo
End If

...................


If Len(strFilter) > 0 Then
strFilter = " 1 = 1 " & strFilter
End If
GetFilterString = strFilter

--
Kind regards
Noëlla

Hi there,
I have 5 combo boxes and I need to be able to choose 1 or all, and the
relevant info show up on my form.
So if I pick 1 and nothing in the rest, show results for just that 1 combo.
If I choose combo 1, then combo 2 will show results from combo 1 and null the
rest. Hope you know what I mean.

 

[vanderghast]

While totally logical, the problem is maybe due to the excessive number of
OR. Can also be due to unmatched ( ) .



Can you try:


SELECT DISTINCT QCRecord.SampleID
FROM QCRecord
WHERE (((([Forms]![QCRecords]![cboDate] NOT IS NULL ) IMP
QCRecord.QCDate)=[Forms]![QCRecords]![cboDate])
AND ((([Forms]![QCRecords]![cboAssay] NOT IS NULL ) IMP
QCRecord.AssayCode)=[Forms]![QCRecords]![cboAssay])
AND ((([Forms]![QCRecords]![cboLot] NOT IS NULL ) IMP
QCRecord.LotNo)=[Forms]![QCRecords]![cboLot])
AND ((([Forms]![QCRecords]![[cboBinder].[column](1) NOT IS NULL ) IMP
QCRecord.BinderNo)=[Forms]![QCRecords]![cboBinder].[column](1)))



if I have the ( ) matches ok. Basically, I just used IMP rather than OR :

(NOT a IS NULL) IMP x= a

( can be read as: if a is not null, then enforce x = a )

instead of

x = a OR a IS NULL



Vanderghast, Access MVP

Hi there,
I have 5 combo boxes and I need to be able to choose 1 or all, and the
relevant info show up on my form.
So if I pick 1 and nothing in the rest, show results for just that 1
combo.
If I choose combo 1, then combo 2 will show results from combo 1 and null
the
rest. Hope you know what I mean.
This was the only way I could figure to do it and I am getting an error
when
I try and view the query in Design view. Anyone know of a better way to do
it
or see why I am getting this error? Thanks!

SELECT DISTINCT QCRecord.SampleID
FROM QCRecord
WHERE (((QCRecord.QCDate)=[Forms]![QCRecords]![cboDate])
AND ((QCRecord.AssayCode)=[Forms]![QCRecords]![cboAssay])
AND ((QCRecord.LotNo)=[Forms]![QCRecords]![cboLot])
AND ((QCRecord.BinderNo)=[Forms]![QCRecords]![cboBinder].[column](1)))

OR ((([Forms]![QCRecords]![cboDate] Is Null)
AND (([Forms]![QCRecords]![cboAssay] Is Null)
AND (([Forms]![QCRecords]![cboLot] Is Null)
AND (([Forms]![QCRecords]![cboBinder] Is Null))

OR (((QCRecord.QCDate)=[Forms]![QCRecords]![cboDate])
AND ((QCRecord.AssayCode)=[Forms]![QCRecords]![cboAssay])
AND ((QCRecord.LotNo)=[Forms]![QCRecords]![cboLot])
AND (([Forms]![QCRecords]![cboBinder] Is Null))

OR (((QCRecord.QCDate)=[Forms]![QCRecords]![cboDate])
AND ((QCRecord.AssayCode)=[Forms]![QCRecords]![cboAssay])
AND (([Forms]![QCRecords]![cboLot] Is Null)
AND (([Forms]![QCRecords]![cboBinder] Is Null))

OR (((QCRecord.QCDate)=[Forms]![QCRecords]![cboDate])
AND (([Forms]![QCRecords]![cboAssay] Is Null)
AND (([Forms]![QCRecords]![cboLot] Is Null)
AND (([Forms]![QCRecords]![cboBinder] Is Null))

OR (((QCRecord.QCDate)=[Forms]![QCRecords]![cboDate])
AND (([Forms]![QCRecords]![cboAssay] Is Null)
AND ((QCRecord.LotNo)=[Forms]![QCRecords]![cboLot])
AND (([Forms]![QCRecords]![cboBinder] Is Null))

OR (((QCRecord.QCDate)=[Forms]![QCRecords]![cboDate])
AND ((QCRecord.AssayCode)=[Forms]![QCRecords]![cboAssay])
AND (([Forms]![QCRecords]![cboLot] Is Null)
AND ((QCRecord.BinderNo)=[Forms]![QCRecords]![cboBinder].[column](1)))

OR (((QCRecord.QCDate)=[Forms]![QCRecords]![cboDate])
AND (([Forms]![QCRecords]![cboAssay] Is Null)
AND (([Forms]![QCRecords]![cboLot] Is Null)
AND ((QCRecord.BinderNo)=[Forms]![QCRecords]![cboBinder].[column](1)))

OR (((QCRecord.QCDate)=[Forms]![QCRecords]![cboDate])
AND (([Forms]![QCRecords]![cboAssay] Is Null)
AND ((QCRecord.LotNo)=[Forms]![QCRecords]![cboLot])
AND ((QCRecord.BinderNo)=[Forms]![QCRecords]![cboBinder].[column](1)))

OR ((([Forms]![QCRecords]![cboDate] Is Null)
AND ((QCRecord.AssayCode)=[Forms]![QCRecords]![cboAssay])
AND ((QCRecord.LotNo)=[Forms]![QCRecords]![cboLot])
AND ((QCRecord.BinderNo)=[Forms]![QCRecords]![cboBinder].[column](1)))

OR ((([Forms]![QCRecords]![cboDate] Is Null)
AND (([Forms]![QCRecords]![cboAssay] Is Null)
AND ((QCRecord.LotNo)=[Forms]![QCRecords]![cboLot])
AND ((QCRecord.BinderNo)=[Forms]![QCRecords]![cboBinder].[column](1)))

OR ((([Forms]![QCRecords]![cboDate] Is Null)
AND (([Forms]![QCRecords]![cboAssay] Is Null)
AND (([Forms]![QCRecords]![cboLot] Is Null)
AND ((QCRecord.BinderNo)=[Forms]![QCRecords]![cboBinder].[column](1)))

OR ((([Forms]![QCRecords]![cboDate] Is Null)
AND ((QCRecord.AssayCode)=[Forms]![QCRecords]![cboAssay])
AND (([Forms]![QCRecords]![cboLot] Is Null)
AND ((QCRecord.BinderNo)=[Forms]![QCRecords]![cboBinder].[column](1)))

OR ((([Forms]![QCRecords]![cboDate] Is Null)
AND ((QCRecord.AssayCode)=[Forms]![QCRecords]![cboAssay])
AND ((QCRecord.LotNo)=[Forms]![QCRecords]![cboLot])
AND (([Forms]![QCRecords]![cboBinder] Is Null))

OR ((([Forms]![QCRecords]![cboDate] Is Null)
AND (([Forms]![QCRecords]![cboAssay] Is Null)
AND ((QCRecord.LotNo)=[Forms]![QCRecords]![cboLot])
AND ((QCRecord.BinderNo)=[Forms]![QCRecords]![cboBinder].[column](1)))

OR ((([Forms]![QCRecords]![cboDate] Is Null)
AND ((QCRecord.AssayCode)=[Forms]![QCRecords]![cboAssay])
AND (([Forms]![QCRecords]![cboLot] Is Null)
AND (([Forms]![QCRecords]![cboBinder] Is Null));

 

[gmazza via AccessMonster.com]

Thanks for your reply.
Can you breakdown your code a little?
I don't understand this part:

If Len(strFilter) > 0 Then
strFilter = " 1 = 1 " & strFilter
End If
GetFilterString = strFilter

and where does the other part of the code go? after update of the combo's?

Thanks!

 

[gmazza via AccessMonster.com]

Sounds interesting, hope it works!
Noticed 1 place where you had 2 [[ in a row and I deleted that, checked all
the brackets (), and it all seems to look good, but I got a syntax error
still. I can't figure it out. Maybe its the column(1) part?

While totally logical, the problem is maybe due to the excessive number of
OR. Can also be due to unmatched ( ) .

Can you try:

SELECT DISTINCT QCRecord.SampleID
FROM QCRecord
WHERE (((([Forms]![QCRecords]![cboDate] NOT IS NULL ) IMP
QCRecord.QCDate)=[Forms]![QCRecords]![cboDate])
AND ((([Forms]![QCRecords]![cboAssay] NOT IS NULL ) IMP
QCRecord.AssayCode)=[Forms]![QCRecords]![cboAssay])
AND ((([Forms]![QCRecords]![cboLot] NOT IS NULL ) IMP
QCRecord.LotNo)=[Forms]![QCRecords]![cboLot])
AND ((([Forms]![QCRecords]![[cboBinder].[column](1) NOT IS NULL ) IMP
QCRecord.BinderNo)=[Forms]![QCRecords]![cboBinder].[column](1)))

if I have the ( ) matches ok. Basically, I just used IMP rather than OR :

(NOT a IS NULL) IMP x= a

( can be read as: if a is not null, then enforce x = a )

instead of

x = a OR a IS NULL

Vanderghast, Access MVP

Hi there,
I have 5 combo boxes and I need to be able to choose 1 or all, and the

[quoted text clipped - 91 lines]

AND (([Forms]![QCRecords]![cboLot] Is Null)
AND (([Forms]![QCRecords]![cboBinder] Is Null));

 

[NG]

Hi,

This was of course only an example bit of code, to explain further:

first you build up a criterium string adding a criterium for each combobox
that is filled in, so for 6 comboboxes you have 6 if tests. Because you
never can tell in advance which combo is filled or not, you proceed each
piece with the AND statement.
Of course you cannot start a criterium string with "AND" so you have to drop
the first AND by dropping the first 4 chars or put a criterium statement in
front which always returns true, in my case the test 1=1.
In your case you also have to put a WHERE statement in front, so you replace
the first AND with a WHERE or just add the WHERE in the final part as:

If Len(strFilter) > 0 Then
strFilter = "WHERE 1 = 1 " & strFilter
End If

Then you use this string to complete the complete command text .

strCommandText = "SELECT DISTINCT QCRecord.SampleID FROM QCRecord " &
strFilter

You can then use this string to build the command text for an ADODB command
object, something like :

Dim cmdMyQuery As New ADODB.Command


cmdMyQuery .ActiveConnection = CurrentProject.Connection
cmdMyQuery .CommandType = adCmdText
cmdMyQuery .CommandText = strCommandText

You can check out the access object model to complete and use this.

The best place to do this is, in my opinion, the OnClick event of a command
button, the user can click after he/she has set all combo boxes.
Hope this helps a bit

 

[vanderghast]

Indeed, bad cut and paste from my part. And removing the [column](1) is also
more standard. I assume column(1) of the combo box is the 'bound' column of
your combo box, the column returning the "value" of the selected row from
the combo box. If you remove it, still have an error?

Vanderghast, Access MVP

Sounds interesting, hope it works!
Noticed 1 place where you had 2 [[ in a row and I deleted that, checked
all
the brackets (), and it all seems to look good, but I got a syntax error
still. I can't figure it out. Maybe its the column(1) part?

While totally logical, the problem is maybe due to the excessive number of
OR. Can also be due to unmatched ( ) .

Can you try:

SELECT DISTINCT QCRecord.SampleID
FROM QCRecord
WHERE (((([Forms]![QCRecords]![cboDate] NOT IS NULL ) IMP
QCRecord.QCDate)=[Forms]![QCRecords]![cboDate])
AND ((([Forms]![QCRecords]![cboAssay] NOT IS NULL ) IMP
QCRecord.AssayCode)=[Forms]![QCRecords]![cboAssay])
AND ((([Forms]![QCRecords]![cboLot] NOT IS NULL ) IMP
QCRecord.LotNo)=[Forms]![QCRecords]![cboLot])
AND ((([Forms]![QCRecords]![[cboBinder].[column](1) NOT IS NULL ) IMP
QCRecord.BinderNo)=[Forms]![QCRecords]![cboBinder].[column](1)))

if I have the ( ) matches ok. Basically, I just used IMP rather than OR :

(NOT a IS NULL) IMP x= a

( can be read as: if a is not null, then enforce x = a )

instead of

x = a OR a IS NULL

Vanderghast, Access MVP

Hi there,
I have 5 combo boxes and I need to be able to choose 1 or all, and the

[quoted text clipped - 91 lines]

AND (([Forms]![QCRecords]![cboLot] Is Null)
AND (([Forms]![QCRecords]![cboBinder] Is Null));

 

[gmazza via AccessMonster.com]

Ya, just having this still produces a syntax error:

SELECT DISTINCT QCRecord.SampleID
FROM QCRecord
WHERE ((([Forms]![QCRecords]![cboDate] NOT IS NULL ) IMP
QCRecord.QCDate=[Forms]![QCRecords]![cboDate]))

Indeed, bad cut and paste from my part. And removing the [column](1) is also
more standard. I assume column(1) of the combo box is the 'bound' column of
your combo box, the column returning the "value" of the selected row from
the combo box. If you remove it, still have an error?

Vanderghast, Access MVP

Sounds interesting, hope it works!
Noticed 1 place where you had 2 [[ in a row and I deleted that, checked

[quoted text clipped - 35 lines]

AND (([Forms]![QCRecords]![cboLot] Is Null)
AND (([Forms]![QCRecords]![cboBinder] Is Null));

 

[vanderghast]

Ah, sit eems the position of NOT is important, try:

WHERE (NOT FORMS!QCrecords!cboDate IS NULL) IMP QCDate =
FORMS!QCRecords!cboDate

instead of the previous suggestion:

WHERE (FORMS!QCrecords!cboDate NOT IS NULL) IMP QCDate =
FORMS!QCRecords!cboDate


Note that the [ ] are not required, here, and removing them increase
readibility. It seems you can even remove the ( ), the order of execution
would be fine, but since that, I never really bother to remember that order
of execution, I stick with keeping ( ) around.


Vanderghast, Access MVP

Ya, just having this still produces a syntax error:

SELECT DISTINCT QCRecord.SampleID
FROM QCRecord
WHERE ((([Forms]![QCRecords]![cboDate] NOT IS NULL ) IMP
QCRecord.QCDate=[Forms]![QCRecords]![cboDate]))

Indeed, bad cut and paste from my part. And removing the [column](1) is
also
more standard. I assume column(1) of the combo box is the 'bound' column
of
your combo box, the column returning the "value" of the selected row from
the combo box. If you remove it, still have an error?

Vanderghast, Access MVP

Sounds interesting, hope it works!
Noticed 1 place where you had 2 [[ in a row and I deleted that, checked

[quoted text clipped - 35 lines]

AND (([Forms]![QCRecords]![cboLot] Is Null)
AND (([Forms]![QCRecords]![cboBinder] Is Null));

 

[gmazza via AccessMonster.com]

Syntax error gone. Will let you know if it works.Thanks!!

Ah, sit eems the position of NOT is important, try:

WHERE (NOT FORMS!QCrecords!cboDate IS NULL) IMP QCDate =
FORMS!QCRecords!cboDate

instead of the previous suggestion:

WHERE (FORMS!QCrecords!cboDate NOT IS NULL) IMP QCDate =
FORMS!QCRecords!cboDate

Note that the [ ] are not required, here, and removing them increase
readibility. It seems you can even remove the ( ), the order of execution
would be fine, but since that, I never really bother to remember that order
of execution, I stick with keeping ( ) around.

Vanderghast, Access MVP

Ya, just having this still produces a syntax error:

[quoted text clipped - 17 lines]

AND (([Forms]![QCRecords]![cboLot] Is Null)
AND (([Forms]![QCRecords]![cboBinder] Is Null));

 

[gmazza via AccessMonster.com]

didn't seem to work. The error is gone, but when I click the combo box for
SampleID, it shows nothing when there are 4 values in the table. If all other
combo boxes are empty, it should show the values in the Sample table.

Ah, sit eems the position of NOT is important, try:

WHERE (NOT FORMS!QCrecords!cboDate IS NULL) IMP QCDate =
FORMS!QCRecords!cboDate

instead of the previous suggestion:

WHERE (FORMS!QCrecords!cboDate NOT IS NULL) IMP QCDate =
FORMS!QCRecords!cboDate

Note that the [ ] are not required, here, and removing them increase
readibility. It seems you can even remove the ( ), the order of execution
would be fine, but since that, I never really bother to remember that order
of execution, I stick with keeping ( ) around.

Vanderghast, Access MVP

Ya, just having this still produces a syntax error:

[quoted text clipped - 17 lines]

AND (([Forms]![QCRecords]![cboLot] Is Null)
AND (([Forms]![QCRecords]![cboBinder] Is Null));

 

[gmazza via AccessMonster.com]

So basically I simplified it for now, and will add values later, once I get
the first 2 combo boxes working.
My first combo box is based on the SampleID from the Sample table.
My 2nd combo box is based on the BinderNo from the Binder table.
The query for my 1st combo is this:
SELECT DISTINCT Sample.SampleID
FROM Sample, Binder
WHERE ((((Not [Forms]![QCRecords]![cboBinder] Is Null)
Imp [Binder].[BinderNo])=[Forms]![QCRecords]![cboBinder]));

The problem is its not all from 1 table anymore, like it was before. So maybe
the query for the combo needs to change now as the error I get is saying
complex expression.

Whta do you think? Do you see why it would be erroring now?

Ah, sit eems the position of NOT is important, try:

WHERE (NOT FORMS!QCrecords!cboDate IS NULL) IMP QCDate =
FORMS!QCRecords!cboDate

instead of the previous suggestion:

WHERE (FORMS!QCrecords!cboDate NOT IS NULL) IMP QCDate =
FORMS!QCRecords!cboDate

Note that the [ ] are not required, here, and removing them increase
readibility. It seems you can even remove the ( ), the order of execution
would be fine, but since that, I never really bother to remember that order
of execution, I stick with keeping ( ) around.

Vanderghast, Access MVP

Ya, just having this still produces a syntax error:

[quoted text clipped - 17 lines]

AND (([Forms]![QCRecords]![cboLot] Is Null)
AND (([Forms]![QCRecords]![cboBinder] Is Null));