Help converting an excel if formula to Access

[willmafingerdo]

Hello,


I am trying to add a query that will find any date discrepancies. I have the formula for excel but im not to familiar nesting if's ect and adding dates in access and wondered if anyone could offer any suggestions and/or reading material for how to do this.

This is the formula in Excel to find what the Due date should be:

=IF(B2<=3,A2+(52*7),IF(AND(B2>=4,B2<=6),A2+(104*7) ,IF(B2>=7,A2+(156*7),"")))

to make sense.. here's what that means:

Step = waiting period (weeks*7) from LEI. 1 yr (52*7), 2 yr (104*7), 3 yr (156*7)

=IF(STEP<=3,LEI+(52*7),IF(AND(STEP>=4,STEP<=6),LEI +(104*7),IF(STEP>=7,LEI+(156*7),"")))

the WIGI DueDate is what this formula calculates, below is an example,


LEI Date- Step - WIGI DueDate
1/3/2021 - 1 - 1/2/2022
1/3/2021 - 2 - 1/2/2022
1/3/2021 - 3 - 1/2/2022
1/3/2021 - 4 - 1/1/2023
1/3/2021 - 5 - 1/1/2023
1/3/2021 - 6 - 1/1/2023
1/3/2021 - 7 - 12/31/2023
1/3/2021 - 8 - 12/31/2023
1/3/2021 - 9 - 12/31/2023



Any questions let me know.

Thanks,
Will

[Micron]

It would be better for some of us if you posted some data and also the expected results. A copy & paste of an Excel range can be pasted in your post just like a table.
Or you could research nested IIF (IIF being the more or less equivalent Access function to Excel's IF). It's also possible to create a custom function that has a Select Case block when there are many possibilities to deal with. Less commonly used is Switch and Choose functions in Access. Point of all that being that sample input and output is often better than just your formula.

[CJ_London]

In Access you would use

=iiF(B2<=3,A2+(52*7),iiF(B2>=4 and B2<=6,A2+(104*7) ,iiF(B2>=7,A2+(156*7),null)))

or simpler

=iiF(B2<=3,A2+(52*7),iiF(B2<=6,A2+(104*7) ,iiF(B2>=7,A2+(156*7),null)))

databases are strongly data typed, your results are all numeric (dates I presume), but in the event that none of the conditions are met, you return a string - it needs to be null or 0 for numbers

you could also use the dateadd function

=iif(isnull(B2), null,dateadd("ww",iif(B2<=3,52,iif(b2<=6,104,156)) ,A2))

[ssanfu]

I created a table (Table1) with fields
TestID_PK (Autonumber) PK
LEI_Date (Date)
Step (Number - Integer)

I entered the provided date from Post #1.
I created a query:

SELECT Table1.LEI_Date, Table1.Step, IIf([STEP]<=3,[LEI_Date]+(364),IIf([Step] Between 4 And 6,[LEI_Date]+(728),IIf([STEP]>=7,[LEI_date]+(1092),""))) AS WIGI_DueDate
FROM Table1;

The results of the query:

LEI_Date	Step	WIGI_DueDate
1/3/2021	1	1/2/2022
1/3/2021	2	1/2/2022
1/3/2021	3	1/2/2022
1/3/2021	4	1/1/2023
1/3/2021	5	1/1/2023
1/3/2021	6	1/1/2023
1/3/2021	7	12/31/2023
1/3/2021	8	12/31/2023
1/3/2021	9	12/31/2023

The formula could also be written

IIF([STEP] <= 3,[LEI_Date] +(52*7), IIF([Step] >= 4 and Step <= 6, [LEI_Date] +(104*7), IIF([STEP] >=7, [LEI_date] +(156*7),""))) AS WIGI_DueDate

OR
 
IIF([STEP] <= 3,[LEI_Date] +(52*7), IIF([Step] Between 4 and 6, [LEI_Date] +(104*7), IIF([STEP] >=7 , [LEI_date] +(156*7),""))) AS WIGI_DueDate

OR

IIf([STEP] <= 3,[LEI_Date] +(364),  IIf([Step] Between 4 And 6, [LEI_Date] +(728), IIf([STEP]>=7 ,[LEI_date] +(1092),""))) AS WIGI_DueDate