Expression....is too complex

[LAS]

I want to display the elapsed time between start and end times in a report. I've written a little function (code below) and have put it in a query which is the recordsource of my report. But when I try to display this value by putting the espression in the report, I get an error message...The expression is typed incorrectly or is too complex.... What can I do to achieve my goal of displaying the elapsed time???? AND the total elapsed time???

tia
las


The item in the query that is the recordsource for my report.

ElapsedTime: fncElapsedTime([Reports]![rptTrackIStudentDetail]![Incident_Time],[Reports]![rptTrackIStudentDetail]![Incident_Return_Time])

The code that is behind "fncElapsedTime"

Public Function fncElapsedTime(dtStart As Date, dtEnd As Date) As String

On Error GoTo Err_ElapsedTime

gv_variant = DateDiff("n", dtStart, dtEnd)

fncElapsedTime = gv_variant

gs_Debug = "stop"
Exit_ElapsedTime:
Exit Function

Err_ElapsedTime:
is_temp = "fncElapsedTime: " & Err.Description
MsgBox is_temp
Resume Exit_ElapsedTime

End Function

 

[John W. Vinson]

I want to display the elapsed time between start and end times in a report. I've written a little function (code below) and have put it in a query which is the recordsource of my report. But when I try to display this value by putting the espression in the report, I get an error message...The expression is typed incorrectly or is too complex.... What can I do to achieve my goal of displaying the elapsed time???? AND the total elapsed time???

tia
las


The item in the query that is the recordsource for my report.

ElapsedTime: fncElapsedTime([Reports]![rptTrackIStudentDetail]![Incident_Time],[Reports]![rptTrackIStudentDetail]![Incident_Return_Time])

The code that is behind "fncElapsedTime"

Public Function fncElapsedTime(dtStart As Date, dtEnd As Date) As String

On Error GoTo Err_ElapsedTime

gv_variant = DateDiff("n", dtStart, dtEnd)

fncElapsedTime = gv_variant

gs_Debug = "stop"
Exit_ElapsedTime:
Exit Function

Err_ElapsedTime:
is_temp = "fncElapsedTime: " & Err.Description
MsgBox is_temp
Resume Exit_ElapsedTime

End Function

Do you get this error if one of the freport controls is NULL? As written
you'll probably get some such error, since the DateDiff() function doesn't
handle NULL.

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[LAS]

Thanks very much! Super helpful on a number of fronts.

You have given Access an impossible task, which results in this "too
complex" message.

In order to run the report, it has to run the query first to feed the data
to the report. But the query has to read the data from the report before
it can run the query. Since it can't run the query until the report is
already open, the query can't run so the report can't open. Access gives
up: it's all "too complex."

More significantly, you can't read a value from the report like that
anyway. Unlike forms, reports don't have a current record, so you can't
use the Reports!... expression like you can the Forms!... expression.
Perhaps you need to open a form first, and have the query read these
values from the form and use them in the report.

There's a third factor that regularly gives this "too complex" message,
and that's to do with data types. Your function returns a string. If the
query attempts to perform a numeric operation on this, it won't be able
to. That can result in Access complaining that what you've asked for is
not doable ("too complex")

I'm not clear if [Incident__Time] and [Incident_Return_Time] are actually
fields in a table? If so, just omit the
[Reports]![rptTrackIStudentDetail]!
from your expression. If not, use an unbound from with a couple of text
boxes, and read the expression from there. Set the Format property of the
text boxes to General Date, so Access understands the data type correctly.
More on that:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Finally, you don't need a function for this kind of task (unless it's just
for practice or as a demo.) You can use DateDiff() directly in the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I want to display the elapsed time between start and end times in a
report. I've written a little function (code below) and have put it in a
query which is the recordsource of my report. But when I try to display
this value by putting the espression in the report, I get an error
message...The expression is typed incorrectly or is too complex.... What
can I do to achieve my goal of displaying the elapsed time???? AND the
total elapsed time???

tia
las


The item in the query that is the recordsource for my report.

ElapsedTime:
fncElapsedTime([Reports]![rptTrackIStudentDetail]![Incident_Time],[Reports]![rptTrackIStudentDetail]![Incident_Return_Time])

The code that is behind "fncElapsedTime"

Public Function fncElapsedTime(dtStart As Date, dtEnd As Date) As String

On Error GoTo Err_ElapsedTime

gv_variant = DateDiff("n", dtStart, dtEnd)

fncElapsedTime = gv_variant

gs_Debug = "stop"
Exit_ElapsedTime:
Exit Function

Err_ElapsedTime:
is_temp = "fncElapsedTime: " & Err.Description
MsgBox is_temp
Resume Exit_ElapsedTime

End Function

 

[David W. Fenton]

The item in the query that is the recordsource for my report.

ElapsedTime:
fncElapsedTime([Reports]![rptTrackIStudentDetail]![Incident_Time],[
Reports]![rptTrackIStudentDetail]![Incident_Return_Time])

I would say eliminate this from your report's recordsource and
calculate it in the presentation layer of your report.

If you need it in the recordsource (because you're sorting or
grouping on it), then don't pass the reference to a control, just
pass the fields themselves.

Last of all, I don't know what the purpose of the function is --
it's just a wrapper around this:

DateDiff("n", Incident_Time, Incident_Return_Time)

I would just use that as the controlsource of a control in the
report, or for the ElapsedTime: field in the recordsource. Either
way, you'd want to wrap it in an IIf() to return nothing if one or
both is Null:

IIf(IsNull(Incident_Time) Or IsNull(Incident_Return_Time), Null,
DateDiff("n", Incident_Time, Incident_Return_Time)

Again, you seem to be choosing the most complicated possible way of
doing simple tasks -- there is no need for a UDF here at all,
especially since your UDF doesn't have anything built into it for
handling Nulls.