If null skip/ignore

[Guest]

Im a little stuck doing a query.

In one of the tables the name of the user can be in any one of three fields,
and i have been asked to create a query where a popup appears to enter the
name of person to search for in each three fields. e.g. one popup for "auth
name", one for "imp name" and one for "test name".

however if nothing/null is entered in one of these popups it is skipped.
e.g. if nothing is entered for "auth name" it will show all fields. unlike if
i tell it "[auth name] is null"

Im trying to use IIF, but i cannot get this to work

My code is

WHERE (((ChangeDetails.Authorised)=[auth name]) AND
((ChangeDetails.Tested)=[test name]) AND ((ChangeDetails.Implemented)=[imp
name])) OR ((iif [auth name] Is Null) AND (iif [test name] Is Null) AND (iif
[imp name] Is Null));

Please help.

 

[Michel Walsh]

Remove the iif-s words. The syntax is just

( [test name] IS NULL )


no need to prefix the statement with iif, it is explicitly implied, in SQL,
when you use IS, or any comparison.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks this works to a point, now im finding if i leave the first two popups
blank and fill in the third popup it does not give any results even though i
am giving an imput that has got results

Remove the iif-s words. The syntax is just

( [test name] IS NULL )


no need to prefix the statement with iif, it is explicitly implied, in SQL,
when you use IS, or any comparison.



Hoping it may help,
Vanderghast, Access MVP

Im a little stuck doing a query.

In one of the tables the name of the user can be in any one of three
fields,
and i have been asked to create a query where a popup appears to enter the
name of person to search for in each three fields. e.g. one popup for
"auth
name", one for "imp name" and one for "test name".

however if nothing/null is entered in one of these popups it is skipped.
e.g. if nothing is entered for "auth name" it will show all fields. unlike
if
i tell it "[auth name] is null"

Im trying to use IIF, but i cannot get this to work

My code is

WHERE (((ChangeDetails.Authorised)=[auth name]) AND
((ChangeDetails.Tested)=[test name]) AND ((ChangeDetails.Implemented)=[imp
name])) OR ((iif [auth name] Is Null) AND (iif [test name] Is Null) AND
(iif
[imp name] Is Null));

Please help.

 

[Michel Walsh]

It is probably because of the way the criterion is built.



(ChangeDetails.Authorised IS NULL OR ChangeDetails.Authorised = [auth
name])

AND

( ChangeDetails.Tested IS NULL OR ChangeDetails.Tested = [test name] )

AND

( ChangeDetails.Implemented IS NULL OR ChangeDetails.Implemented = [imp
name] )



would probably be preferable.



Vanderghast, Access MVP

Thanks this works to a point, now im finding if i leave the first two
popups
blank and fill in the third popup it does not give any results even though
i
am giving an imput that has got results

Remove the iif-s words. The syntax is just

( [test name] IS NULL )


no need to prefix the statement with iif, it is explicitly implied, in
SQL,
when you use IS, or any comparison.



Hoping it may help,
Vanderghast, Access MVP

Im a little stuck doing a query.

In one of the tables the name of the user can be in any one of three
fields,
and i have been asked to create a query where a popup appears to enter
the
name of person to search for in each three fields. e.g. one popup for
"auth
name", one for "imp name" and one for "test name".

however if nothing/null is entered in one of these popups it is
skipped.
e.g. if nothing is entered for "auth name" it will show all fields.
unlike
if
i tell it "[auth name] is null"

Im trying to use IIF, but i cannot get this to work

My code is

WHERE (((ChangeDetails.Authorised)=[auth name]) AND
((ChangeDetails.Tested)=[test name]) AND
((ChangeDetails.Implemented)=[imp
name])) OR ((iif [auth name] Is Null) AND (iif [test name] Is Null) AND
(iif
[imp name] Is Null));

Please help.

 

[Guest]

Thanks for that Michael,

unfortunately now if i enter one or two of the user defined criteria, it is
only bringing up the records where all three [auth name], [test name] and
[imp name] are blank. and when i fill in all three of the user defined
criteria, it shows the records where all three match AND where all three are
blank.

