Datediff from previous record to current record (1 Viewer)

[tamangspace]

Hi,
I want to get the datediff of the last returned date from vacation and the current vacation start date. But I have no idea how to get difference from the previous record's date to this current record's date. I want to show date difference in an unbound textbox. I have a contiuous form which shows all vacation list of employees. It shows how many times he went vacations and when he went and when he returned.

 

[plog]

Can you provide sample data, along with table and field names, along with what you want your query to return based on that sample data? Use this format:

TableNameHere
Field1Name, Field2Name, Field3Name
1/1/2009, David, 14
3/8/2007, Steve, 11
10/9/2008, Larry, 9

 

[tamangspace]

Dear Plog,
In this query I could only check the duration days of vacations. But I couldn't check the duration that he is in work. For Example: SomeOne1 returned from vacation on 16/7/2003 and went next vacation on 25/5/2004. Now I want to get the date difference with these two dates which are in different rows in my table and query. It is the problem.

Query1:
EmpName, departuredate, arrivaldate, workdays
SomeOne1,5/4/2003, 16/7/2003
SomeOne1, 25/5/2004, 15/8/2004
SomeOne1, 18/6/2007, 5/9/2007

 

[tamangspace]

This is the sample data.
So do I need vba code? if so please help me.

 

[plog]

I need a starting point and an ending point. You've provided a starting point, now I need to know what the data should look like based on that starting point.

Provide me with what the results should be based on the sample data you have provided me.

 

[tamangspace]

I am also confusing what is ending point or I couldn't understand you what is ending point you want. I want to know how many days SomeOne1 attended at work between arrivaldate of his last vacation and current vacation departuredate. Let's assume, SomeOne1 arrived on 16/7/2003 and joined his work and going next vacation on 25/5/2004. Now I want to know how many days he stayed at work. that's it.
Sorry, because I don't know how to express my problem or my English is not correct.

 

[plog]

No english required, I want data. Suppose you said this: "I want a query to return everyone in my contacts list with a first name that ends in 'y'". I would ask you for starting sample data (A) and what you want returned based on that sample data (B).

You would provide me with this for A:

tblContacts
ContactID, FirstName, LastName
3, David, Smith
7, Sally, Jones
8, Larry, Allen
9, Steve, May

You would also provide me with this as B:


Results
ContactID, FirstName, LastName
7, Sally, Jones
8, Larry, Allen

You've given me A and an explanation of what B shoudl be. I want actual data for B.

 

[tamangspace]

This is the problem. I couldn't create it.
The Sample Data (B) supposed to be:
EmpID, ArrivalDate, DepartureDate, Workdays
1, 16/7/2003, 25/5/2004, 314 days
1, 15/8/2004, 18/6/2007, 1037 days

But I don't want to store the workdays in table or query, I only want to show in the form in a textbox. So that If someone applies for vacation, I could answer him that he doesn't have enough workdays to apply for next vacation or he is legal to apply next vacation. If possible please write for me that the result will be in Years, Months and days.

 

[tamangspace]

Or need I create another table with the rows containing like this:
empID, LastArrivalDate, thisDepartureDate
and,
Query like this:
workdays: datediff("d",[lastarrivaldate],[thisdeparturedate])

I think it is not reasonable, is it?

 

[plog]

I almost give up. There is no DepartureDate of 18/6/2007 in the database you provided.

Last chance: in your next post provide me starting sample data (A) and and what the result should be based on that sample data (B). No more explanations or paragraphs, just data.

 

[tamangspace]

Sorry, But this was just assumption. Here is my data

 

[RainLover]

tamangspace

If I understand your question then all you want is the number of days between two Dates.

One of these dates is when they last returned from vacation which is found in your table, and the other date could be anytime in the future. This second date could be entered into a form's text box and the result could be shown in another text box. The latter two dates are not stored.

Is this what you need.?

 

[tamangspace]

Hi RainLover,
Thanks for your good response. I think you understand 50% of my question. But if the later 2 dates will not be stored then how do you calculate the next vacation record. I want to record Last Arrival date and also the next departure date.
I know putting dates in textbox and calculate in another textbox. But I don't know how to calculate date difference between field Arrivaldate in a row and the field Departuredate in the next row. the two dates are already stored. Its true there is no need to store the difference. It is to show only in the continuous form after every record.
Please analyse in my sample data, could you?

 

[RainLover]

Sorry, But this was just assumption. Here is my data

I along with a lot of the older people here do not have anything pass Access 2003. So if you need an opinion you will have to convert your data to 2003.

Personally I think it is a good idea as more people can help.

As far as your problem is concerned I have attached a Database that will give you the running differences with reference to numbers.

If the principal is correct then you need to change the sample to Dates. Do some testing then apply to your own situation.

There is one problem that requires your attention. The first is that you will have to filter out the employee to the one you want.

You can include all employees but you will need an identifier which maybe what PLOG was referring to,

 

[tamangspace]

There is one problem that requires your attention. The first is that you  will have to filter out the employee to the one you want. 
 
You can include all employees but you will need an identifier which maybe what PLOG was referring to,

Thanks a lot rainlover and sorry replying late, because in Saudi Arabia, the Eid was in last week and I was in a 10 day holiday.

I got it and having checked in my database. I found it so helpful. But there is a problem that I am a new to Ms Access and don't know about identifier much more, how to use it. I know only that identifier is something like PK or FK. So if there is any articles about 'identifier' please give me the link.
Thanks again.

 

[RainLover]

I don't remember everything in this thread as some time has passed while you were on holidays.

You need to re explain what your problem is now.

Post a sample if you have one.