How do I convert #Error to empty

[Mafi]

How do I convert #Error to empty in Year column in access Query?
Year colum is obtain by Year:year([date])
If I run the query it gives me error where there is no data in the row. pls help

[PhilOfWalton]

Try

Iif(Not IsNull([date]), [date])

Note
Date is a reserved word and although Access will recognise [date] with the square brackets round it as a user defined field, I would strongly recommend renaming the field

Phil

[zmbd]

Date is a reserved word and although Access will recognize [date] with the square brackets round it as a user defined field, I would strongly recommend renaming the field

Building on this - ALL of the words in these lists should be avoided for naming variables, tables, queries, etc... when designing ANY application in Access.
> http://allenbrowne.com/AppIssueBadWord.html
> https://support.office.com/en-ie/article/access-2007-reserved-words-and-symbols-e33eb3a9-8baa-4335-9f57-da237c63eabe
> https://docs.microsoft.com/en-us/office/client-developer/access/reserved-words-access-custom-web-app

(IMHO: It's best practice to avoid these words in other programing/application development environments as quite often the same tokens are reserved cross platform!)

[NeoPa]

I would echo all that's been said already, but as a direct answer to your question try :

Year: IIf([Date] Is Null,Null,Year([Date]))

Change the field name in here once you've changed it in your table ;-)

[Mafi]

I tried all your suggestion and that error is still their. But i have sort out my problem by appending all the record to another table. thanks

[NeoPa]

Mafi:
I tried all your suggestion and that error is still their.

I can only assume then that somewhere in the very little you've told us about your problem is a mistake.

For the situation you've outlined I can confirm the solution suggested in post #4 works 100% reliably. Even naming the field with a reserved word Year.

Perhaps you should try it again and pay attention to doing it exactly as posted this time.