Problem with control source for text box on report

[VivN]

I want to use an expression as the control source for a text box in a
report (Access 2000).
Whilst I have sucessfully used these simple ones

=TimeToSingle(TotalHours([TimeFrom],[TimeTo]))
=TimeToSingle(BHolHours([dtmShiftDate],[timefrom],[timeto]))

I get problems when I include a text parameter Scale (control just
displays #error)

=TimeToSingle(SaturdayHours([dtmShiftDate],[timefrom],[timeto],[Scale]))

Where TotalHours, BHolHours and SaturdayHours are user defined
functions.

[Scale] is either null or has a value.
I tried forcing [Scale] to be an empty string (if scale is null)
without success.
If I change the parameter type in the function call to variant, the
parameter value is error 0 at run time (type mismatch).

The function runs fime if used to calculate the value as a calculated
column in a query.

Is this expression too complicated for a report field control source?

[Mike MacSween]

Vivienne

Just off the top of my head, I've had problems with conflicts of control
name and field name, more on reports than on forms. That is, if you drag a
field from the field chooser thingy onto a report it is automatically given
the same name as the field. So then when you come to refer to it in an
expression you might be referring to the control, not a field in the table.
I often end up renaming the controls txtFieldName.

Have you tried just evaluating SaturdayHours in the debug window, to find
out if your problem is with the function, rather than the report? Oh ,sorry,
you've run it as a query.

SaturdayHours looks a bit numeric to me. Are you sure you wouldn't be better
forcing nulls to a numeric 0 rather than an empty string? Though I don't
know how SaturdayHours works. Or making the last argument optional?

By the way. The naming of the functions implies to me that you're writing
functions to account for different rates of pay for different 'special'
times of work. I'd try to get that sort of information into tables. If you
can:

tblRatesByDayOfWeekOrHoliday:

WorkDate Rate
Monday 1.0
..
..
Friday 1.0
Saturday 1.5
Sunday 1.5
BH 2.0
XmasDay 2.0
NYDay 2.0

Then some function, highestRate (workDate) that for any day finds what the
highest rate is for that particular date.

Then your calculating function calls highestRate x hoursWorked. Or
something.

Of course you will need a table of bank holidays (or dates that you pay at
the bank holiday rate). But the rest can be got from the date.

Mike

"VivN" <vi*****************@whnt.nhs.uk> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...

I want to use an expression as the control source for a text box in a
report (Access 2000).
Whilst I have sucessfully used these simple ones

=TimeToSingle(TotalHours([TimeFrom],[TimeTo]))
=TimeToSingle(BHolHours([dtmShiftDate],[timefrom],[timeto]))

I get problems when I include a text parameter Scale (control just
displays #error)

=TimeToSingle(SaturdayHours([dtmShiftDate],[timefrom],[timeto],[Scale]))

Where TotalHours, BHolHours and SaturdayHours are user defined
functions.

[Scale] is either null or has a value.
I tried forcing [Scale] to be an empty string (if scale is null)
without success.
If I change the parameter type in the function call to variant, the
parameter value is error 0 at run time (type mismatch).

The function runs fime if used to calculate the value as a calculated
column in a query.

Is this expression too complicated for a report field control source?

[VivN]

Mike
I thought the naming conflict was a winner - but the this is not the
solution.
I have had problems in the past in Access 97 where a string
concatenation expression worked happily in a query but not for the
calculated field in a report.
I think it could be to do with the string argument not getting properly
formatted, but find it impossible to debug a function call made this
way - breakpoints and message boxes don't get fired.

I don't have the option of a table lookup - this report goes to payroll
who work out the fees - I just have to work out how many hours each
person worked in each category (weekday, sat, sun, unsocial, bank
holiday).

Vivn

[Mike MacSween]

"VivN" <vi*****************@whnt.nhs.uk> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

Mike
I thought the naming conflict was a winner - but the this is not the
solution.
I have had problems in the past in Access 97 where a string
concatenation expression worked happily in a query but not for the
calculated field in a report.
I think it could be to do with the string argument not getting properly
formatted, but find it impossible to debug a function call made this
way - breakpoints and message boxes don't get fired.
What's the code for your 4 functions then?

And I assume that 3 of the parameters are datetime, what type is Scale?

I'll try it here.
I don't have the option of a table lookup - this report goes to payroll
who work out the fees - I just have to work out how many hours each
person worked in each category (weekday, sat, sun, unsocial, bank
holiday).

Sure. Not for fees maybe, but you seem to have 3 functions that are doing
different things based upon the day of week of holiday type. In which case
are you feeding data in manually? That's what I meant and wrap those 3
functions into one, Access can work out the DOW and a short table of BH etc.
could catch exceptions. But I'm only guessing, I don't really know how it's
being called.

Mike

[VivN]

OK here it is -
SaturdayHours works out hours worked on a saturday, uses IsBHol also
listed below.
As you will see, there is a table to hold bank hol days for look up
purposes.
The database is used to book staff into shifts (these do not have fixed
start/end times)
Admin person then records if each person worked that shift, then prints
the report to send to payroll.
Staff who are in charge get an enhanced rate, this is different from
the rate that their staff grade normally gets.
All rather complicated..

VivN

@@@@@@@@@@@@@@@@@@@@@@@
Public Function SaturdayHours(pDate As Date, pStart As Date, pEnd As
Date, pScale As Variant) As Date
'Work outs saturday hours
dtmTempHours = 0
If pDate < #1/10/2004# And pScale = "NHZZ" Then
SaturdayHours = Format(dtmTempHours, "hh:mm")
Exit Function
End If
'Work out if night shift - covers two days
If pStart > pEnd Then
'Covers two days
If Weekday(pDate) = vbSaturday And Not IsBHol(pDate) Then
'Midnight - pStart = saturday hours
dtmTempHours = 24 - pStart
Else
If Weekday(pDate + 1) = vbSaturday And Not IsBHol(pDate + 1)
Then
dtmTempHours = pEnd
End If
End If
Else
'one day
If Weekday(pDate) = vbSaturday And Not IsBHol(pDate) Then
dtmTempHours = pEnd - pStart
End If
End If
SaturdayHours = Format(dtmTempHours, "hh:mm")
End Function
@@@@@@@@@@@@@@@@@@@@@@@@@
Public Function IsBHol(pDate As Date) As Boolean
'This chunk of code was put in because of the problem caused by Bank
Holidays
'not being recognised. This was due to the computer puttting any low
dates into
'American date format.

'================================
Dim var1 As Variant 'DATE
Dim var2, var3, var4 As Variant

var1 = CStr(pDate)
If Left(var1, 2) <= "12" Then
var2 = Left(var1, 3)
var3 = Mid(var1, 4, 3)
var4 = Right(var1, 2)
var1 = var3 & var2 & var4
var1 = CDate(var1)
Else
var2 = Left(var1, 3)
var3 = Mid(var1, 4, 3)
var4 = Right(var1, 2)
var1 = var3 & var2 & var4
var1 = Format(CDate(var1), "MM/DD/YY")
End If
'MsgBox var1

'================================

'Check whether a date is bank holiday, uses lookup from bank holiday
table
If DLookup("BankHolidayDate", "tblBankHoliday", "BankHolidayDate = #" &
var1 & "#") Then
IsBHol = True
Else
IsBHol = False
End If
End Function