Search for Null in an iif expression 1

[BV874]

Hi,
I am running a totals query on a Yes/No data type field. The field contains an Accepted response or a Declined response. When a reponse has not been received the field is null. But when I query the field the Null values appear as false so I need to differentiate between them. My expression is grouping the nulls in with the false records.

RESPONSE: IIf([dbo_CUSACCT].[PROP_ACEPT]=True,'ACCEPTED',IIf([dbo_CUSACCT].[PROP_ACEPT]=False,'DENIED',IIf([dbo_CUSACCT].[PROP_ACEPT] Is Null,'NO REPLY')))

I am running Access 2003 on XP SP3. Please help.

Thanks.

 

[dhookom]

I would try something like:

RESPONSE: Choose( Abs(Nz([dbo_CUSACCT].[PROP_ACEPT],2))+1,
'DENIED','ACCEPTED','NO REPLY')

Actually, I would change the data type of field to tinyint and set a default of 0 for No Reply. Or possibly create a small lookup table of PROP_ACEPT values. I rarely if ever use yes/no fields since they are so restricted.

Duane
Hook'D on Access
MS Access MVP

 

[BV874]

Hi dhookom,
That did not work. It grouped the falses with the nulls. I requested the date type to be changed, but its not going to happen anytime soon.

thanks.

 

[dhookom]

It looks like you are storing your data on a SQL Server which handles yes/no fields different from Access. I don't think SQL Server supports Nulls in yes/no field but I could be wrong.

Duane
Hook'D on Access
MS Access MVP

 

[BV874]

Yes, the data is on a sequel server. Thanks for your help.