If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Report to bring back active field only
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] |
#3
|
|||
|
|||
Report to bring back active field only
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] . |
#4
|
|||
|
|||
Report to bring back active field only
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] |
#5
|
|||
|
|||
Report to bring back active field only
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] . |
#6
|
|||
|
|||
Report to bring back active field only
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] . |
#7
|
|||
|
|||
Report to bring back active field only
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] |
#8
|
|||
|
|||
Report to bring back active field only
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] . |
Thread Tools | |
Display Modes | |
|
|