Help a noob (calculating fields and logic)

[unnamedplayer]

Hi all, I am a total noob to access programming so I am hoping someone can help. I am trying to make a report that displays the total number of days someone has occupied a residence during a given time frame. My report currently displays a Move-In Date and Move-Out Date which is pulled from a basic query on the appropriate table. I need a total days field as well. I have added a text box that will hold this calculated value. I was originally using the Control Source property and nested IIf statements to calculate the total, but there were some errors I made in logic that really need me to perform a little work in determining what dates are used to calculate the total days. Is there a way to perform some conditional processing with my Move-In and Move-Out dates and then use that calculated value in my report? Thanks!

[pbaldy]

You could use DateDiff() if you just want the difference in days. If you need to perform more complicated processing, you can either create a formula or a VBA function that can be called from anywhere (presumably feeding it the two dates). Not knowing what might be entailed in your "little work", I can't be more specific than that.

[unnamedplayer]

You could use DateDiff() if you just want the difference in days. If you need to perform more complicated processing, you can either create a formula or a VBA function that can be called from anywhere (presumably feeding it the two dates). Not knowing what might be entailed in your "little work", I can't be more specific than that.

I think a function is what I am after. Would it be possible to feed a function dates from my report, do whatever calculations I need to do, and then return the results to be displayed in the report?

Thanks!

[pbaldy]

Sure; here's a primer:

http://www.baldyweb.com/Function.htm

[unnamedplayer]

Ok, well I am making progress. I made a function that accepts for date parameters and set that function as the control source of the text box on my report that I want to display the final result. The first time I ran the report it seemed to call the function fine, but I had a problem in my function. I was trying to test if one of the date arguments was null using If MyDate Is Null Then, but this resulted in a type mismatch. I have tried changing it to If IsNull(MyDate) Then, but now when I try to run the report I am asked to enter a value for Parameter TotalDays (TotalDays is the name of my function). How did I break it??

[pbaldy]

Can you post the function, and how you call it?

[unnamedplayer]

Sure here is the function to calculate the total number of days someone stayed during a given time frame:

Public Function TotalDays(MoveInDate As Date, MoveOutDate As Date, StartDate As Date, EndDate As Date)
    'Is client still here?
    If IsNull(MoveOutDate) Then
        'Did they move in before the start date?
        If MoveInDate < StartDate Then
            TotalDays = EndDate - StartDate
        Else
            TotalDays = EndDate - MoveInDate
        End If
    Else 'Client was discharged
        'Were they discharged during time frame?
        If MoveOutDate > StartDate And MoveOutDate < EndDate Then
            'Did they move in during time frame?
            If MoveInDate > StartDate And MoveInDate < EndDate Then
                TotalDays = MoveOutDate - MoveInDate
            Else
                TotalDays = MoveOutDate - StartDate
            End If
        Else 'Client was not discharged during time frame
            'Did they move in during time frame?
            If MoveInDate > StartDate And MoveInDate < EndDate Then
                TotalDays = EndDate - MoveInDate
            Else
                TotalDays = EndDate - StartDate
            End If
        End If
    End If
    
End Function

I am calling the function from the Control Source property of the text box that I want to display the total in using:

=TotalDays([MoveInDate],[MoveOutDate],CDate([StartDate]),CDate([EndDate]))

It seems to be working now after I restarted Access. My new problem is that for some results I am receiving #Error when running the report. It seems to be isolated to the first IF condition where the client is not discharged, but they moved in before the start date. I don't know why I am getting #Error when I am just trying to subtract the EndDate from StartDate.

[pbaldy]

A couple of issues, since it appears that your dates can be Null. First, CDate() will throw an error if the value is Null, so you may not want to use it unless you're sure those two will have a value. Second, a variable defined as a Date data type can't accept a Null. You can use Variant instead of Date for any that might be Null, and then test in your function as you already are for the Nulls:

Public Function TotalDays(MoveInDate As Date, MoveOutDate As Variant, StartDate As Date, EndDate As Date)

[unnamedplayer]

A couple of issues, since it appears that your dates can be Null. First, CDate() will throw an error if the value is Null, so you may not want to use it unless you're sure those two will have a value. Second, a variable defined as a Date data type can't accept a Null. You can use Variant instead of Date for any that might be Null, and then test in your function as you already are for the Nulls:

Public Function TotalDays(MoveInDate As Date, MoveOutDate As Variant, StartDate As Date, EndDate As Date)

Oh I think that solved it! As I was debugging, I saw that my records that had a null MoveOutDate were not being processed through my function. Changing the MoveOutDate to Variant seems to have solved that.

Thanks!

[pbaldy]

Excellent, glad we got it sorted out.