DateDiff in Table

[Downie77]

Hi All, I am still learning and need help!!
Can I use the DateDiff function in a table? I have the formula: DateDiff("d",[Approved Date],[Check Date]) but when I try this I get a message that it doesn't recognize Approved Date?? What am I doing wrong??

 

[jzwp22]

Calculated values such as you describe are generally not stored in the table but calculated on the fly when you need them in forms, queries or reports.

 

[Downie77]

So I can set up a field in my form? That is what I really need. I have people who are going to be entering stuff in a form and I need them to know if it is over 30 days between 2 of the fields. I will try that if I can. Thanks!

 

[Downie77]

I got it! Thank you for saving my sanity!

 

[jzwp22]

You will have to use the actual form control names that hold the date values. If you used the form wizard to create the form, the field names and control names will be synonymous. If you have spaces in the control names, make sure to enclose them in square brackets as you showed earlier.

That was quick; glad to hear that you got it worked out!

 

[Downie77]

Wait.....
It calculates the correct number of days for my first record and then that same # of days is on all other records. What did I do wrong?

 

[DCrake]

Where and what are you performing the calculation?

You should either use it in the Default Value of the calculated unbound form control or using the Form On Current event in vba.

 

[DCrake]

You may also need to use it on the after update evebts of the dates that can change.

 

[Downie77]

I have the box set as unbound and I have my formula in the default value. I don't know the other ways to do it.

 

[DCrake]

Go to the properties of the form and select OnCurrent

Then code

Me.Textbox = DateDiff(...)

This will cause the calculation to e refreshed each time a new record appears on screen.

However you may need to test that a value exists in both date fields for the calculation to work.

 

[Downie77]

Well there will always be an Approved Date but the Check Date is input at a later time. So I need it to work once the Check Date is input. Does that make sense?

 

[DCrake]

Ok, so on the AfterUpdate event of the date control place you code in there.

 

[Downie77]

I'm so sorry, I am sure this isn't as hard as I am making it.... Putting that formula in the AfterUpdate isn't working.

 

[DCrake]

So what are you actually putting in their?

 

[Downie77]

I tried just the =DateDiff("d",[Approved Date],[Check Date]) and the Me.textbox=DateDiff(...)

 

[DCrake]

The textbox control on your form will have a name such as textbox1 or TxtDate

you need to refer to this

Me.TxtDate = DateDiff("d",[Approved Date],[Check Date])

 

[Downie77]

So I am sure this is an ID10t error but ugh.... What do you mean by textbox control? Sorry, I am really new and obviously don't have much of a clue.

 

[DCrake]

The textbox control is the box on your form where you want to display the time difference.

You will have control for your approved date and a control for your check date and a third one for the elapsed time

You use these textbox names - not the control source names to perform the calculation

Therefore

Me.TxtElapsed = DateDiff("d",Me.TxtApprovedDate,Me.TxtCheckDate)

 

[Downie77]

From what I can see my box names are Days Btwn, Approved Date and Check Date. I have tried plugging those into your formula and it doesn't work.

 

[jzwp22]

Are you using the standard form view or a datasheet/continuous form?