Urgent answer welcomed! (1 Viewer)

[Matizo]

Hello,

I have a question. Is that possible to find a number of days between two dates BUT if those two days are from different record? (if we assume that dates are in ascending order) (uk date format)

For example:

StartDate EndDate
Record 1 01/02/07 28/02/07
Record 2 18/03/07 25/03/07


And now find number of days between EndDate from Record 1 and StartDate from Record 2??

Any ideas??
Thanks!

 

[Moniker]

Create a Make Table query and make a table called t_DateDifferences. Run the query and only add the StartDate values into t_DateDifferences. Open t_DateDifferences and delete the very first record. Go to Design View and add a field called "EndDate", data type Date.

Create an Append query. Append all of the EndDate values from your original table into the table you just made, t_DateDifferences.

The StartDate from your original record 2 is now lined up with the EndDate from your original record 1, putting them both in the same record and making the math from this point forward self-explanatory.

 

[Matizo]

Thanks! I know what you mean.
Can I create a macro that will perform all these tasks for me automatically?
So that the user could do it with one click of the button?

Thanks again!

 

[Bat17]

How about using a query with a calculated field to add one to your record number, then use a second query to join the original table + query together using the adjusted record number. You can now have the dates in separate columns and work with them

Peter

 

[Moniker]

You could, I guess, but it'd be a lot easier in VBA. (I'm not sure you can delete an individual record with a macro without calling VBA, but I don't use macros much because they're too limited.)

I poked around a little bit and didn't see how to select a column/field and move it, nor did I see how to move an entire column, offset by one, in SQL alone. Programmatically, it's pretty straightforward, but it'll take more than RunSQL commands out of a macro.

 

[Matizo]

Bat17 could You please give some example to demonstrate this?


Moniker could you take a look:
http://www.access-programmers.co.uk/forums/showthread.php?t=123251

..well if that is pretty straightforward with VBA can you give code because I have no idea how to do it.

Thanks!

 

[Bat17]

first query would be something like
SELECT [ID]+1 AS newID, tblDates.startDate AS NewStart, tblDates.EndDate AS NewEnd FROM tblDates;

second query
SELECT tblDates.ID, qryDates.NewEnd, tblDates.startDate, [startdate]-[newend] AS days
FROM qryDates INNER JOIN tblDates ON qryDates.newID = tblDates.ID;


HTH

Peter

 

[Matizo]

Hello again,

The way with two queries work fine!

Thanks Peter!

regards,
matt