Report to bring back active field only

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.

--
Brendan
Adelaide, Australia
As always I have searched the forum first before posting my question

Brendan wrote:
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.

--
Marsh
MVP [MS Access]

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.

--
Brendan
Adelaide, Australia
As always I have searched the forum first before posting my question


"Marshall Barton" wrote:

Brendan wrote:
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.

--
Marsh
MVP [MS Access]
.

Brendan wrote:
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

--
Marsh
MVP [MS Access]

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


"Marshall Barton" wrote:

Brendan wrote:
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

--
Marsh
MVP [MS Access]
.

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


"Brendan" wrote:

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


"Marshall Barton" wrote:

Brendan wrote:
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

--
Marsh
MVP [MS Access]
.

Brendan wrote:

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.

--
Marsh
MVP [MS Access]

Thanks Marsh, I have made sure my text fields are set as you suggest. Thanks
again for your help
--
Brendan
Adelaide, Australia
Office Professional 2007 on Windows XP
I always search the forum before posting a question


"Marshall Barton" wrote:

Brendan wrote:

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.

--
Marsh
MVP [MS Access]
.