423,688 Members | 1,879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,688 IT Pros & Developers. It's quick & easy.

How do I convert #Error to empty

P: 20
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
Aug 24 '18 #1
Share this Question
Share on Google+
5 Replies


PhilOfWalton
Expert 100+
P: 1,373
Try

Expand|Select|Wrap|Line Numbers
  1. 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
Aug 24 '18 #2

zmbd
Expert Mod 5K+
P: 5,285
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!)
Aug 24 '18 #3

NeoPa
Expert Mod 15k+
P: 31,084
I would echo all that's been said already, but as a direct answer to your question try :
Expand|Select|Wrap|Line Numbers
  1. Year: IIf([Date] Is Null,Null,Year([Date]))
Change the field name in here once you've changed it in your table ;-)
Aug 24 '18 #4

P: 20
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
Sep 13 '18 #5

NeoPa
Expert Mod 15k+
P: 31,084
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.
Sep 13 '18 #6

Post your reply

Sign in to post your reply or Sign up for a free account.