Afternoon all,

I'm abit stuck and I can't seem to find anything on this, or I am just not asking the right question.


Anyways, I am trying to get the number of Days to show up on a form when you put the date in on another text box.

Bascily what i am searching for is this.

Calculating number of days from a start date

I have found Calculating number of days from a start date and end date, but that does not help me.

plus i am fairly new at access 2007.

Thank you in advanced.

Hi

Check out: MS Access: DateAdd Function

For example:

DateAdd ("yyyy", 3, #22/11/2003#) would return '22/11/2006'
DateAdd ("q", 2, #22/11/2003#) would return '22/05/2004'
DateAdd ("m", 5, #22/11/2003#) would return '22/04/2004'
DateAdd ("n", 51, #22/11/2003 10:31:58 AM#) would return '22/11/2003 11:22:58 AM'
DateAdd("yyyy", -1, #22/11/2003#) would return '22/11/2002'

HTH's

Not quite what I am after, but thank you for the help.

I have a Form that is pulling data from a table, in this Form i have a SHIP DATE area, in the next area is DAYS FROM SHIP DATE.

I'm trying to find out how i can get the amount of days from when it got shipped. I'm not sure how to put this in as i am new to access.


thank you for the help.

Hi
If I understand you want to count the days between Shipped Date and the current date..!!!

If so check out: MS Access: DateDiff Function.

For example:

DateDiff ("yyyy", #15/10/1998#, #22/11/2003#) would return 5
DateDiff ("m", #15/10/2003#, #22/11/2003#) would return 1
DateDiff ("d", #15/10/2003#, #22/11/2003#) would return 38

X = DateDiff("d",[Shipped Date],Date())

HTH's

Hi,larry

If i have startdate and enddate is null, then when i calculate the difference it should consider march as the enddate.How i can do this?


Thanks

waseem

Hi waseem,


What would you consider when the startdate is null? Check out the Nz() function.

Hope that helps...

Hi
Or some thing like this:

Hi, larry

It gives me end date as current date.But i need the end date should be march.
Example: suppose start date is February1st, there is no end date date automatically system should take march of current year as end date.
If start date april 1st, then if end date is null, then it should march of next year as the end date.

Note:There is always start date. only end date is null

How i can do this?

Thanks

Ahhh thank you Oh so much....

I can't tell you how much this has been driving me up a wall. It seems the code I was using was somewhat correct, but was missing Date(). Either way, THANK YOU for the quick response, and the line of code that makes it all better lol

Thank you =]

Hi, larry

It gives me end date as current date.But i need the end date should be march.
Example: suppose start date is February1st, there is no end date date automatically system should take march of current year as end date.
If start date april 1st, then if end date is null, then it should march of next year as the end date.

Note:There is always start date. only end date is null

How i can do this?

Thanks

This should do:

=DateDiff("d", [Start Date], Nz([End Date], DateAdd("yyyy", Abs(Month(Date()) > 3),DateSerial(Year(Date()), 3, 1)))

as the controlsource for your textbox displaying the count of days.

/gustav

Hi
OK.. all you need to is replace the DAte() with your end date of march..

eg:

Before:


After:


HTH's

hI,

Thanks for your support.
But the code of Gustav gives the correct result. But it gives 1 month less. If the Joining date is april, it should calculate upt o march ,ie 12 months. but it shows 11 months.

The code provided by larry gives negative months. Why?


Dear Gustav, can you explain me in detail the code
=DateDiff("d", [Start Date], Nz([End Date], DateAdd("yyyy", Abs(Month(Date()) > 3),DateSerial(Year(Date()), 3, 1)))

What is this nz? abs? etc. it will be great help to every one to understand..


Thanks

Waseem

Hi
Here are my test results from the SQL:


SQL:
SELECT Table4.ItemDate, Table4.EndDate, DateDiff("d",[ItemDate],IIf(IsNull([Enddate]),DateSerial(Year(Date()),3,1),[EndDate])) AS [Count Days], DateSerial(Year(Date()),3,1) AS [Check March Date], DateAdd("d",[Count Days],[Itemdate]) AS [Cal End Date]
FROM Table4;

Where does the negative months appear..??


Bear in mind my result format is UK date format dd/mm/yyyy

Thank You
Larry, I got it ...

Waseem

> Larry, I got it ...

No I don't think so.

> If the Joining date is april, it should calculate upt o march ,ie 12 months. but it shows 11 months.

Correct. That's because it will be 11 months, not 12.

This calculates the count of days:
=DateDiff("d", [Start Date], [End Date])

This uses Mar. 1th of the current year if end date is Null:
=DateDiff("d", [Start Date], Nz([End Date], DateSerial(Year(Date()), 3, 1)))

This return true if the start date falls in a month later than March. Abs turns the numeric value of True, -1, to 1:
Abs(Month(Date()) > 3)

This adds one year if the start date falls in a month later than March:
=DateDiff("d", [Start Date], Nz([End Date], DateAdd("yyyy", Abs(Month(Date()) > 3),DateSerial(Year(Date()), 3, 1)))

By second thought, a year should be added also if the start date falls in March, thus:
=DateDiff("d", [Start Date], Nz([End Date], DateAdd("yyyy", Abs(Month(Date()) >= 3),DateSerial(Year(Date()), 3, 1)))

This is what Larry forget; if the start date falls later than March and both start date and end date fall in the same year, a negative count of days is the result if you don't add a year.

/gustav

Hi,

Gustav...

Excellent!!!!

You are welcome!

/gustav

Hi,
If if the end date is null, it should show march month as the end month.Suppose the person joined in july and End date is null, it should consider and display next year march 1st as the end date.if the guy joined in february, then next month march as the end date. It should dipslay like 01-03-2011. How i can do this??

Thanks

waseem

That could be:

=DateDiff("d", [Start Date], Nz([End Date], DateAdd("yyyy", Abs(Month([Start Date]) >= 3),DateSerial(Year([Start Date]), 3, 1))))

or for just the end date:

=Nz([End Date], DateAdd("yyyy", Abs(Month([Start Date]) >= 3),DateSerial(Year([Start Date]), 3, 1)))

/gustav