Field that shows Yes / No if value is null

[Andibevan]

I want to have a field in a query that shows yes/no depending on whether a
date is present in another field. It doesn't seem to work:-

SELECT [tbl_CR_CR2Deliverables].[ID], [tbl_CR_CR2Deliverables].[Delivery
Expected], [tbl_CR_CR2Deliverables].[Delivered],
[tbl_CR_CR2Deliverables].[PVCS], [tbl_CR_CR2Deliverables].[File Name],
[tbl_CR_CR2Deliverables].[Archive Path],
[tbl_CR_CR2Deliverables].[Verification Date],
[tbl_CR_CR2Deliverables].[Verfied By],IsNull([Verification Date]) AS
cVerified
FROM tbl_CR_CR2Deliverables
WHERE ((([tbl_CR_CR2Deliverables].[CR]) Like
[Forms]![frm_Change_Requests]![LSTB_CR_Number]));

But it shows -1 when the date is blank - any ideas?

TIA

Andi

 

[MH]

That's working perfectly, -1 is the logical value of True (not "True") <g>

Replace

IsNull([Verification Date]) AS cVerified

With

Iif(IsNull([Verification Date])=True,"True","False") AS cVerified

To return strings instead of 0 or -1

MH

 

[fredg]

I want to have a field in a query that shows yes/no depending on whether a
date is present in another field. It doesn't seem to work:-

SELECT [tbl_CR_CR2Deliverables].[ID], [tbl_CR_CR2Deliverables].[Delivery
Expected], [tbl_CR_CR2Deliverables].[Delivered],
[tbl_CR_CR2Deliverables].[PVCS], [tbl_CR_CR2Deliverables].[File Name],
[tbl_CR_CR2Deliverables].[Archive Path],
[tbl_CR_CR2Deliverables].[Verification Date],
[tbl_CR_CR2Deliverables].[Verfied By],IsNull([Verification Date]) AS
cVerified
FROM tbl_CR_CR2Deliverables
WHERE ((([tbl_CR_CR2Deliverables].[CR]) Like
[Forms]![frm_Change_Requests]![LSTB_CR_Number]));

But it shows -1 when the date is blank - any ideas?

TIA

Andi

I assume this is your verification column:

IsNull([Verification Date]) AS cVerified <

It's working properly. When the [Verification Date] is null it's
displaying -1 (True).
You just didn't tell it what you really wanted.

Try:
IIf(IsNull([Verification Date]),"No","Yes") as cVerified

Reverse the "No" and "Yes" depending upon the logic you want.

 

[giorgio rancati]

Hi Andibevan,

Open your query in design mode, select the [cVerified] column, click on the
View-->Property, type Yes/No in Format property.

bye

 

[Andibevan]

Thanks all - I was being blonde - Easy how the obvious can be so difficult
to spot some-times.

Ta

Andi

I want to have a field in a query that shows yes/no depending on whether a
date is present in another field. It doesn't seem to work:-

SELECT [tbl_CR_CR2Deliverables].[ID], [tbl_CR_CR2Deliverables].[Delivery
Expected], [tbl_CR_CR2Deliverables].[Delivered],
[tbl_CR_CR2Deliverables].[PVCS], [tbl_CR_CR2Deliverables].[File Name],
[tbl_CR_CR2Deliverables].[Archive Path],
[tbl_CR_CR2Deliverables].[Verification Date],
[tbl_CR_CR2Deliverables].[Verfied By],IsNull([Verification Date]) AS
cVerified
FROM tbl_CR_CR2Deliverables
WHERE ((([tbl_CR_CR2Deliverables].[CR]) Like
[Forms]![frm_Change_Requests]![LSTB_CR_Number]));

But it shows -1 when the date is blank - any ideas?

TIA

Andi

I assume this is your verification column:

IsNull([Verification Date]) AS cVerified <

It's working properly. When the [Verification Date] is null it's
displaying -1 (True).
You just didn't tell it what you really wanted.

Try:
IIf(IsNull([Verification Date]),"No","Yes") as cVerified

Reverse the "No" and "Yes" depending upon the logic you want.