Report to bring back active field only
in [Reports]
|
Prev: Using filtered form data as record source for report
Next: Print Report without openning it / Fast printing
From: Brendan on 13 Apr 2010 02:15 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
From: Marshall Barton on 13 Apr 2010 09:44 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]
From: Brendan on 14 Apr 2010 03:43 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] > . >
From: Marshall Barton on 14 Apr 2010 10:09 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]
From: Brendan on 15 Apr 2010 00:52
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] > . > |