Number of days between 2 dates

[Orchid]

Hi,
I try to get the last day of the month based on a hire date and then
count the no. of days. For example: Hire date is 3/12/2008, I want to
format the hire date to be 3/31/2008, and then count the Number of
days. I want to make it 30days a month even it could be 31 or 29
days. So the answer should be 18 days. I have the formula below;
however, it gave me error:

DateDiff("d",[Hire Date], Format([Hire Date],"mm/""30""/yyyy"))+1

Could someone help? Greatly appreciated!

 

[Douglas J. Steele]

Format returns a string, whereas DateDiff is expecting a date value.

Try

DateDiff("d",[Hire Date], DateSerial(Year([Hire Date]), Month([Hire Date]),
30))+1

 

[Jeff Boyce]

Take a look at the DateSerial() function in Access HELP. You can use
something like:

DateSerial(Year([HireDate]), Month([HireDate])+1,0)

to get the day before the first day of the month after the month of the hire
date (i.e., the last day of the month in which the hire took place.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Beetle]

If you ALWAYS want to base it on 30 days, regardless of how many
days are actually in the month,then you can just do;

30 - Day([HireDate])

The problem there is that if the HireDate is 3/31/2008, it's going to
return -1.

If "30" was just your compromise because you didn't know how to find
the last day of the month, then use;

DateDiff("d",[HireDate],DateSerial(Year([HireDate]),Month([HireDate])+1,0))

 

[John W. Vinson]

Hi,
I try to get the last day of the month based on a hire date and then
count the no. of days. For example: Hire date is 3/12/2008, I want to
format the hire date to be 3/31/2008, and then count the Number of
days. I want to make it 30days a month even it could be 31 or 29
days. So the answer should be 18 days. I have the formula below;
however, it gave me error:

DateDiff("d",[Hire Date], Format([Hire Date],"mm/""30""/yyyy"))+1

Could someone help? Greatly appreciated!

Sorry, this is making no sense. A Format changes what's DISPLAYED, not what's
stored; and I'm not seeing what you mean by displaying the last date of the
month but calculating a date that is different from the last day of the month!

At a WILD GUESS, assuming you both want to see the last day of the month
(#2/28/2009# or #12/31/2009#), and assuming that you want to display -1 if the
employee was hired on the 31st, try

DateSerial(Year([Hire Date]), Month([Hire Date]) + 1, 0)

to display the last day of the month, and

30 - Day([Hire Date])

to display the number of days until the fictitious "end of the month".

 

[John Spencer]

Perhaps what you want is
IIF(Day([HireDate])<31,0,30-Day([HireDate]))

So anyone hired on the 31st or the 30th of the month will return 0 days.
Anyone hired on the last day of February (29 or 28) will return 1 or 2.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Orchid]

Perhaps what you want is
   IIF(Day([HireDate])<31,0,30-Day([HireDate]))

So anyone hired on the 31st or the 30th of the month will return 0 days.
Anyone hired on the last day of February (29 or 28) will return 1 or 2.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi,
I try to get the last day of the month based on a hire date and then
count the no. of days.  For example: Hire date is 3/12/2008, I want to
format the hire date to be 3/31/2008, and then count the Number of
days. I want to make it 30days a month even it could be 31 or 29
days.  So the answer should be 18 days. I have the formula below;
however, it gave me error:

DateDiff("d",[Hire Date], Format([Hire Date],"mm/""30""/yyyy"))+1

Could someone help?  Greatly appreciated!- Hide quoted text -

- Show quoted text -

Thanks all for your response. The if(Day()) function works perfect,
thanks John!!

For future reference, I am interesting to find out the problem-- I
keep on getting error message on both functions DateDiff() and
DateSerial() on this query. But they worked fine when I tested it on
another query. Is it something wrong with my query? And by the way,
it takes approximately one minute to just move a text box on my report
design which based on this query (that's a long time). My other MS
access database runs normal, so it's not my computer but the
database.

It really ignores me that's so slow. Is there a way to fix it?
Thanks in advance!

 

[Douglas J. Steele]

Check your References collection to see whether any of the selected
references (the checked ones at the top) have MISSING: in front of them.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Perhaps what you want is
IIF(Day([HireDate])<31,0,30-Day([HireDate]))

So anyone hired on the 31st or the 30th of the month will return 0 days.
Anyone hired on the last day of February (29 or 28) will return 1 or 2.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi,
I try to get the last day of the month based on a hire date and then
count the no. of days. For example: Hire date is 3/12/2008, I want to
format the hire date to be 3/31/2008, and then count the Number of
days. I want to make it 30days a month even it could be 31 or 29
days. So the answer should be 18 days. I have the formula below;
however, it gave me error:

DateDiff("d",[Hire Date], Format([Hire Date],"mm/""30""/yyyy"))+1

Could someone help? Greatly appreciated!- Hide quoted text -

- Show quoted text -

Thanks all for your response. The if(Day()) function works perfect,
thanks John!!

For future reference, I am interesting to find out the problem-- I
keep on getting error message on both functions DateDiff() and
DateSerial() on this query. But they worked fine when I tested it on
another query. Is it something wrong with my query? And by the way,
it takes approximately one minute to just move a text box on my report
design which based on this query (that's a long time). My other MS
access database runs normal, so it's not my computer but the
database.

It really ignores me that's so slow. Is there a way to fix it?
Thanks in advance!