Search code examples
ms-access-2007ms-access-2010

If error on Access query

Hi I am doing a query in MS Access that is the following:

Tenure_days: DateSerial(Year(Date()),Month(Date()),11)-DateSerial(Mid([AHT_Tenure].[ABAY Start Date],1,4),Mid([AHT_Tenure].[ABAY Start Date],6,2),Mid([AHT_Tenure].[ABAY Start Date],9,2))

In some records is get #Error when [AHT_Tenure].[ABAY Start Date] is empty, so how do I edit the above query so that when Tenure_days = #Error to put 120 ?

Solution

  • The problem is because the field is empty, you need to test if the field is empty first. Try this,

    Tenure_days: IIF(Len([AHT_Tenure].[ABAY Start Date] & "") = 0, 120, DateSerial(Year(Date()),Month(Date()),11) - DateSerial(Mid([AHT_Tenure].[ABAY Start Date],1,4),Mid([AHT_Tenure].[ABAY Start Date],6,2),Mid([AHT_Tenure].[ABAY Start Date],9,2)))

    You first using an Immediate IF, see if the field you are using the Mid function to is empty or not. If it is empty you are giving 120 straight away. If not, you are performing the operation. I hope this helps.