0

I was handed an old Access db someone else built awhile ago. I added one field to an existing query, but when I went to save it threw an error on a different field I never touched. Error message: Invalid Use of Vertical Bars. Here is the expression it didn't like, but somehow they were able to save it in the past:

DaysOpened: 
IIf([re_close_date] Is Null|IIf([close_date] Is Null|Date()-[date_reported]|Date()-[reopen_dt]))

I've tried switching to commas and &'s, but can't get the right combination to rebuild this if someone can help? Basically...if re_close_date is null, check if close_date is null. If yes to both, record must still be Open so we need to calculate DaysOpened by taking current Date and subtracting date_reported or current Date - reopen_dt if record was reopened. Thanks.

Andre
  • 26,751
  • 7
  • 36
  • 80
CompileThis
  • 1
  • 1

2 Answers2

0

Replace those bars to obtain a valid syntax:

IIf([re_close_date] Is Null, IIf([close_date] Is Null, Date()-[date_reported], Date()-[reopen_dt]))

or perhaps:

IIf([re_close_date] Is Null, IIf([close_date] Is Null, Date()-[date_reported], Date()-[reopen_dt]), 0)
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • I tried switching to commas before and it did not work, however I did ultimately solve it by using IsNull() function instead and switching my regional settings. You had the right approach, thanks! – CompileThis Apr 12 '18 at 15:06
0

Finally solved it by switching my Regional Settings for List Separator from Vertical Bar to Comma, and switching to IsNull() function. Here is what finally worked:

DaysOpened: IIf(IsNull([Re_Close_Date]),IIF(IsNull([Close_Date]),Date()-[Date_Reported],Date()-[Reopen_dt]),0)

What is frustrating is even if you use the Expression Builder, it will tell you to use Vertical Bars (instead of commas) for IIf statements, but then will throw the Vertical Bar error message as soon as you try to run it. To solve this, change your OS settings in Windows as follows:

Control Panel > Region & Language > click Additional Settings under Format tab > change the List Separator value from | to ,. Click Apply and then swap vertical bars out for commas in your expression.

CompileThis
  • 1
  • 1