find first day of the month

S

SAm

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
 
R

Rick Brandt

SAm said:
i have the following field in a query:
Click to expand...
IIf(Month([PeriodStartDate]) said:
& 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
Click to expand...

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

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

SAm

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

Rick Brandt said:
SAm said:
i have the following field in a query:
Click to expand...
IIf(Month([PeriodStartDate]) said:
& 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
Click to expand...

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

LastDayOfAMonth: DateSerial(Year([DateField]), Month([DateField]) + 1, 0)
Click to expand...
 
J

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.
Click to expand...

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]
 
Top