summing Calculated Fields

[reel knotty]

I have a form I need to Sum the milesfromdll field in the footer. The milesfromdll field is calculated by calling an API and returning the number of miles between 2 points. When I try to =Sum([milesfromdll]) All i get is an #error. Any ideas?

As always thanks in advance!!
Nate

 

[reel knotty]

so there is no way to sum a calculated field??

 

[dcx693]

What exactly do you mean by

The milesfromdll field is calculated by calling an API

do you really mean a function? or an API call?

 

[reel knotty]

Milesfromdll comes from this


Public Declare Function RouteStopPairByParm Lib "batch32.dll" _
(ByVal pStop1 As String, ByVal pStop2 As String, _
ByVal pParams As String, ByVal pErrFile As String, _
ByVal pPath As String, _
ByRef pMiles As Double, _
ByRef pHours As Byte, ByRef pMins As Byte) As Byte



Public Function fmiles(pZip As String, Zip As String) As Double
Dim x As Byte
Dim Miles As Double
Dim hours As Byte
Dim mins As Byte
Dim parms As String
Dim dpath As String
Dim errorfile As String

parms = "-xx -h14.0 -p"
dpath = "C:\program Files\Prophesy\Common\Tripsdb"
errorfile = "err.txt"


x = RouteStopPairByParm(pZip, Zip, parms, errorfile, dpath, Miles, hours, mins)


If x = 1 Then
fmiles = Miles

Else
fmiles = 0


End If

End Function

 

[dcx693]

How is your form set up? Continous view? How many times does the [milesfromdll] field occur? A few? A variable number?

 

[reel knotty]

it would be a variable number. from 2-10 times would be the normal range. and it is continuous

I was thinking if I could make the call in the underlying query I would be OK but I have not been able to make that work.

 

[dcx693]

milesfromdll is the name of the control on the form, right? And the controlsource of the control is the function that you call?

If so, an expression like: =Sum([milesfromdll])
won't work because Access doesn't sum over a "control". It can sum over the underlying field values in the controls. So if the controlsource of the milesfromdll control was a field from the form's recordsource called "miles", then =Sum([miles]) would work.

The point being that Access needs to get to the underyling data to sum the values and I don't know that it can do that in this case.

 

[reel knotty]

Yes
Yes
and Doh

Any ideas on what i can do to make this work even if its redoing everything? I just spent $1700 for the API data and really need to be able to sum the miles to get the value.

 

[dcx693]

The results from the [milesfromdll] controls are not stored in your database, are they? If they are bound fields and you are filling in their values using the API call, then you can sum the underlying field.

Otherwise, if they're just being displayed on scree, truth is I just don't know if it's possible - easily. You could use some VBA code to go through the form recordset, calling your API for each record and adding up the results and placing that into a field in the footer.

 

[reel knotty]

thats correct they are not stored because the order in which the run is made changes constantly so the miles would change as well. I was trying to figure out some code. I will focus on that route and post my results incase anyone else has the same issue.

Thanks for your time!!

 

[Rich]

Why doesn't the function work in a query?

 

[reel knotty]

I actually need to run 2 functions and they are as follows:

'**************************************************************
Function PrevRecVal(shipmentlookup As Form, Trans As String, KeyValue, _
zip As String)
Dim pZip As DAO.Recordset

On Error GoTo Err_PrevRecVal

' The default value is zero.
PrevRecVal = 0

' Get the form recordset.
Set pZip = shipmentlookup.RecordsetClone

' Find the current record.
Select Case pZip.Fields(Trans).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
pZip.FindFirst "[" & Trans & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
pZip.FindFirst "[" & Trans & "] = #" & KeyValue & "#"
'Find using text data type key value?
Case DB_TEXT
pZip.FindFirst "[" & Trans & "] = '" & KeyValue & "'"
Case Else
msgBox "ERROR: Invalid key field data type!"

Exit Function
End Select

' Move to the previous record.
pZip.MovePrevious

' Return the result.

PrevRecVal = pZip(zip)


Bye_PrevRecVal:
Exit Function
Err_PrevRecVal:
Resume Bye_PrevRecVal
End Function

**Which creates the pzip data****
and

Public Declare Function RouteStopPairByParm Lib "batch32.dll" _
(ByVal pStop1 As String, ByVal pStop2 As String, _
ByVal pParams As String, ByVal pErrFile As String, _
ByVal pPath As String, _
ByRef pMiles As Double, _
ByRef pHours As Byte, ByRef pMins As Byte) As Byte



Public Function fmiles(pZip As String, zip As String) As Double
Dim x As Byte
Dim miles As Double
Dim hours As Byte
Dim mins As Byte
Dim parms As String
Dim dpath As String
Dim errorfile As String

parms = "-xx -h14.0 -p"
dpath = "C:\program Files\Prophesy\Common\Tripsdb"
errorfile = "err.txt"




x = RouteStopPairByParm(pZip, zip, parms, errorfile, dpath, miles, hours, mins)


If x = 1 Then
fmiles = miles

Else
fmiles = 0


End If

End Function


** which returns the miles ****

I never got as far as trying the routestoppairbyparm function because i could not get the prevrecval to work through the query.

 

[Rich]

Use a report, it'll be much easier