Calculate 1 of 2 fields for Dates or Hours? 1

[misscrf]

I have 3 fields on a form:
Start Date/Time
End Date/Time
Hours Length

When a user is on the form, they naturally tab to the Start Date/Time first. On click of that field a mini calendar form pops up for them to choose the date and time.

The same mini form pops up for the End Date/Time.

Because of the information these users have, they will typically have maybe 1 of these pieces of information now, and 1 more later.

We cannot control which they enter first, but once any 2 of those are filled out, we want the calculate the 3rd based on the 2 that are entered. Make sense?

So, if...

1) Start Date/Time is filled out, and End Date/Time is filled out, then we need to calculate Hours Length between those two.

2) Start Date/Time is filled out, and Hours Length is filled out, then we need to calculate End Date/Time as the Start Date/Time plus Hours Length.

3) End Date/Time is filled out, and Hours Length is filled out, then we need to calculate Start Date/Time as the End Date/Time minus Hours Length.

I want to have this form auto update which ever field can calculate at any time. We store the information in fields so we can run reports later and not have to worry about all of these calculations.

Any ideas how I can do this, please?

I'm hoping this will be a fun challenge!!!



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

p.s. I put this in the Form's On Current...

If Me.dtDateStart Is Not Null And Me.dtDateEnd Is Not Null Then
Me.intLength = DateDiff("h", Me.dtDateStart, Me.dtDateEnd)
End If

I figure if I can get that to work, then I can write out the other scenarios in the if statement (else if etc)

When I try to go to the form preview though, I get a run-time error 424 Object Required.
:-(


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

ok, I changed it to this:

If not isnull(Me.dtDateStart) And not isnull(Me.dtDateEnd) Then
Me.intLength = DateDiff("h", Me.dtDateStart, Me.dtDateEnd)
End If

Now I don't get the error. The calculation refeshes when I load the form if there are values in those fields, but even with that code on the on change of the fields and after update, it doesn't update the calculation if I change the date.

Any ideas, please?


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Why are you storing a value that can be calculated? This is generally considered bad practice.

Changing the dates via code will not run the On Change or After Update event.


Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Thank you for responding.

I am storing the calculation, because many times the user will enter the start and the hours and we are left calculating the end. We don't always calculate the hours. I need to be able to calculate the 3rd piece of the puzzle at any time.

I have seen that question asked many times and I don't understand why it is a problem to calculate a value and store it in a field. Why is this so hard? I thought this would be the easiest thing to do.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

why it is a problem to calculate a value and store it in a field

Apparently this is causing you enough problems that you have to come here and ask for assistance. Also every where a user can possibly edit the start or end dates, the 3rd field will also need to be updated.

What if you rely on the intLength field to pay someones wages and[ol][li]someone adds a new form to your application and doesn't realized the value is stored[/li]
[li]you have code or a query that updates the start or end but not the intLength[/li]
[li]someone edits the values directly in the table[/li]
[li]you build another front-end in either Access or .net or web interface that doesn't update intLength[/li]

[/ol]

A calculated value will always show the correct number.

You can simply use a text box with a control source of:
Control Source: =DateDiff("h",[dtDateStart],[dtDateEnd])

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

Additionally, you can do the calculations on your form without storing the calculated value in the table.
Store the dtStart and dtEnd fields only. If you allow users to enter dtStart and intLength values, perform the
calculation to find dtEnd. You still have no need to store the intLength field.

Randy

 

[misscrf]

Thank you for all the responses. I ended up creating a button, so that they can calculate at any time, but they have to hit the button to run the calc.
This is the code:

Private Sub cmdRunCalc_Click()
If Not IsNull(Me.dtDateStart) And Not IsNull(Me.dtDateEnd) And IsNull(Me.intLength) Then
Me.intLength = DateDiff("h", Me.dtDateStart, Me.dtDateEnd)
ElseIf Not IsNull(Me.dtDateStart) And Not IsNull(Me.intLength) And IsNull(Me.dtDateEnd) Then
Me.dtDateEnd = DateAdd("h", Me.intLength, Me.dtDateStart)
ElseIf IsNull(Me.dtDateEnd) And Not IsNull(Me.intLength) And IsNull(Me.dtDateStart) Then
Me.dtDateStart = DateAdd("h", (Me.intLength * -1), Me.dtDateEnd)
ElseIf Not IsNull(Me.dtDateStart) And Not IsNull(Me.dtDateEnd) Then
Me.intLength = DateDiff("h", Me.dtDateStart, Me.dtDateEnd)
ElseIf Not IsNull(Me.dtDateStart) And Not IsNull(Me.intLength) Then
Me.dtDateEnd = DateAdd("h", Me.intLength, Me.dtDateStart)
End If

End Sub

As long as people are diligent with this, is the code good?

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I don't care for lots of nested If or ElseIf statements. I prefer Select Case. I would also move the code to its own sub and then call it from the On Click of the command button and possibly other places. Your code also lacks any comments :-(

Looking at your expression, I wonder if you should have ()s around some expressions following your " And ".

I'm not going to test this since I don't agree with storing all three values ;-)


Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

