refreshing the form

[Alix]

Hi
I have a form which has to type of date one todays date another the date which something happened i could do something that find the difference between these to days BUT everyday i have to go across all fields to update the days difference is there any way to make the form refresh itself whenever you open the form?

 

[otis1969]

Dude, I don't think I really understand the question. Could be that you need to use datediff or dateadd functions.

 

[Alix]

actually i dont know anything about those tuff you named
i ment
for example if todays date is "30/3/03" and the day that a paper recived is "10/2/03" so the difference is 20 days but tomorrow which is 1/4/03 if i dont check all fields the difference is still 20 days i want all my fields refresh or update themselves everytime i open that form

 

[otis1969]

ok, just to let you know, I'm not by any means an expert here! I myself have just learned access.

I think you will need to create a sub in the form that will insert the date(today's), then you need to use the datediff function. I am not sure at all how the code will work, but you can look up datediff on the msdn library from microsoft.

Sorry I cant be of more help, but I didnt like the fact that no one was really helping you out!!

 

[Idjit]

Alix,

If what you want to do is display the difference between 2 dates, put an unbound textbox on your form and use the DateDiff statement as its control source. For example, if you want to display the number of days that have elapsed since an event, use the statement:

=DateDiff("d",[YourDateField],Date())

using your field's name. Date() specifies today's date, and "d" specifies that you want the difference in days as opposed to weeks, etc. If you want to subtract 2 date fields, substitute the 2nd field's name for Date(). Every time you open the form or change one of the date fields, the difference control will automatically update itself.

It sounds like the reason your days elapsed fields don't automatically update is because they are separate fields stored in your table along with the dates. There is no need to do this, and as you've seen it's not a good idea anyway. Don't put an extra field in your table that contains information you can easily compute from other fields. Do the computations on the fly when you need them via a form or query.

 

[Alix]

Thanks you Both
I'll test it and let you know what was the result!
Have fun

 

[Alix]

I made a "Unbound text box" and used
"=DateDiff("d",[Date of Request],Date())" as the text box control source and when i checked the my form there were " #Name? "

Date of Request => is my field name which specifies the date of request that i want subtract it from my todays date.

before i used=> Date()-[Date of request]

 

[Idjit]

Alix,

The form isn't recognizing [Date of Request] as a valid control on the form. Are you sure that this is exactly how it's spelled in the Name property of the text box? I noticed that in one place in your post you capitalized "request" and in the other you didn't.

Double check this and post again if it's still not working.

 

[Alix]

you mean the capital and small really makes difference?
I'll double check it and let you know

 

[Idjit]

Yes, Access is case-sensitive.

 

[Alix]

Thanks Alot
Works perfectly
and you think now the form update the date itself?!

 

[Idjit]

Remember that the only piece of data you're storing is the date of the request. Every time you open the form, Access will automatically subtract that date from the current date and display the result in a text box that isn't linked to the table. Also, if you change the date of the request the unbound field will immediately display the new difference.

Experiment by playing around with different scenarios that might come up as the db is used to make sure that you're getting the results you want.

Post again if you have any more questions.