Suggests on how to identify null vs not null

[gojets1721]

I'm trying to create a select query where if a field has any value whatsoever, then in a new field, it just says "1". Whereas if the field is null, then it says "0".

I'm pretty new to access so apologies if this is a basic question. I've looked online and found guides for both individual questions but no solutions for a combined one. Any tips or guides would be great. Thanks

 

[Minty]

Something like;

SELECT YourField, IIf([YourField] is null , 0, 1 ) as MyNewField
FROM MyTable

 

[plog]

First, there is a function called IsNull that will let you know if a value is NULL:

MS Access: IsNull Function

This MSAccess tutorial explains how to use the Access IsNull function with syntax and examples. The Microsoft Access IsNull function returns TRUE if the expression is a null value. Otherwise, it returns FALSE.

www.techonthenet.com

 

[CJ_London]

or just

abs(myfield is not null)

 

[gojets1721]

Something like;

SELECT YourField, IIf([YourField] is null , 0, 1 ) as MyNewField
FROM MyTable

That worked! Just curious...if I wanted it to be 'no' and 'yes' instead of 0 and 1, respectively, is that possible?

 

[Pat Hartman]

SELECT YourField, IIf([YourField] is null , "No", "Yes" ) as MyNewField
FROM MyTable

 

[The_Doc_Man]

That worked! Just curious...if I wanted it to be 'no' and 'yes' instead of 0 and 1, respectively, is that possible?

Just be aware that a Boolean result of TRUE/FALSE in Access itself and in Access SQL uses a BYTE (integer) to store that T/F value. Which means that seeing TRUE/FALSE, YES/NO, and -1/0 are ALL simply cases of how you format the binary result. Behind the scenes it will always be the same - a zero or non-zero byte - unless you move up to SQL Server, which actually DOES have bit-level T/F flags.

 

[Galaxiom]

First, there is a function called IsNull that will let you know if a value is NULL:

Be aware that IsNull() is a VBA function while Fieldname Is Null is a database engine expression so the latter is much more efficient.

 

[Isaac]

Be aware that IsNull() is a VBA function while Fieldname Is Null is a database engine expression so the latter is much more efficient.

+1