Results of functions in my code don’t appear in my output table

[Bouldergirl]

Hi there,

I've got a function and within it are embedded 2 other functions (CalcAsin2 and CalcGDD), which are called from within the main function. The code seems to run (i.e., no error messages, and the output table that I create is made and populated with most of the values I want) but the two columns that should be filled with values generated by my Functions CalcAsin2 and CalcGDD are populated with empty cells instead.

Any help on this matter would be greatly appreciated!

Thanks,
-Tiffany

The code is:


Option Compare Database

Function CalculateHeatUnits()
Dim db As Database
Dim tdfNew As TableDef
Dim rin, rout, T_mean, T_range, theta, Pmon, GDD, Asin2
ofilename = "growing_degree_day_test2"
Set db = CurrentDb()
For i = 0 To db.TableDefs.Count - 1 ' Delete table
If db.TableDefs(i).Name = ofilename Then
DoCmd.DeleteObject A_TABLE, ofilename
Exit For
End If
Next
Set tdfNew = db.CreateTableDef(ofilename)
With tdfNew
.Fields.Append .CreateField("grid", dbLong)
.Fields.Append .CreateField("month", dbbyte)
.Fields.Append .CreateField("GDD_test", dbSingle)
.Fields.Append .CreateField("theta_test", dbSingle)
.Fields.Append .CreateField("asin_theta", dbSingle)
.Fields.Append .CreateField("Tm", dbSingle)
.Fields.Append .CreateField("Tr", dbSingle)
db.TableDefs.Append tdfNew
End With
Set rinwd = db.OpenRecordset("GDD_test_input2")
Set rout = db.OpenRecordset(ofilename)

 

[Bouldergirl]

Whoops! I didn't include the entire code, so here it is:

Function CalculateHeatUnits()
Dim db As Database
Dim tdfNew As TableDef
Dim rin, rout, T_mean, T_range, theta, Pmon, GDD, Asin2
ofilename = "growing_degree_day_test2"
Set db = CurrentDb()
For i = 0 To db.TableDefs.Count - 1 ' Delete table
If db.TableDefs(i).Name = ofilename Then
DoCmd.DeleteObject A_TABLE, ofilename
Exit For
End If
Next
Set tdfNew = db.CreateTableDef(ofilename)
With tdfNew
.Fields.Append .CreateField("grid", dbLong)
.Fields.Append .CreateField("month", dbbyte)
.Fields.Append .CreateField("GDD_test", dbSingle)
.Fields.Append .CreateField("theta_test", dbSingle)
.Fields.Append .CreateField("asin_theta", dbSingle)
.Fields.Append .CreateField("Tm", dbSingle)
.Fields.Append .CreateField("Tr", dbSingle)
db.TableDefs.Append tdfNew
End With
Set rinwd = db.OpenRecordset("GDD_test_input2")
Set rout = db.OpenRecordset(ofilename)
T_mean = (rinwd.mint + rinwd.maxt) / 2
T_range = (rinwd.maxt - rinwd.mint) / 2
theta = (rinwd.baseT - T_mean) / T_range

rinwd.MoveFirst
Do Until rinwd.EOF
Call CalcAsin2(theta)
GDD2 = CalcGDD(rinwd.maxt, rinwd.mint, T_mean, rinwd.baseT, Asin2)
milf = theta
gilf = GDD2
cilf = theta
dilf = Asin2
eilf = T_mean
filf = T_range

rout.AddNew
rout![grid] = rinwd.deg05
rout![Month] = rinwd.Month
rout![GDD_test] = gilf
rout![theta_test] = cilf
rout![asin_theta] = dilf
rout![Tm] = eilf
rout![Tr] = filf
rout.Update

rinwd.MoveNext
Loop
rinwd.Close: rout.Close
End Function
Function CalcAsin2(pDiddy)
Dim Asin2 As Double
Asin2 = Atn(pDiddy / Sqr(-pDiddy * pDiddy + 1))

End Function

Function CalcGDD(T_x, T_min, T_m, T_thresh, Asin2)
Dim GDD As Double
cow_pi = 3.14159265
GDD = 0
If Max_T > T_thresh And T_thresh > Min_T Then
GDD = (1 / cow_pi) * ((T_m - ThreshT) * ((cow_pi / 2) - Asin2(theta)) + (T_r * Cos(Asin2(theta))))
ElseIf T_thresh <= T_min Then GDD = T_m - T_thresh
Else: If T_thresh >= T_x Then GDD = 0
End If
End Function

 

[dhookom]

CalcGDD() doesn't return any value. I would expect to see a line prior to the End Function like:

Function CalcGDD(T_x, T_min, T_m, T_thresh, Asin2)
  Dim GDD As Double
  cow_pi = 3.14159265
  GDD = 0
  If Max_T > T_thresh And T_thresh > Min_T Then
    GDD = (1 / cow_pi) * ((T_m - ThreshT) * ((cow_pi / 2) - Asin2(theta)) + (T_r * Cos(Asin2(theta))))
   ElseIf T_thresh <= T_min Then GDD = T_m - T_thresh
   Else: If T_thresh >= T_x Then GDD = 0
  End If
  CalcGDD = GDD
End Function

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

This calling convention

Call CalcAsin2(theta)

Just throws the returned value of the function away. You probably want

ASin2 = CalcAsin2(theta)

and you need to modify CalcASin2 so that it returns a value which its doesn't do now. I made a slight modification because argument values of +1 or -1 will raise a "divide by zero" error.

Function CalcAsin2(pDiddy) As Double
Const PI As Double = 3.14159265
Select Case pDiddy 
    Case 1:     CalcAsin2 = PI / 2
    Case -1:    CalcAsin2 = (3 * PI) / 2
    Case Else:  CalcAsin2 = Atn(X / Sqr((-pDiddy * pDiddy + 1)) 
End Select
End Function

 

[Golom]

Sorry ... typo

Case Else:  CalcAsin2 = Atn(pDiddy  / Sqr((-pDiddy * pDiddy + 1))

 

[Golom]

I also notice that in the CalcGDD function you have

Function CalcGDD(T_x, T_min, T_m, T_thresh, [red]Asin2[/red])
    :
GDD = (1 / cow_pi) * ((T_m - ThreshT) * ((cow_pi / 2) - [red]Asin2(theta)[/red]) + (T_r * Cos([red]Asin2(theta)[/red])))

ASin2 is a calling argument but you are invoking it with an argument (Theta). Either call the function for ASin or lose the argument. ASin2 is not a function (at least from what I see of your code.)