Thanks for your reply. I’ve been experimenting with VBscript, reworking
the
DAP.
In Access I deleted the column using my Access function “duration()”
from
the underlying query, and created a new DAP. This DAP opens fine from
SharePoint, no longer throwing the “Data provider failed while executing
a
provider command” and “Undefined function ‘duration’ in
expression” errors.
I then added a textbox to the Header section of the DAP and set its Id to
“Elapsed_Time”. I also added a label to the Caption section of the
DAP, set
its Id to Elapsed_Time_Label and the InnerText to Elapsed_Time.
I saved the DAP in Access then opened it again successfully from
SharePoint.
The other columns populate with data; my Elapsed_Time column appears but
has
no data at this point.
I closed the DAP, went back to Access, opened the DAP in design mode,
opened
the script editor, and added the following script/function at the tail end
of
the HEAD section:
<SCRIPT language=vbscript>
Option Explicit
Function duration(AdmitDate,AdmitTime,DispoDate,DispTime)
Dim myAdmitHours
Dim myAdmitMinutes
Dim myDischargeHours
Dim myDischargeMinutes
Dim temp
Dim temp2
myAdmitHours = Int(Left(myAdmit, 2))
myDischargeHours = Int(Left(myDischarge, 2))
myAdmitMinutes = Int(Right(myAdmit, 2))
myDischargeMinutes = Int(Right(myDischarge, 2))
If myAdmitDate = myDischargeDate Then
temp = myDischargeHours - myAdmitHours
Else
temp = 24 + (myDischargeHours - myAdmitHours)
End If
temp2 = myDischargeMinutes - myAdmitMinutes
If temp2 < 0 Then
temp = temp - 1
temp2 = 60 + temp2
End If
duration = cstr(temp) & "hrs" & trim(cstr(temp2))
&
"mins"
End Function
</SCRIPT>
Note: AdmitDate, AdmitTime, DispoDate, and DispTime are other columns in
the
DAP and the underlying query. They are also visible in the pull-down list
of
the ControlSource property for textboxes. My first question is: can my
function recognize these fields/columns as arguments? (The Access
function
can.)
Being methodical, I saved the DAP in Access, then opened it again from its
SharePoint do***ent folder. No problems, but still no data in the
Elapsed_Time column.
I closed the DAP, went back to Access, and opened the DAP in design mode.
My second question is now: How do I get the Elapsed_Time textbox to use
the
vbscript function and display the calculated result for each row?
For my Elapsed_Time textbox I tried entering:
=duration(AdmitDate,AdmitTime,DispoDate,DispTime) into the ControlSource
property. This throws the error: The expression
“duration(AdmitDate,AdmitTime,DispoDate,DispTime)” is not valid.
duration(AdmitDate,AdmitTime,DispoDate,DispTime) into the ControlSource
property. This throws the error: Invalid property value.
=duration(AdmitDate,AdmitTime,DispoDate,DispTime) into the DefaultValue
property. This doesn’t throw any errors; neither does it render any
data
into the Elapsed_Time field when I view the DAP.
duration(AdmitDate,AdmitTime,DispoDate,DispTime) into the DefaultValue
property. This doesn’t throw any errors; it renders the word
“duration” in
the Elapsed_Time field when I view the DAP.
either variation into the AlternateDataSource property. This doesn’t
throw
any errors; neither does it render any data into the Elapsed_Time field
when
I view the DAP.
++++++
Just for comparison, here is the Access version of the function, which
works
fine:
Public Function duration(myAdmitDate As Date, myAdmit As String,
myDischargeDate As Date, myDischarge As String) As String
Dim myAdmitHours, myAdmitMinutes, myDischargeHours, myDischargeMinutes
As Integer
Dim temp, temp2 As Integer
'first let's separate the hours and minutes for the admit and
discharge
myAdmitHours = Int(Left(myAdmit, 2))
myDischargeHours = Int(Left(myDischarge, 2))
myAdmitMinutes = Int(Right(myAdmit, 2))
myDischargeMinutes = Int(Right(myDischarge, 2))
If myAdmitDate = myDischargeDate Then
temp = myDischargeHours - myAdmitHours
Else
temp = 24 + (myDischargeHours - myAdmitHours)
End If
temp2 = myDischargeMinutes - myAdmitMinutes
If temp2 < 0 Then
'the admission time is greater than the discharge time
'that means we need to subtract an hour from temp
'and adjust the hour
temp = temp - 1
temp2 = 60 + temp2
Else
'the discharge time is greater than the discharge time
'no action necessary
End If
duration = Str(temp) & "hrs " & Trim(Str(temp2)) & "mins"
End Function
+++++
Thanks again in advance for any help.
--
Christian Bahnsen
"Ken Snell (MVP)" wrote:
> You have to put the user-defined function in the script module of the
DAP
> itself so that the script can "see" and run it. You need to put it in as
a
> separate script procedure.
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
>
>
> "Christian Bahnsen" <ChristianBahnsen@[EMAIL PROTECTED]
> wrote
in
> message news:3B3692A5-BF26-41E3-AEE8-4FEAB8A005D8@[EMAIL PROTECTED]
> >I wrote a public function in an Access module to calculate elapsed time
> > between two values. It works fine in Access queries and in a data
access
> > page (DAP) in Access during the design phase. But when I try to open
the
> > DAP
> > outside Access from its SharePoint folder the DAP throws an error,
> > apparently
> > unable to access the user-defined function.
> >
> > Is there any way for DAPs to use user-defined functions?
> >
> > --
> > Christian Bahnsen
>
>
>
|
