Null Value & Non Null Values

[pleg12345]

Within a parameter query,i have many user input criterias.

Im getting very confused so could somebody please explain:

If a user entry is not made, then the records should display both null values and non null values.

If a user entry is made then it should just display the records that match that criteria.

How do i say that? Ive tried loads of different combinations using the "like" command, but im going round in circles!

 

[MacCaillean]

Hi pleg12345

Use the syntax

Like [Your Parameter] & "*"

.

HTH

Mac

 

[pleg12345]

Ive tried that and it doesnt return null values!

 

[MacCaillean]

Hi pleg12345

If you remove all parameters from the query does it return all null and non-null values?

 

[pleg12345]

yes, when i remove the parameter it shows both null and non null values.

 

[MakeItSo]

Include both in your where clause:
Where ([field] Like [parameter] & "*" or [field] is null)

This should do it.

MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[pleg12345]

dont mean to sound stupid but where do i input the where clause?

in the criteria part of the design view of the query?
and is this as well the original parameter?

 

[MakeItSo]

Correct.
Put Like [parameter] & "*"
in the criteria line
and
is null
in the line below ("or&quot

 

[pleg12345]

im recieving an error, undefined function "where" in expression

 

[MakeItSo]

Sorry, I guess I wasn't clear enough:
the line "Where ([field] Like [parameter] & "*" or [field] is null)"
is only valid in query SQL, not in query design.
In query designer, you must only put
Like [parameter] & "*"
in the criteria line
and
is null
in the line below ("or&quot

 

[pleg12345]

well i think that worked for that field, but unfortuantely it has effected my other parameter searches.

for e.g if in custname i type in PAUL it no longer finds all the pauls. ???????????

 

[MakeItSo]

That is caused by some strange behavior of query designer:

When you enter additional criteria in the lines und the main criteria, it gets included as logically OR to the entire criteria.

Perhaps it is better to leave the criteria in one line, else you must double all others.
Put this in one line instead of splitting into two:
(Like [parameter] & "*&quot Or Null