last date

[silverxx12]

Hi

In my query i have various dates in a given month eg mar is it possible to create a function / expression which will give me the last date of the month ?

so my query would look like ?

Preferably without the use of vba as i am not too familiar with it?



26/3/12 - 31/3/12
24/3/12 - 31/3/12


many thanks

[recyan]

Just check if below gives some guidelines :

SELECT 
	tblDates.testDate, 
	DateSerial(Year([testDate]),Month([testDate])+1,1) AS NextMonthDate, 
	DateAdd("d",-1,[NextMonthDate]) AS LastDateOfMonth
FROM 
	tblDates;

Thanks

[silverxx12]

hi for some reason it does not like the syntax keep on getting an error:

could you possibly explain what the formula is doing maybe i am inputting something incorrectly ?

[recyan]

what error ?
Can you show us the sql.

The input date

SELECT 
 tblDates.testDate,

This should get the 1st Date of the next month

DateSerial(Year([testDate]),Month([testDate])+1,1) AS NextMonthDate,

This should subtract 1 day from the Next month 1st Date to get the last day of the month

DateAdd("d",-1,[NextMonthDate]) AS LastDateOfMonth

FROM 
    tblDates;

Thanks

[silverxx12]

ok finally got it to work :

i guess i had a couple of issues when copy and replacing code mainly and some wierd characters appearing from "word".

basically the formula gives two outputs, correct? The next month and the day before the next month clever...:-)

The next issue i am having is now i have three columns with dates - the current date of the data the next months date and last date of the month

is there a way to only show the last date of the month without the previous two columns showing ? i tried unchecking the expression in design view but the results cased the last date of the month to disappear?

many thanks for the help so far

[recyan]

Just check out if below gives some guidelines :

SELECT 
    tblDates.testDate, 
    DateSerial(Year([testDate]),Month([testDate])+1,1) AS NextMonthDate, 
    DateAdd("d",-1,[NextMonthDate]) AS LastDateOfMonth
FROM tblDates;

SELECT 
    tblDates.testDate, 
    DateAdd("d",-1,DateSerial(Year([testDate]),Month([testDate])+1,1)) AS LastDateOfMonth
FROM tblDates;

SELECT 
    DateAdd("d",-1,DateSerial(Year([testDate]),Month([testDate])+1,1)) AS LastDateOfMonth
FROM tblDates;

Thanks

[silverxx12]

ty works perfectly!!

[recyan]

H2H - Happy To Help

Thanks