I have 2 functions, one figures out adding 6 months to a given date. The 6 months added is stored in the variable dt - which is a public variable in a module..however, this function returns a value of "Y" or "N". This function works fine..
I would, however, like to see this date in my query, so I thought I'd create a new function that references the one mentioned above. However, I get a return value in the query of 12:00:00am..
Any suggestions? The 2 functions follow:
Public Function fcn_Add_Time(DOH As Date, FLAG_EXP_DT As Date) As String
Dim time_added As Integer 'amount of months added to DOH
Dim month_plus_time As Integer 'month when time added is added - converted to proper month by select case statement if time added plus DOH month > 12..
Dim year_plus_time As Integer 'year with time added if applicable; select case will determine if +1 needs to be added to the year..
Dim month_day As Integer
time_added = 6
month_plus_time = Month(DOH) + 6
year_plus_time = year(DOH)
Select Case month_plus_time
Case Is < 13
month_plus_time = month_plus_time
year_plus_time = year_plus_time
Case Is = 13
month_plus_time = 1
year_plus_time = year_plus_time + 1
Case Is = 14
month_plus_time = 2
year_plus_time = year_plus_time + 1
Case Is = 15
month_plus_time = 3
year_plus_time = year_plus_time + 1
Case Is = 16
month_plus_time = 4
year_plus_time = year_plus_time + 1
Case Is = 17
month_plus_time = 5
year_plus_time = year_plus_time + 1
Case Is = 18
month_plus_time = 6
year_plus_time = year_plus_time + 1
End Select
Select Case Day(DOH)
Case Is <= 28
month_day = Day(DOH)
Case Is = 31
Select Case month_plus_time
Case Is = 1, 3, 5, 7, 8, 10, 12
month_day = 31
Case Is = 2
month_day = 28
Case Is = 4, 6, 9, 11
month_day = 30
End Select
Case Is = 30
Select Case month_plus_time
Case Is = 1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
month_day = 30
Case Is = 2
month_day = 28
End Select
Case Is = 29
Select Case month_plus_time
Case Is = 1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
month_day = 29
Case Is = 2
month_day = 28
End Select
End Select
dt = DateValue(month_plus_time & "/" & month_day & "/" & year_plus_time)
If FLAG_EXP_DT >= DOH And FLAG_EXP_DT < dt Then
fcn_Add_Time = "Y"
Else
fcn_Add_Time = "N"
End If
End Function
==============================================
'2nd function that I want to reference the above "dt" variable..
'when I enter this function in my query and open query I get 12:00:00am
Public Function fcn_Add_Time_Date() As Date
fcn_Add_Time_Date = dt
End Function
I would, however, like to see this date in my query, so I thought I'd create a new function that references the one mentioned above. However, I get a return value in the query of 12:00:00am..
Any suggestions? The 2 functions follow:
Public Function fcn_Add_Time(DOH As Date, FLAG_EXP_DT As Date) As String
Dim time_added As Integer 'amount of months added to DOH
Dim month_plus_time As Integer 'month when time added is added - converted to proper month by select case statement if time added plus DOH month > 12..
Dim year_plus_time As Integer 'year with time added if applicable; select case will determine if +1 needs to be added to the year..
Dim month_day As Integer
time_added = 6
month_plus_time = Month(DOH) + 6
year_plus_time = year(DOH)
Select Case month_plus_time
Case Is < 13
month_plus_time = month_plus_time
year_plus_time = year_plus_time
Case Is = 13
month_plus_time = 1
year_plus_time = year_plus_time + 1
Case Is = 14
month_plus_time = 2
year_plus_time = year_plus_time + 1
Case Is = 15
month_plus_time = 3
year_plus_time = year_plus_time + 1
Case Is = 16
month_plus_time = 4
year_plus_time = year_plus_time + 1
Case Is = 17
month_plus_time = 5
year_plus_time = year_plus_time + 1
Case Is = 18
month_plus_time = 6
year_plus_time = year_plus_time + 1
End Select
Select Case Day(DOH)
Case Is <= 28
month_day = Day(DOH)
Case Is = 31
Select Case month_plus_time
Case Is = 1, 3, 5, 7, 8, 10, 12
month_day = 31
Case Is = 2
month_day = 28
Case Is = 4, 6, 9, 11
month_day = 30
End Select
Case Is = 30
Select Case month_plus_time
Case Is = 1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
month_day = 30
Case Is = 2
month_day = 28
End Select
Case Is = 29
Select Case month_plus_time
Case Is = 1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
month_day = 29
Case Is = 2
month_day = 28
End Select
End Select
dt = DateValue(month_plus_time & "/" & month_day & "/" & year_plus_time)
If FLAG_EXP_DT >= DOH And FLAG_EXP_DT < dt Then
fcn_Add_Time = "Y"
Else
fcn_Add_Time = "N"
End If
End Function
==============================================
'2nd function that I want to reference the above "dt" variable..
'when I enter this function in my query and open query I get 12:00:00am
Public Function fcn_Add_Time_Date() As Date
fcn_Add_Time_Date = dt
End Function