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])))
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])))