IIF returns same for all conditions

[Larryg]

Hello All...
Can anyone help me understand why this expression returns the same result (5) for ANY/ALL conditions? (being used in QBE query grid or Access 2010) The intent is to calculate a future date (and time), based on set number of [business] days, given a specific date (and time) the job was originated.

IIf([Request Type]="Condition_One" Or "Condition_Two",(DateAdd("d",+5,[Date])),IIf([Request Type]="Condition_Three",(DateAdd("d",+25,[Date])),(DateAdd("d",+3,[Date]))))

I'm dealing with about 10 or so possible conditions (job types), but as the expression suggests, most should return "3".

Also, the results indicate [calendar] days. I wanted to get the initial code working before attempting to calculate for [business] days rather then calendar days. I found the following code online, but not quite sure how to incorporate it into the above expression. (Sorry, I don't recall the creator in order to give credit.) All dates (number of days) returned will be future, so there is no need to calculate for passed days.

Public Function PlusWorkDays(dteStart As Date, intNumDays As Long) As Date
'Dim PlusWorkDays As String
Dim HolidaysUntilPlusWorkdays As Date
Dim Idx As Long
'if dteStart on a weekend correct to Monday
If Weekday(dteStart, 2) > 5 Then
dteStart = dteStart + (2 - (dteStart Mod 7))


End If
' add days with W-E
If Weekday(dteStart, 2) = 1 Then ' On Monday
PlusWorkDays = CDate(dteStart + ((intNumDays \ 5) * 7) + intNumDays Mod 5) - 3 ' Weekends
Else
PlusWorkDays = CDate(dteStart + ((intNumDays \ 5) * 7) + intNumDays Mod 5) - 1
End If
' Holidays on workdays until intNumDays workdays
HolidaysUntilPlusWorkdays = DCount("[HolDate]", "tbl_MyHolidays", _
"[HolDate]>=#" & Format(dteStart, "mm\/dd\/yyyy") & _
"# AND [HolDate]<=#" & Format(PlusWorkDays, "mm\/dd\/yyyy") & _
"# AND Weekday([HolDate],2)<6")
'Add these holiday days
For Idx = 1 To HolidaysUntilPlusWorkdays
If Weekday(PlusWorkDays, 2) = 5 Then
PlusWorkDays = DateAdd("d", 3, PlusWorkDays)
Else
PlusWorkDays = DateAdd("d", 1, PlusWorkDays)
End If
'if the temporary day is a holiday add a workday
Do While DLookup("[HolDate]", "tbl_MyHolidays", _
"[HolDate]=#" & Format(PlusWorkDays, "mm\/dd\/yyyy") & "#")
If Weekday(PlusWorkDays, 2) = 5 Then
PlusWorkDays = DateAdd("d", 3, PlusWorkDays)
Else
PlusWorkDays = DateAdd("d", 1, PlusWorkDays)
End If
Loop
Next
End Function

__________________________________________________ ________

Appreciate any help given...

Larry

[orange]

A couple of points for consideration.

Access doesn't use this construct

IIf([Request Type]="Condition_One" Or "Condition_Two"......

It requires you to be explicit

IIf([Request Type]="Condition_One" Or [Request Type]= "Condition_Two"..............

I posted a working days function in the code library--see https://www.accessforums.net/showthread.php?t=58953

With 10 or more conditions, you might want to consider a Select Case construct.

Good luck with your project.

[Larryg]

A couple of points for consideration.

Access doesn't use this construct


It requires you to be explicit

I posted a working days function in the code library--see https://www.accessforums.net/showthread.php?t=58953

With 10 or more conditions, you might want to consider a Select Case construct.

Good luck with your project.

Orange... that worked for the initial [calendar] days portion. Thank You ! Now off to get the [Business] days portion working.

BTW... I have used Select Case concept in VBA code, for activity on a text box, but never in a QBE query grid. Would you know of an example of how that is set up?

Thanks...

Larry

[orange]

Select Case is a vba construct only-- not QBE.

[Larryg]

Hello again All...

All code is running now, however, the calculation of my_end_date is not consistent. My concept is... I have a known start date, then (based on a condition) I calculate the number of days. I then use the PlusWorkDays function to produce an end date. (i.e. StartDate, plus number of days, equals EndDate). (I am using General Date format for all fields and calculations.) There are instances where the EndDate is off by 1-day, maybe 2. Most inconsistencies involve the Start, or End date being on a weekend. I suspect it has something to do with the StartDate/EndDate falling on a weekend, and the function counting only business days (as it was designed to do, and as I need it to do). Does anyone have any thoughts as to why the result would be so close, but yet inconsistent occasionally? Would anyone know what the the fix for this situation would be?

Example: If StartDay is 7/09/2016, and the number of days is 6, then the EndDate should be 7/18/2016.

Thanks...
Larry

[orange]

It may be relate to use of Dlookup see this

What holidays do you have in your table?

I have no holidays in my test table, and with your function and start date, I get 7/16/2016

[Larryg]

It may be relate to use of Dlookup see this

What holidays do you have in your table?

I have no holidays in my test table, and with your function and start date, I get 7/16/2016

Orange... I do have several years of holidays in the holiday table, however all years jump from Memorial day to July 4th. So the DLookup shouldn't see any days between 7/9 and 7/18 regardless of the years in the past (i.e. first instance encountered).

Maybe I'm doing it wrong, but when I enter ?PlusWorkDays (07/09/2016,6) into the intermediate window, it returns 1/6/1900 12:00:33 AM. I of course get better results when the function is run within the procedure.

As a test I manipulated the data in both the table and the query to represent the example given above, and I also get 7/16/2016 when run. It should (or at least my desire would be) return 7/18/2016.

[orange]

Try enclosing your Date in #

?PlusWorkDays (#07/09/2016#,6)

[Larryg]

Try enclosing your Date in #

?PlusWorkDays (#07/09/2016#,6)

That worked, at least it worked to the extent it returned 7/16/2016.

I'm concerned with the line: If Weekday(dteStart, 2) = 1 Then ' On Monday
Wouldn't Monday be 2? I'm struggling to understand the specifics of the code due to a skills issue on my part.

Thanks so much for you replies, they are certainly appreciated !

[orange]

?? Where did your function come from???

Ii have another function that does weekdays or business days but doesn't deal with Holidays.
?enddate(#7/9/2016#,6,False)
18/07/2016


The syntax for the Weekday function in MS Access is:
Weekday ( date_value, [firstdayofweek] )

[Larryg]

?? Where did your function come from???

Ii have another function that does weekdays or business days but doesn't deal with Holidays.
?enddate(#7/9/2016#,6,False)
18/07/2016


The syntax for the Weekday function in MS Access is:
Weekday ( date_value, [firstdayofweek] )

Orange...

I added the following to the above function.

Next (didn't repost complete function above)
If PlusWorkDays = vbSaturday Then
PlusWorkDays = DateAdd("d", 2, PlusWorkDays)
If PlusWorkDays = vbSunday Then
PlusWorkDays = DateAdd("d", 1, PlusWorkDays)
End If
End If
End Function

My thought process is... if the function is not accounting for the EndDate ending on a weekend day, then I would force that calculation after the function arrived at a date. But... in spite of the PlusWorkDays in the If statement displaying 7/16/2016 in break mode... it skips right over the PlusWorkDays = DateAdd("d", 2, PlusWorkDays) lines in both If statements.

Any thoughts on why if it displays 7/16/2016, it doesn't perform the DateAdd of +2?

Thanks...

Larry

[orange]

I also found if I use your code and start on 7/9/2016 and add 1 day I get 7/9/2016... sounds like it isn't accounting for the start date.

[orange]

Larry,

I had some issues with date formats (I'm in Canada and regional setting is DD/MM/YYYY.) but did get it resolved.
Here is a procedure to add the number of business days(working days) to a date, and get you the end date (inclusive of start date)
of that period. It also accommodates holidays. This shows a starting date of Jul 9 2016 and adding 6 Business Days. Weekend is Saturday and Sunday.
You could make this a function to use any start date and any number of business/working days. The accompanying holiday table requires the design and name I have given --but you could change that as/if necessary.

Here is the code:

'---------------------------------------------------------------------------------------
' Procedure : addBusDays
' Author    : mellon
' Date      : 17/July/2016
' Purpose   : to calculate 6 business days (inclusive) from a given date AND
'to use Tbl_myHolidays for identified Holidays and to account for holidays
'---------------------------------------------------------------------------------------
'
Sub addBusDays()
'
'
'
      Dim BusDayCnt As Integer, NthBusinessDay As Integer
10    Dim dtstart As Date: dtstart = #7/9/2016#

20    Dim intBusDays As Integer: intBusDays = 6
30    NthBusinessDay = intBusDays

    ' Since Starting day is day 1,must adjust the number of business days
40        intBusDays = intBusDays - 1
50    On Error GoTo addBusDays_Error

60    Do While Not BusDayCnt = intBusDays
70      If Not (WeekDay(dtstart) = 7 Or WeekDay(dtstart) = 1) Then  'weekday
80          If DCount("*", "tbl_MyHolidays", "Holdate=#" & dtstart & "#") > 0 Then    'holiday
90              Debug.Print dtstart & " is a holiday"  'do not add to BusDayCnt
100         Else
110             BusDayCnt = BusDayCnt + 1    ' not holiday and is a weekday =BusDay
120             Debug.Print "BusDayCnt is " & BusDayCnt & "  on date " & dtstart
130         End If
140     End If
150     dtstart = dtstart + 1
160   Loop
170   Debug.Print "The " & NthBusinessDay & "th  business day is " & dtstart

180   On Error GoTo 0
190   Exit Sub

addBusDays_Error:

200   MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure addBusDays of Module AWF_Related"
End Sub

Here is my holidays table tbl_MyHolidays data ( design is in attached jpg the original set up)

HolDate	id
03/25/2016	1
03/28/2016	2
03/17/2016	3
12/07/2016	4

And this is the result in the immediate window: NOTE my Canadian Regional setting for Dates DD/MM/YYYY

BusDayCnt is 1 on date 11/07/2016
12/07/2016 is a holiday
BusDayCnt is 2 on date 13/07/2016
BusDayCnt is 3 on date 14/07/2016
BusDayCnt is 4 on date 15/07/2016
BusDayCnt is 5 on date 18/07/2016
The 6th business day is 19/07/2016




I hope this is helpful.

Good luck with your project.


Note: After posting this I noticed that the holiday for July is formatted as per Canadian Regional setting???
In design view, as soon as I put the cursor on the field, it immediately goes to American MM/dd/yyyy format???
And when I move off the field it reverts to DD/MM/YYYY??????

Update again: I modified my regional setting to show date as dd-mmm-yyyy. The table field Holdate also has format dd-mmm-yyyy and it now shows this latest design:

HolDate	id
25-Mar-2016	1
28-Mar-2016	2
17-Mar-2016	3
12-Jul-2016	4

and the procedure results are

BusDayCnt is 1 on date 11-Jul-2016
12-Jul-2016 is a holiday
BusDayCnt is 2 on date 13-Jul-2016
BusDayCnt is 3 on date 14-Jul-2016
BusDayCnt is 4 on date 15-Jul-2016
BusDayCnt is 5 on date 18-Jul-2016
The 6th business day is 19-Jul-2016