Like statement returning all values if blank

  • Thread starter Thread starter PC
  • Start date Start date
P

PC

Hi I'm running a query where one of the criteria contains a like statement.
I'm placing the criteria directly in the query design window.

e.g. The original criteria for the query would appear as: Like
[Forms]![FormFame]![FieldName] & "*". The user can enter all or part of the
word they are searching for and this works fine.

My problem is if the field is left blank all results are returned which I
don't want.

If I try IIf(Not IsNull([Forms]![FormName]![FieldName]), Like
[Forms]![FormName]![FieldName] & "*") nothing is returned whether text is
entered or not.

I'm sure I missing something simple enough but and advise would be
appreciated.

Regards

...pc
 
If nothing gets entered by the user, you will have
everything returned since you have no selection criteria.
 
Yes when I have the first criteria mentioned below everything is returned
when the field in the form is left blank But what I want is for nothing to
be returned unless Something is entered. I tried the second criteria below
to which says "If the field is blank - Do nothing, else do something" this
returns nothing even when something is entered in the field.

Any ideas.

Regards

...pc


If nothing gets entered by the user, you will have
everything returned since you have no selection criteria.
-----Original Message-----
Hi I'm running a query where one of the criteria contains a like statement.
I'm placing the criteria directly in the query design window.

e.g. The original criteria for the query would appear as: Like
[Forms]![FormFame]![FieldName] & "*". The user can enter all or part of the
word they are searching for and this works fine.

My problem is if the field is left blank all results are returned which I
don't want.

If I try IIf(Not IsNull([Forms]![FormName]![FieldName]), Like
[Forms]![FormName]![FieldName] & "*") nothing is returned whether text is
entered or not.

I'm sure I missing something simple enough but and advise would be
appreciated.

Regards

...pc


.
Click to expand...
Click to expand...
 
One method would be to force a value that will never exist in the table if the
criteria is left blank.

Like NZ([Forms]![FormFame]![ControlName],"zx^^(--_)") & "*"

or use criteria like:

Like [Forms]![FormFame]![ControlName] & "*" AND [Forms]![FormFame]![ControlName]
Is Not Null
 
Thanks John,

That one was annoying me.

Paul


John Spencer (MVP) said:
One method would be to force a value that will never exist in the table if the
criteria is left blank.

Like NZ([Forms]![FormFame]![ControlName],"zx^^(--_)") & "*"

or use criteria like:

Like [Forms]![FormFame]![ControlName] & "*" AND [Forms]![FormFame]![ControlName]
Is Not Null
Hi I'm running a query where one of the criteria contains a like statement.
I'm placing the criteria directly in the query design window.

e.g. The original criteria for the query would appear as: Like
[Forms]![FormFame]![FieldName] & "*". The user can enter all or part of the
word they are searching for and this works fine.

My problem is if the field is left blank all results are returned which I
don't want.

If I try IIf(Not IsNull([Forms]![FormName]![FieldName]), Like
[Forms]![FormName]![FieldName] & "*") nothing is returned whether text is
entered or not.

I'm sure I missing something simple enough but and advise would be
appreciated.

Regards

..pc
Click to expand...
Click to expand...
 
Back
Top