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!

 

[PNGBill]

If you have the MoveIndate and MoveOutDate controls on rour report you should be able to have an unbound text box control with source as =DateDiff("d","MoveOutDate","MoveInDate")
Check DateDiff in your access help for exact syntax for different values.

In example above MoveOutDate is assumed to be the Name of the text box control.

 

[PNGBill]

Try this:

=DateDiff("d",[PolicyDate],[ReleaseDate])

 

[unnamedplayer]

No, no, I know that. Problem is, I need to see how many days a resident was there between a given time frame. So for example, if the person moved in on 5/29/10 and moved out 7/30/10 and I wanted to see how many days that person was occupying a space for the period between 5/1/10 and 6/30/10 I couldn't just subtract the move-in date and move-out date.

 

[MSAccessRookie]

No, no, I know that. Problem is, I need to see how many days a resident was there between a given time frame. So for example, if the person moved in on 5/29/10 and moved out 7/30/10 and I wanted to see how many days that person was occupying a space for the period between 5/1/10 and 6/30/10 I couldn't just subtract the move-in date and move-out date.

The same rules would apply, but you would need to determine the appropriate start and end date as well.


For instance:

• If the person moved in BEFORE the Start Date, you would use the Start Date (Unless they also moved out before the Start Date)
• If the person moved in AFTER the Start Date, you would use the Move-In Date (unless they also moved in after the End Date).
• If the person moved out BEFORE the End Date, you would use the Move-Out Date (Unless they also moved out before the Start Date).
• If the person moved out AFTER the End Date, you would use the End Date (Unless they also moved in after the End Date).

Youi can see how confusing this could become.

 

[unnamedplayer]

I know. That is the logic I would like to implement. I was hoping there is a way to do that other than writing nested IIf statements in the Control Source property of the textbox where I want to display the value.

 

[PNGBill]

Am I missing something here?

Move In Date is 29th May and Move Out Date is 30th July
Simple DateDiff.

But you want 1st May as Start Date - Makes sence, beginning of Move In Month.
And 30th June as End Date ?? where does this date come from ? or is this a Typo??
If you mean from start of Move In Month to End Of Move Out Month then this should be able to be nested into the DateDiff Function and if the dates are the same month, then you will just get the number of days of that month.