Function in Query won't return Value (1 Viewer)

[catbeasy]

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

 

[YevS]

you need to make sure that

dt = DateValue(month_plus_time & "/" & month_day & "/" & year_plus_time)

returns a valid date, I'm guessing its not.

 

[catbeasy]

you need to make sure that

dt = DateValue(month_plus_time & "/" & month_day & "/" & year_plus_time)

returns a valid date, I'm guessing its not.

It is. As I explained, the main function works. I can step thru it and see the correct date, and besides, the function assigns correctly the adding of a "Y" or "N" where appropriate.

It is the calling of that other one that doesn't..any suggestions?

Thanks..

 

[boblarson]

Well, in your "calling" of the other function you would need to pass it parameters. So right now it is getting no input and therefore the output is also "nothing" or 12:00:00 AM.

You would have to pass parameter values:

Public Function fcn_Add_Time_Date() As Date
   Call fcn_Add_Time_Date([COLOR="Red"]SomethingHereForDOH[/COLOR], [COLOR="red"]SomethingHereForFLAG_EXP_DT[/COLOR])
End Function