How Many Days Since? 1

[Fozzy9767]

I have to calculate how many days from the last day of the previous month a certain date was. I have a table that contains the last activity date, I need to calculate howmany days between that date and the last day of the previous month. I'm drawing a blank here.

 

[SkipVought]

hi,

[last activity date] - dateserial(year([last activity date]),month([last activity date]),0)

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[Fozzy9767]

I'm sorry, that code looks like you are subtracting the last activity date from itself? And how does it give me the number of days? I may be a bit thick today...

 

[SkipVought]

Did you happen to TEST it?

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[SkipVought]

OF course, even a simpler approch...

CInt(Format([last activity date], "d"))

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[Fozzy9767]

Sorry, trying to do things a piece at a time. I wanted to understand what it was trying to do so I could maybe learn something rather than just having someone do stuff for me. I plugged your second code into a query and it works great, thanks, now to deal with those null entries.

 

[SkipVought]

Well, think about how the DateSerial function works. If the DAY argument were ONE, it would return the FIRST of the month. A ZERO, yields the LAST of the PREVIOUS month, one day earlier.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[PHV]

I plugged your second code into a query and it works great
A simpler way:
Day([last activity date])

now to deal with those null entries
Nz(Day([last activity date]),0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Fozzy9767]

OK, back at work and double checking, have a small problem.
this code:

CInt(Format([last activity date], "d"))

That I thought was working is not. With a last activity date of 01/26/2008 I should get a result of 5, but I am getting 26. It seems to be counting from the beginning of the month and I need backwards from the end of the last month. Perhaps I should be using the first of this month so it counts all the days, not sure.

 

[PHV]

So, you wanted this ?
DateSerial(Year(Now()),Month(Now()),0) - [last activity date]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Fozzy9767]

That did the trick, thanks. I was almost there on my own too, which is encouraging.