Expression too complex

[odun]

Hello all,

I am experiencing this error:

"This expression is typed incorrectly or it is too complex to be evaluated….For example a numeric expression my contain too many complicated elements."

I think the error is as a result of this expression in my query, I have 9 fields with the expression below with different field names:

Like Forms!QBF_Form!Sales & "*" Or Forms!QBF_Form!Sales Is Null

The problem is when I close the query and open it, the expression multiplies to numerous rows and also creates additional fields for the last part of the expression. Is there an expression that I could use that would work okay.

I tried using this expression:
Like IIF (Forms!QBF_Form!Sales)="", "*", "*" & Forms!QBF_Form!Sales & "*"

It was working, but is no longer working..not sure why.

Do you have a similar expression that will achieve the same result but create additional criteria.

Very grateful!

 

[Jon K]

Like Forms!QBF_Form!Sales & "*" Or Forms!QBF_Form!Sales Is Null

The problem is when I close the query and open it, the expression multiplies to numerous rows and also creates additional fields for the last part of the expression.

That is a common problem which occurs when one puts something like one of the following expressions with an OR operator in the criteria row:-
Forms!FormName!ControlName OR Forms!FormName!ControlName Is Null

Like Forms!FormName!ControlName & "*" OR Forms!FormName!ControlName Is Null


To avoid the problem, you can put the criteria for each field in a new column in the query grid like this (replacing with the correct field name and form and control names):-
-----------------------------------
Field: [FieldName] Like Forms!FormName!ControlName & "*" OR Forms!FormName!ControlName Is Null

Show: uncheck

Criteria: True
-----------------------------------
This way, Access would leave the criteria for each field intact when the query is saved and you can easily add other criteria.

I tried using this expression:
Like IIF (Forms!QBF_Form!Sales)="", "*", "*" & Forms!QBF_Form!Sales & "*"

It was working, but is no longer working..not sure why.

The correct syntax is:-
Like IIf([Forms]![QBF_Form]![Sales] Is Null, "*", "*" & [Forms]![QBF_Form]![Sales] & "*")

But the LIKE operator cannot return null values. So it will not work if the field may contain null values.
.

 

[odun]

What do I do in the case of this expression:

Between Forms!QBF_Form!StartDate And Forms!QBF_Form!EndDate Or Forms!QBF_Form!StartDate Is Null

What expression do I put in the newly created field?

Thanks again.

 

[Jon K]

------------------------------
Field: [FieldName] Between Forms!QBF_Form!StartDate And Forms!QBF_Form!EndDate Or Forms!QBF_Form!StartDate Is Null

Show: uncheck

Criteria: True
------------------------------

When Forms!QBF_Form!StartDate is left blank, every record will be returned.
.

 

[odun]

Thanks you so so much, it worked perfectly