#Error# Until Fields in New Record completed

[Brockers]

Hi

I've created (and stole!) the 3 functions below, the first 2 taken from Tek-Tips to work out the Start and End of Months the 3rd Created by me to work out how much a customer should be billed for storage based on Total Days something has been stored in that month (MonthOfCharge) multiplied by a Rate (ChargeRate) that comes from the customers record.

e.g. A bill for May 2004 with a Customer Rate of 3, where storage is from (UK Date Format) 20/02/04 to 31/07/04 would incur a cost of 93 (31 days * Rate of 3)

It all works perfectly in the SubForm and the Subsequent Report for existing records but here is the problem...

When entering a new record in the SubForm the Calc Field shows #Error# Until the Values For FirstDate and LastDate are completed Thus creating the TotalDays used in the final calculation.

I have tried IIF's NZ's but I can't seem to get them in the right place or working!

Please Help, my head is pickled and Englands defeat on Penalties (again!) in Euro 2004 last night and subsequent drowning of sorrows hasn't helped much either!

Here's the code:

Public Function StartOfMonth(Inputdate As Date) As Date
    If IsDate(Inputdate) Then
        StartOfMonth = DateSerial(Year(Inputdate), Month(Inputdate), 1)
    End If
    
End Function

Public Function EndOfMonth(Inputdate As Date) As Date
    
    EndOfMonth = DateSerial(Year(Inputdate), Month(Inputdate) + 1, 0)
    
End Function

Public Function MonthlyCharge(MonthOfCharge As Date, FirstDate As Date, LastDate As Date, ChargeRate As Integer) As Currency

    Dim TotalDays As Long
    Dim BillStart As Date
    Dim BillEnd As Date
    
    BillStart = IIf(FirstDate < StartOfMonth(MonthOfCharge), StartOfMonth(MonthOfCharge), FirstDate)
    BillEnd = IIf(LastDate > EndOfMonth(MonthOfCharge), EndOfMonth(MonthOfCharge), LastDate)
    TotalDays = DateDiff("d", BillStart, BillEnd) + 1
    MonthlyCharge = IIf(TotalDays <= 0, 0, TotalDays * ChargeRate)
    
End Function

Regards

Brockers
Instructor
New Horizons Manchester England

 

[PHV]

You may consider something like this:
Public Function MonthlyCharge(MonthOfCharge, FirstDate, LastDate, ChargeRate As Integer) As Currency
If IsNull(MonthOfCharge) Or IsNull(FirstDate) Or IsNull(FirstDate) Then
MonthlyCharge = 0
Exit Function
End If
Dim TotalDays As Long
Dim BillStart As Date
Dim BillEnd As Date

BillStart = IIf(FirstDate < StartOfMonth(MonthOfCharge), StartOfMonth(MonthOfCharge), FirstDate)
BillEnd = IIf(LastDate > EndOfMonth(MonthOfCharge), EndOfMonth(MonthOfCharge), LastDate)
TotalDays = DateDiff("d", BillStart, BillEnd) + 1
MonthlyCharge = IIf(TotalDays <= 0, 0, TotalDays * ChargeRate)

End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Brockers]

Thanks PHV

It didn't quite work but it put me on the right line of thought (as did a couple of asprin!), rather than having it as an unbound field, I placed it in the query the subform is based on and put the IIf statement in there

IIf(IsNull([DateOut]),0,MonthlyCharge([Forms]![frmCustomer]![BillingMonth],[DateIn],[DateOut],[Rate]))

It seems being able to refer to the the Fields directly rather than using the Variable in the Code has got it working in this instance.

Cheers for your reply, it genuinely did give me an angle I hadn't spotted!

Regards

Brockers
Instructor
New Horizons Manchester England