last day of month

J

jer

can a query return the last day of a month on a date field
for exampy if in date field I have
Oct 29 2006
Nov 15 2006
Dec 10 2006
can a query return
Oct 31 2006
Nov 30 2006
Dec 31 2006
I know this can be done in excel but not sure in access
 
R

Rick Brandt

jer said:
can a query return the last day of a month on a date field
for exampy if in date field I have
Oct 29 2006
Nov 15 2006
Dec 10 2006
can a query return
Oct 31 2006
Nov 30 2006
Dec 31 2006
I know this can be done in excel but not sure in access
Click to expand...

LastDay: DateSerial(Year(DateField), Month(DateField)+1,0)

With the DateSerial() function the "zeroth" of a given month equals the last day
of the prior month.
 
J

jer

Thank you
--
thanks as always for the help


Ofer Cohen said:
Try

DateSerial ( Year([DateFieldName]),Month([DateFieldName])+1,1)-1

--
Good Luck
BS"D


jer said:
can a query return the last day of a month on a date field
for exampy if in date field I have
Oct 29 2006
Nov 15 2006
Dec 10 2006
can a query return
Oct 31 2006
Nov 30 2006
Dec 31 2006
I know this can be done in excel but not sure in access
Click to expand...
Click to expand...
 
J

jer

thank you
--
thanks as always for the help


Rick Brandt said:
LastDay: DateSerial(Year(DateField), Month(DateField)+1,0)

With the DateSerial() function the "zeroth" of a given month equals the last day
of the prior month.
Click to expand...
 
Top