DATE Minus 1 day

[EileenAchil]

Hi , my date field name is TransDate and i want to minus the date -1. Anyone know how? My situation is if the time is less than 8pm and the shift is night , so the transdate will minus one. Eg. If today is 29 dec then it should change to 28 dec
Code:
IIF ( record.time<#20:00:00#) AND (calendar.shift = "Night"), record.date -1, "-") AS scantype


The code error is undefined function.

[andy49]

Worked for me with a few alterations.

Perhaps change the field names (date and time are key phrases in access so aren't used to avoid confusion)
Are you calculating the field transdate?

I left off as scantype (sql??)

And if your fields are date/time fields, the iif statement cannot return your false value of "-"

I used

iif([time1]<#8:00:00 pm#, [date1]-1,[date])


Sent from my iPhone using Tapatalk

[ssanfu]

"Time" and "Date" are reserved words in Access and shouldn't be used as object names. Plus, they are built-in functions.

Are you trying to use this in a query? If so, please post the SQL of the query.

Maybe this??

IIf(TimeValue([TransDate]) < #8:00:00 PM# And (shift = "Night"), [TransDate] - 1, "-")   AS scantype