find first day of the month

[Guest]

i have the following field in a query:

IIf(Month([PeriodStartDate])<>Month([PeriodEndDate]),DateDiff("y",[PeriodStartDate],"1/" & Month([PeriodEndDate]) & "/" & Year([PeriodEndDate])))

basically, i need to find the last day of the month or the first day of the
month. how do i do that.

what i am doing here is trying to figure out how many days are from the
periodstartdate until the end of the month.

the above if statment produces the right number only for periodstartdates in
2004 and periodenddates in 2005 ??????????!!!!!!!!!

thanks, sam

 

[Rick Brandt]

i have the following field in a query:

& Month([PeriodEndDate]) & "/" & Year([PeriodEndDate])))

basically, i need to find the last day of the month or the first day
of the
month. how do i do that.

what i am doing here is trying to figure out how many days are from
the
periodstartdate until the end of the month.

the above if statment produces the right number only for
periodstartdates in 2004 and periodenddates in 2005
??????????!!!!!!!!!

thanks, sam

FirstDayOfAMonth: DateSerial(Year([DateField]), Month([DateField]), 1)

LastDayOfAMonth: DateSerial(Year([DateField]), Month([DateField]) + 1, 0)

 

[Guest]

thanks so much for your quick reply.

i would also like to add that i rewrote the
DateDiff("y",[PeriodStartDate],"1/"
& Month([PeriodEndDate]) & "/" & Year([PeriodEndDate])))
statement to date2: month/day/year and that worked fine.

again, thanks for your reply.

sam

i have the following field in a query:

& Month([PeriodEndDate]) & "/" & Year([PeriodEndDate])))

basically, i need to find the last day of the month or the first day
of the
month. how do i do that.

what i am doing here is trying to figure out how many days are from
the
periodstartdate until the end of the month.

the above if statment produces the right number only for
periodstartdates in 2004 and periodenddates in 2005
??????????!!!!!!!!!

thanks, sam

FirstDayOfAMonth: DateSerial(Year([DateField]), Month([DateField]), 1)

LastDayOfAMonth: DateSerial(Year([DateField]), Month([DateField]) + 1, 0)

 

[John Vinson]

i have the following field in a query:

IIf(Month([PeriodStartDate])<>Month([PeriodEndDate]),DateDiff("y",[PeriodStartDate],"1/" & Month([PeriodEndDate]) & "/" & Year([PeriodEndDate])))

basically, i need to find the last day of the month or the first day of the
month. how do i do that.

what i am doing here is trying to figure out how many days are from the
periodstartdate until the end of the month.

Try using the DateSerial function:

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

The zeroth of *next* month is the last day of this month.


John W. Vinson[MVP]