Which would be fine (could just edit all the blank records), or just make
three different querys (one for each set of []'s) but ive been asked to make
it so it can pull off different users doing different work on different
customers, as the auth name is customers, the imp name is users and the test
name is the quality checkers.

im trying to figgure out if there is a way to have the query to be able to do

"[auth name] or if [auth name] is null ignore [auth name]"

for all three of these, but i have not got a clue how to get access to do
that.

It is probably because of the way the criterion is built.



(ChangeDetails.Authorised IS NULL OR ChangeDetails.Authorised = [auth
name])

AND

( ChangeDetails.Tested IS NULL OR ChangeDetails.Tested = [test name] )

AND

( ChangeDetails.Implemented IS NULL OR ChangeDetails.Implemented = [imp
name] )



would probably be preferable.



Vanderghast, Access MVP

Thanks this works to a point, now im finding if i leave the first two
popups
blank and fill in the third popup it does not give any results even though
i
am giving an imput that has got results

Remove the iif-s words. The syntax is just

( [test name] IS NULL )


no need to prefix the statement with iif, it is explicitly implied, in
SQL,
when you use IS, or any comparison.



Hoping it may help,
Vanderghast, Access MVP


Im a little stuck doing a query.

In one of the tables the name of the user can be in any one of three
fields,
and i have been asked to create a query where a popup appears to enter
the
name of person to search for in each three fields. e.g. one popup for
"auth
name", one for "imp name" and one for "test name".

however if nothing/null is entered in one of these popups it is
skipped.
e.g. if nothing is entered for "auth name" it will show all fields.
unlike
if
i tell it "[auth name] is null"

Im trying to use IIF, but i cannot get this to work

My code is

WHERE (((ChangeDetails.Authorised)=[auth name]) AND
((ChangeDetails.Tested)=[test name]) AND
((ChangeDetails.Implemented)=[imp
name])) OR ((iif [auth name] Is Null) AND (iif [test name] Is Null) AND
(iif
[imp name] Is Null));

Please help.

 

[Michel Walsh]

Sure, I got it wrong, it is the parameter that should be tested as null, not
the field:



([auth name] IS NULL OR ChangeDetails.Authorised = [auth
name])

AND

( [test name] IS NULL OR ChangeDetails.Tested = [test name] )

AND

( [imp name] IS NULL OR ChangeDetails.Implemented = [imp
name] )




should be better.

Vanderghast, Access MVP

Thanks for that Michael,

unfortunately now if i enter one or two of the user defined criteria, it
is
only bringing up the records where all three [auth name], [test name] and
[imp name] are blank. and when i fill in all three of the user defined
criteria, it shows the records where all three match AND where all three
are
blank.

Which would be fine (could just edit all the blank records), or just make
three different querys (one for each set of []'s) but ive been asked to
make
it so it can pull off different users doing different work on different
customers, as the auth name is customers, the imp name is users and the
test
name is the quality checkers.

im trying to figgure out if there is a way to have the query to be able to
do

"[auth name] or if [auth name] is null ignore [auth name]"

for all three of these, but i have not got a clue how to get access to do
that.

It is probably because of the way the criterion is built.



(ChangeDetails.Authorised IS NULL OR ChangeDetails.Authorised = [auth
name])

AND

( ChangeDetails.Tested IS NULL OR ChangeDetails.Tested = [test name] )

AND

( ChangeDetails.Implemented IS NULL OR ChangeDetails.Implemented = [imp
name] )



would probably be preferable.



Vanderghast, Access MVP

Thanks this works to a point, now im finding if i leave the first two
popups
blank and fill in the third popup it does not give any results even
though
i
am giving an imput that has got results

:

Remove the iif-s words. The syntax is just

( [test name] IS NULL )


no need to prefix the statement with iif, it is explicitly implied, in
SQL,
when you use IS, or any comparison.



Hoping it may help,
Vanderghast, Access MVP


Im a little stuck doing a query.

In one of the tables the name of the user can be in any one of three
fields,
and i have been asked to create a query where a popup appears to
enter
the
name of person to search for in each three fields. e.g. one popup
for
"auth
name", one for "imp name" and one for "test name".

however if nothing/null is entered in one of these popups it is
skipped.
e.g. if nothing is entered for "auth name" it will show all fields.
unlike
if
i tell it "[auth name] is null"

Im trying to use IIF, but i cannot get this to work

My code is

WHERE (((ChangeDetails.Authorised)=[auth name]) AND
((ChangeDetails.Tested)=[test name]) AND
((ChangeDetails.Implemented)=[imp
name])) OR ((iif [auth name] Is Null) AND (iif [test name] Is Null)
AND
(iif
[imp name] Is Null));

Please help.