How to use ISNULL function in ms-access

ms-access ms-access-2003
NIlesh Lanke picture NIlesh Lanke · May 7, 2012 · Viewed 15.2k times · Source

I have database table with following fields

FieldId FieldGroupId FieldName

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

Following query does not work if FieldID is not present in databse.

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

Answer

HansUp picture HansUp · May 7, 2012

If you will be doing this from within an Access application session, you can use DLookup() to retrieve the FieldGroupID value which matches your FieldID value.

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

DLookup() will give you Null when FieldID is not found. You can use the Nz() function to substitute your desired value for Null.

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

Related questions

Now() function with time trim

So the function =Now()....is there a way I can use this and only get the date, not the time? or is there just a function for this idea?

Read More
how to use distinct in ms access

I have two tables. Task and Categories. TaskID is not a primary key as there are duplicate values.When there are multiple contacts are selected for a specific task,taskid and other details will be duplicated.I wrote the query: …

Read More
create form to add records in multiple tables

I am new to access (I have only 2003 version) and I am getting crazy. Consider also that I am a novice with DB in general, so now I am at the point that I am very confused... please help. I …

Read More