the only reason I used if statements is because I don't know case statements well and I also don't know how to create a sub and call it to another sub.

The only reason I didn't comment is because, as an if statement it seemed pretty self explanatory what it is doing. lol

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[Ray1127]

Since you're testing 3 values I'm not sure you can use a Select Case statement. I believe those are based on a single value. Although you could nest them I'm not sure that would give much of an improvement over the If statements. However looking at your If statement I see a couple of problems.

The main problem is there are too many of them. If you have 3 values and 2 of those values must have data to calculate the 3rd. You only need 3 If statements

If Not IsNull(value1) and not isnull(value2) Then
Calculate Value3
Else If Not isNull(value1) and Not isnull(value3) Then
Calculate Value2
Else If not IsNull(value2) and Not isnull(value3) Then
Calculate Value1
End if

Using booleans where the 1st column is value1, 2nd is value2 and 3rd is value 3

False False False - Cannot be calculated 0 have values
False False True - Cannot be calculated 1 has value
False True False - Cannot be calculated 1 has value
False True True - Value 1 can be calculated
True False False - Cannot be calculated 1 has value
True False True - Value 2 can be calculated
True True False - Value 3 can be calculated
True True True - This is the Tricky one since there is a value in every column any value 1, 2 or 3 can be calculated. The issue is which value should be calculated?

 

[randy700]

As Ray1127 states, it's possible to have all 3 with values. How do you intend to handle the situation if someone
makes changes?


Randy

 

[TheAceMan1]

Howdy misscrf . . .

I know your dying to get going so I'll jump right into the code ... you can ask questions later!
[ol][li]In the declarations section of the forms code module, copy/paste the following:

[blue]Private flgChange As Byte[/blue]

[/li]
[li]Next copy/paste the following to the same code module:

[blue]Private Sub dtDateEnd_AfterUpdate()
   Call CalcMissing
End Sub

Private Sub dtDateStart_AfterUpdate()
   Call CalcMissing
End Sub

Private Sub intLength_AfterUpdate()
   Call CalcMissing
End Sub

Private Sub dtDateEnd_Change()
   flgChange = 2
End Sub
Private Sub dtDateStart_Change()
   flgChange = 1
End Sub

Private Sub intLength_Change()
   flgChange = 4
End Sub

Public Function CalcMissing()
   Dim BinVal As Byte

   'Note Binary Bit Positions: dtDateStart=1, dtDateEnd=2, intLength=4
   BinVal = Abs(IsDate(Me.dtDateStart) = True) + _
            Abs(IsDate(Me.dtDateEnd) = True) * 2 + _
            Abs(IsNumeric(Me.intLength) = True) * 4
            
   'Set BinVal by deleting sought value from all three!
   If BinVal = 7 Then 'All data present but edit was made
      If flgChange = 1 Or flgChange = 2 Then
         Me.intLength = Null
         BinVal = 3
      ElseIf flgChange = 4 Then
         Me.dtDateEnd = Null
         BinVal = 5
      End If
   End If
   
   flgChange = Null
         
   'Calculations Here
   If BinVal = 3 Then 'calculate hours
      Me.intLength = DateDiff("h", Me.dtDateStart, Me.dtDateEnd)
   ElseIf BinVal = 5 Then 'calculate dtDateEnd
      Me.dtDateEnd = DateAdd("h", Me.intLength, Me.dtDateStart)
   ElseIf BinVal = 6 Then 'calculate dtDateStart
      Me.dtDateStart = DateAdd("h", (Me.intLength * -1), Me.dtDateEnd)
   End If

