Embedded Macro to return only active records

B

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 blanl but brings back the rest of the fields
from the same record. Hope this makes sense.
Thanks.
 
S

Steve Schapel

Brendan,

It is not clear to me what is the meaning of "based on the condition false".
But if (as I guess) you mean that you only want the report to include
records where the value of a Yes/No field is 0, then this is not a job for a
macro. In this case, you need to use a Criteria in the query that the
report is based on.
 
B

Brendan

Hi Steve
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.
 
S

Steve Schapel

Brendan,

What is the data type of the [Referral Assigned To] field? Is it a Yes/No
field? If so, it is never "blank".

You can use True and False to refer to the values of a Yes/No field.
Equally you can use Yes and No, On and Off, <>0 and 0. I usually use <>0
and 0 myself, but if you use the other referants, they are not enclosed in
""s as they are not text. Therefore, you can try:
WHERE Referrals.[Referral Assigned To]=False
 
B

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


Steve Schapel said:
Brendan,

What is the data type of the [Referral Assigned To] field? Is it a Yes/No
field? If so, it is never "blank".

You can use True and False to refer to the values of a Yes/No field.
Equally you can use Yes and No, On and Off, <>0 and 0. I usually use <>0
and 0 myself, but if you use the other referants, they are not enclosed in
""s as they are not text. Therefore, you can try:
WHERE Referrals.[Referral Assigned To]=False

--
Steve Schapel, Microsoft Access MVP


Brendan said:
Hi Steve
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.


.
 
S

Steve Schapel

Brendan,

In that case, the query criteria will be:
WHERE Referrals.[Referral Assigned To] Is Null
 
B

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


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


Steve Schapel said:
Brendan,

What is the data type of the [Referral Assigned To] field? Is it a Yes/No
field? If so, it is never "blank".

You can use True and False to refer to the values of a Yes/No field.
Equally you can use Yes and No, On and Off, <>0 and 0. I usually use <>0
and 0 myself, but if you use the other referants, they are not enclosed in
""s as they are not text. Therefore, you can try:
WHERE Referrals.[Referral Assigned To]=False

--
Steve Schapel, Microsoft Access MVP


Brendan said:
Hi Steve
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.


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top