Is there a formula?

[Guest]

I have a select query that shows an employee's last day on payroll. For a
mail merge I am working on I want the query to show me the last day of the
month.

For example
[dbo_Pr_EmpPayData_T]![dteTerminationDate] = 10/17/2006

I want a field to show up like this

LastDayOGLI= 10/31/2006

Of course [dbo_Pr_EmpPayData_T]![dteTerminationDate] always varies in each
case. Help me with a formula?

 

[Brendan Reynolds]

SELECT tblTest.TestDate, DateSerial(Year([TestDate]),Month([TestDate])+1,0)
AS LastDayOfMonth
FROM tblTest;

Replace 'tblTest' with the name of your table, and 'TestDate' with the name
of your termination date field.

 

[John Vinson]

I have a select query that shows an employee's last day on payroll. For a
mail merge I am working on I want the query to show me the last day of the
month.

For example
[dbo_Pr_EmpPayData_T]![dteTerminationDate] = 10/17/2006

I want a field to show up like this

LastDayOGLI= 10/31/2006

Of course [dbo_Pr_EmpPayData_T]![dteTerminationDate] always varies in each
case. Help me with a formula?

DateSerial(Year([dteTerminationDate]), Month([dteTerminationDate]) +
1, 0)

It's the zeroth day of the next month - which DateSerial is smart
enough to interpret correctly as the last day of the selected month.

John W. Vinson[MVP]

 

[BruceM]

=DateSerial(Year([dteTerminationDate]),Month([dteTerminationDate)+1,0)

DateSerial looks at the year, month, and day from a date field or function,
adding and subtracting as needed, or else it uses constants. In this case
it is taking the year from your date field, adding 1 to the month, and using
the constant 0 as the day, which evaluates to the day before the first day
of the month. October 25, 2006 becomes November 0, 2006; DateSerial
evaluates that to October 31, 2006. The advantage is that it works with
months of any length.

You can wrap the Format function around the expression, as needed.

 

[Guest]

Thanks guys! Worked like a charm.