Nested IIF Statement in calculating dates (1 Viewer)

bffluv

New member
Local time
Yesterday, 20:48
Joined
Oct 22, 2018
Messages
9
I started with an excel table with 358 records already in it. I created an Access Data Entry Form to avoid typos and unnecessary data for the excel table. There are calculated fields in the excel table that I need to convert to the proper Access syntax. The attempts below are giving me a different number from the preexisting excel table or giving me blanks. Will you help? What am I doing wrong? I have spent a month trying to figure this out. banghead:

Excel IF statement
=IF([Complaint_Closed]<>"",[Complaint_Closed]-[INV_IncomingLtr],IF([INV_IncomingLtr]<>"",TODAY()-[INV_IncomingLtr],""))

Attempt #1
=IIf(IsNull([Complaint_Closed]),DateDiff("d",[INV_IncomingLtr],[INV_LtrSigned]),DateDiff("d",[INV_IncomingLtr],[Complaint_Closed]))

Attempt #2
=IIf(IsNotNull([Complaint_Closed]),DateDiff("d",[tlbInvestigations].[Complaint_Closed]- [tlbInvestigations].[INV_IncomingLtr]), IIf(IsNotNull([INV_IncomingLtr]),DateDiff("d",TODAY()-[tlbInvestigations].[INV_IncomingLtr],""))

Attempt #3
=IIf(IsNull([Complaint_Closed]),IIf(IsNull([INV_IncomingLtr]),"",DateDiff("d",[INV_IncomingLtr],Now())),IIf(IsNull([INV_IncomingLtr]),"",DateDiff("d",[INV_IncomingLtr],[Complaint_Closed])))
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:48
Joined
Oct 29, 2018
Messages
21,322
Hi,

How about?

IIf([Complaint_Closed]<>"",[Complaint_Closed]-[INV_IncomingLtr],IIf([INV_IncomingLtr]<>"",Date()-[INV_IncomingLtr],""))

Hope it helps...
 

bffluv

New member
Local time
Yesterday, 20:48
Joined
Oct 22, 2018
Messages
9
I get an error in the form field, #Name?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:48
Joined
Oct 29, 2018
Messages
21,322
Hi,

Double check for any typos. A Name error usually means the field name is off somehow.
 

bffluv

New member
Local time
Yesterday, 20:48
Joined
Oct 22, 2018
Messages
9
Everything looks good :banghead:

Here is what I am trying to do with this formula:

If complaint is active/open, TODAY() minus Complaint Date of Incoming Letter

If complaint is closed, subtract Complaint Closed Date from Complaint Date of Incoming Letter.

Here is the syntax that I am using:

=IIf(IsNull([Complaint_Closed]),
TODAY()-[INV_IncomingLtr],
DateDiff("d",[Complaint_Closed],[INV_IncomingLtr]))

Is this syntax correct using IIF and IsNull?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:48
Joined
Oct 29, 2018
Messages
21,322
Hi,

Strictly speaking of the syntax, TODAY() is not an Access function. Try using Date() instead.

Hope it helps...
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:48
Joined
Oct 29, 2018
Messages
21,322
You’re very welcome. We’re happy to assist.
 

Users who are viewing this thread

Top Bottom