need start day and end day calculation

[Marcel2586]

Hello all,

I need to calculate a start date to a day number.
Lets say start date is 01-01-2012 corresponding to day one of the year.
And end date is 10-03-2012 corresponding to day 70 of the year.
I need this calculation in a query where i have a start date field and a end date field. we work with day numbers. so a part with day number 10 has more priority than a part with a day number of 14. how can i get this to work?
I used this from access help =Format(Now(), "y")
But it gives me a error
Also keeps this formula into account February 29? (leap year)
I would appreciate a sample so i can figure out how it works and use it for my own database.

Greetings,
Marcel.

 

[NickHa]

Something like this will do what you need

SELECT (dtDateEnd - DateSerial(Year(dtDateStart),1,0)) AS dtAgeDays FROM tDays;

This gives the age between start of year and end date.

Here, the start of year is determined by the DateSerial function, using the year part of the start date, month 1 and day 0. Day 0 makes the start date 31 December of the previous year, so the age gives the number of days inclusive of the current year (e.g. 1 January is day 1).

You could use a simple subtraction between start and end dates to give the age relative to start date.

SELECT (dtDateEnd - (dtDateStart-1)) AS dtAgeDays FROM tDays;

The -1 is again to make the calculation inclusive

 

[Marcel2586]

Thank you for your reply NichHa, but where and when do i use this. Is this VBA? sorry, for my ignorance, but i am a access beginner. (I made some infopath forms and now i am bombarded the IT wizzard!)

 

[NickHa]

This is SQL for a query. You can paste it in the query editor (when in SQL) view, or you can put it in the data source for a control. I used a dummy table (tDays) as the source for the two dates and created two Date/Time fields for them. If you use your field names and table name, the query will work. If any of your names have embedded spaces, then surround the name with square brackets[].
You said you wanted a query, but gave no context for its use (e.g. In a form, VBA etc.). If you provide more detail on how you want to use the calculation, I'm sure someone in the forum can help.
You asked if the calculation takes account of leap years - it does. If you try two dates for this year near end February and start of March, you can check the result is correct.

 

[Marcel2586]

NickHa, many, many thanks.

 

[NickHa]

You are very welcome.
Did you manage to run the query successfully?

 

[Marcel2586]

It works fantastic, made a test query and it works. Tomorrow at work i will implement it to my database. so once again thank you for your help.

 

[Marcel2586]

There is something wrong with the calculation.
see zip Inter-mu Test day numbers
query qry Day numbers
field TBL Inter Mu
I have even a day with 719. that cant be good.

 

[NickHa]

It looks like its working properly to me!

The 719 days is the correct result for that row. What the calculation is saying is subtract 31 December 2009 from 20 December 2011. That is 365 days in 2010 and 354 days in 2011.

What I suspect you want is the difference between the order and delivery dates, which is a different calculation (the second example I gave you originally). I notice that one of your delivery dates occurs before the order date - is this legal? If it is, then the reult will be negative.

So the query I think you need is

SELECT [Serv order], Partnumbers, [maximum amounts], [Box color], processes, Diameters,
[formal delivery date], [Customer department], [Customer wkctr], [Part name], Remarks, SSD, [TAT SLA], Days,
[order creation date],
([formal delivery date] - [order creation date]) AS elapsedDays
FROM [TBL Inter Mu];

Note that I have simplified the query fields by omitting the '[TBL Inter Mu].' prefix (which is not necessary in this particular case) and changing the date calculation to

([formal delivery date] - [order creation date]) AS elapsedDays

This does not count both dates - you need to add 1 to make them inclusive, thus:

([formal delivery date] - [order creation date] + 1) AS elapsedDays

You get a negative value for row 2, as this is where the dates are in the wrong order.

 

[Marcel2586]

I have altered my SQL in my original database and it works.
I picked yust random dates for my test.