# of Days between 2 Dates

[jnp102]

Hi All,


Just had a quick question about how to do this. I read MichaelRed's FAQ181-261 (by the way thanks for the great FAQ!) on how to do this and I can get it to work perfectly. However, I want to add one last twist in it and I just can't seem to get it to work for me. The text field that has the number of days is part of a table and I want that number to be stored in that table. Is there a way to combine MichaelRed's method and have that value be stored in a table field?


Thanks in advance
jnp102

 

[scriverb]

When you say text field are you referring to a text control on a form that you are populating with the results of a function call to Michael's code? Is this an unbound control? If so, just create your field in the table that is populating the form and select that field in the controls ControlSource property. When the record is saved then the table will be updated with the calculated value.

Post back with more info if this is not what you mean.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jnp102]

Bob,

To answer questions (I hope) I have a table that has 2 dates and a days field. Now on the form I have the three text boxes that are bound to the fields in the table. However, when I do Michael's code he has you put =DeltaDays([start],[end]) in the control source. So currently, to get the calculation, I have the above in the control source of my days text box. This does part works perfectly, but since I replaced the original control source (which obviously refered back to the table field) it only calculates the text box not populates the table. I really hope this clarifies things a little.


Thanks

jnp102

 

[scriverb]

Okay, I understand. Change the controlsource back to the Days field from your table. Now put a Command button on the form and put the following code in the OnClick event procedure of the button:

Me![Days] = DeltaDays([start],[end])

This will get the calculated days for you into the field in your table once the record is saved. You could also add a line to the event procedure after the above to perform the save function:

DoCmd.RunCommand acCmdSaveRecord

If you don't want to use a command button you could always just put the code behind the textboxes in their AfterUpdate event procedures to automatically calculate and save. put this in the AfterUpdate of both Start and End textboxes:

If Not IsNull([start]) and Not IsNull([end]) then
     Me![Days] = DeltaDays([start],[end])
     DoCmd.RunCommand acCmdSaveRecord
End if

This code will only run if there is a legitimate date in both textboxes. The command button is not necessary here.

Post back with any other questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jnp102]

Bob,

That's perfect, I appreciate the quick reply to this post. I think that this will help tremendously.



Thanks again
jnp102