How to use ISNULL function in ms-access

Question

I have a database table with the following fields

FieldId FieldGroupId FieldName

I need to select FieldGroupId from the Fields table, where FieldId = "?" and iff FieldGroupId is empty, it should return the default value.

The following query does not work if the field identifier is not in the database.

select IIf(IsNull(FieldGroupID),"AA",FieldGroupID) from Fields where FieldID ='ALPHAA'

+3

NIlesh Lanke May 07 '12 at 10:31

1 answer

Hansup · Accepted Answer · 2012-05-07T11:30:56+0000

If you will do this from an Access application session, you can use DLookup()to get the value FieldGroupIDthat matches your value FieldID.

DLookup("FieldGroupID", "[Fields]", "FieldID ='ALPHAA'")

DLookup()will give you Null if FieldIDnot found. You can use the function Nz()to replace the desired value for Null.

Nz(DLookup("FieldGroupID", "[Fields]", "FieldID ='ALPHAA'"), "AA")