Report to bring back active field only

[Brendan]

Hi
I'm looking for some help please.
I have a table of contact details and a report that contains some of the
fields from the table. I need an embedded macro in the report that says
basically "only bring back a record into the report based on the condition
false". I've tried using IIf based on one of the fields, but all it does is
leave the field in the report blank but brings back the rest of the fields
from the same record. Hope this makes sense.
Thanks.

 

[Marshall Barton]

I have a table of contact details and a report that contains some of the
fields from the table. I need an embedded macro in the report that says
basically "only bring back a record into the report based on the condition
false".

Use a query as the report's record source. Set the query
Yes/No field's criteria to False.

 

[Brendan]

Hi Marsh
Thank you.

I have a table of customers (the table is called Referrals). One field is
"Assigned to" an internal staff member. I want my report to bring back all
customers that have not been ''assigned to", ie the field is still blank.

I have now tried building a query, and have tried numerous things but I
can't get it to work. I can get it to work based on True (here is the SQL):
SELECT Referrals.*
FROM Referrals
WHERE (((Referrals.[Referral Assigned To])<>"True"));

This obviously brings back all records that have been "assigned" but if I
put False, it returns nothing, even though there are customer records with no
"assigned to". I guess False is the wrong expression to use in this case, and
I've tried isNull and that didn't work either.

Any further advice is appreciated.

 

[Marshall Barton]

I have a table of customers (the table is called Referrals). One field is
"Assigned to" an internal staff member. I want my report to bring back all
customers that have not been ''assigned to", ie the field is still blank.

I have now tried building a query, and have tried numerous things but I
can't get it to work. I can get it to work based on True (here is the SQL):
SELECT Referrals.*
FROM Referrals
WHERE (((Referrals.[Referral Assigned To])<>"True"));

This obviously brings back all records that have been "assigned" but if I
put False, it returns nothing, even though there are customer records with no
"assigned to". I guess False is the wrong expression to use in this case, and
I've tried isNull and that didn't work either.

What is the data type of the [Referral Assigned To] field in
the table?

If it is a Yes/No type field, then its value will be either
True or False (no quotes), not the text "True" or "False".
Because the value of the field is either True or False, you
do not have to compare it to True and can use the simpler
conditions if you like:
WHERE Referrals.[Referral Assigned To]
or
WHERE Not Referrals.[Referral Assigned To]

If it is a number type field (used as a foreign key?), then
it either has a value or it is null. In this case try
using:
WHERE Referrals.[Referral Assigned To] Is Null
or
WHERE Referrals.[Referral Assigned To] Is Not Null

 

[Brendan]

The field is a text field. It's the initials of the staff member, which is
populated from a combo box in a form. Thank you
--
Brendan
Adelaide, Australia
As always I have searched the forum first before posting my question

I have a table of customers (the table is called Referrals). One field is
"Assigned to" an internal staff member. I want my report to bring back all
customers that have not been ''assigned to", ie the field is still blank.

I have now tried building a query, and have tried numerous things but I
can't get it to work. I can get it to work based on True (here is the SQL):
SELECT Referrals.*
FROM Referrals
WHERE (((Referrals.[Referral Assigned To])<>"True"));

This obviously brings back all records that have been "assigned" but if I
put False, it returns nothing, even though there are customer records with no
"assigned to". I guess False is the wrong expression to use in this case, and
I've tried isNull and that didn't work either.

What is the data type of the [Referral Assigned To] field in
the table?

If it is a Yes/No type field, then its value will be either
True or False (no quotes), not the text "True" or "False".
Because the value of the field is either True or False, you
do not have to compare it to True and can use the simpler
conditions if you like:
WHERE Referrals.[Referral Assigned To]
or
WHERE Not Referrals.[Referral Assigned To]

If it is a number type field (used as a foreign key?), then
it either has a value or it is null. In this case try
using:
WHERE Referrals.[Referral Assigned To] Is Null
or
WHERE Referrals.[Referral Assigned To] Is Not Null

 

[Brendan]

G'day
I have just cracked it. Is Null is what has worked. Thank you for your time
and help
--
Brendan
Adelaide, Australia
Office Professional 2007 on Windows XP
I always search the forum before posting a question

The field is a text field. It's the initials of the staff member, which is
populated from a combo box in a form. Thank you
--
Brendan
Adelaide, Australia
As always I have searched the forum first before posting my question

I have a table of customers (the table is called Referrals). One field is
"Assigned to" an internal staff member. I want my report to bring back all
customers that have not been ''assigned to", ie the field is still blank.

I have now tried building a query, and have tried numerous things but I
can't get it to work. I can get it to work based on True (here is the SQL):
SELECT Referrals.*
FROM Referrals
WHERE (((Referrals.[Referral Assigned To])<>"True"));

This obviously brings back all records that have been "assigned" but if I
put False, it returns nothing, even though there are customer records with no
"assigned to". I guess False is the wrong expression to use in this case, and
I've tried isNull and that didn't work either.

What is the data type of the [Referral Assigned To] field in
the table?

If it is a Yes/No type field, then its value will be either
True or False (no quotes), not the text "True" or "False".
Because the value of the field is either True or False, you
do not have to compare it to True and can use the simpler
conditions if you like:
WHERE Referrals.[Referral Assigned To]
or
WHERE Not Referrals.[Referral Assigned To]

If it is a number type field (used as a foreign key?), then
it either has a value or it is null. In this case try
using:
WHERE Referrals.[Referral Assigned To] Is Null
or
WHERE Referrals.[Referral Assigned To] Is Not Null

 

[Marshall Barton]

G'day
I have just cracked it. Is Null is what has worked. Thank you for your time
and help

Good.

For a Text field, you should also check to make sure you
either set the field's AllowZeroLength property to No
(strongly preferred) or, if you must use zero length
strings, change the condition to check for it as well as
Null.

 

[Brendan]

Thanks Marsh, I have made sure my text fields are set as you suggest. Thanks
again for your help