End Function[/blue]

[/li]
[li]Perform your testing ...[/li][/ol]

Ask any questions you like ...

[blue]Your Thoughts? . . .[/blue]


See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[misscrf]

Aceman. This is amazing!!!! You trapped every issue and gave me an answer I never would have found, but always wanted!!!! I give you a STAR and I will try to implement this and test it out. I will let you know how it goes. I don't have any questions yet, except how did you do this??? lol It all makes sense and I get the logic, but I have never done this where you provide a bit value for the logic to pass through. It's incredible.

Thanks for taking all the time to write this out. I hope it helps lots of people besides me!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

ok, so I put in all the code. So far I tested changing dates and nothing seems to happen. I click on the start date and the mini form comes up. I choose a date and close the form and the field updates to the new date and time. There were already values in the end date and the length. I would think it would update the length, but this is where I would think Randy and Ray;s question would come in to play. Do I say the end date is good or the length is good? Here I would say if I change the start date, the end is still good and just recalc the hours.

I can also answer the others that might come up. If the end is changed, keep the start and calc the hours. If the hours are changed, keep the start and recalc the end.

So at this point, I have the code in, but not sure those scenarios are getting trapped. How would I do that, please?

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

misscrf,
I think your issue might be

Changing the dates via code will not run the On Change or After Update event.

What happens if you type in the dates rather than use your calendar form?

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Yeah, I was thinking about that dhookom. My only problem is that typing in a date and time format is really difficult and I wanted to make it easier for the people entering the data. They originally asked for calendars to pick dates from, just so they wouldn't have to type the values the right way. I messed around with an input mask, but one for date and time in 1 field is really hard to make someone type just right.

Do you have a way around that?

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[TheAceMan1]

misscrf . . .

Calculations are setup to be triggered by the [blue]AfterUpdate[/blue] event of the controls. However these events won't occur if the dates are written thru VBA (aka the calendar). Your [blue]Length[/blue] field should work fine as your using the user interface here. So for testing rem the line(s) that open the calendar form and enter manually.

To circumvent non-trigger of AfterUpdate by the calendar, you simply need to run the function [purple]CalcMissing[/purple] thru the calendar ... after you set the date. Just add the following line to the code in the calendar:

[blue]   Call Forms.[purple][B][I]FormName[/I][/B][/purple][/blue]

Also when all three values are given here's the skinny:
[ol][li] Dates have priority!. Re-edit either date and setup to calc length by removing length.[/li]
[li]Re-edit Length and setup to calc DateEnd by removing DateEnd.[/li][/ol]

Your Thoughts? . . .

BTW ... I'm at work and may not be able to get to this until this evening.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

Sorry ...

The code line should be:

[blue]   Call Forms.[purple][B][I]FormName[/I][/B][/purple].CalcMissing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[misscrf]

I have this code on the command button of the minidatetime form that takes the date and time for both the start and the end:

Private Sub cmdOK_Click()

    If Nz(datSelected, #12:00:00 AM#) = #12:00:00 AM# Then datSelected = Date
    txtResult = Me.txthr.Value & ":" & Me.txtmm.Value & " " & Me.txtap.Value
'    lngResult = Me.ogResult.Value
    'Close and set value(s)
    DoCmd.Close acForm, "frmMiniDateTime"
    ' Determine what value and format the calling control should use
   
            Screen.ActiveControl.Format = "m/d/yyyy h:nn am/pm"
            datSelected = datSelected + txtResult
 
    Screen.ActiveControl.Value = datSelected
    Call Forms.[frmFormName].CalcMissing
    
End Sub

When I try to test that, I get a run-time error '94' invalid use of null on the line that calls the function.

I researched this and you even made it a public function, so it really should not be bugging out like this. I appreciate that you have a day job and thanks for continuing to work through this with me. When you get a chance to look at this that would be great. I will get the email reply.

Thanks!



misscrf

It is never too late to become what you could have been ~ George Eliot