Last date of field's date in query

[mba_110]

Hi everyone,

I want to get result in query for cut off (Year end) date for my employees hire date the year they join i want that year's last date.

I have date field called [HireDate] i want the result in another column which is 1stYearEndDate: to show the 31/12/## of [HireDate] field's date.

for example : if [HireDate] is 05/02/2012 then it should give me result 31/12/2012

Please provide the function and criteria.

I try following but not able to achieve the result.

Date([HireDate]) = Year(Date())

 

[Gasman]

CutoffDate = dateserial(Year(HireDate),12,31) ?

 

[mba_110]

Thnks for your help its working now.

Actually i want to cut off the periods in Years on the report.

for example:

I have one employee Mr.ABC and his join date [HireDate] is 05/02/2012 i want to calculate his Service award by each year end starting from his joining year.

Can i able to do it from 1. query or 2. need to make separate table for it and than use that table in query or 3. just in report query it can be done?

because in report i have two headings in details section of report first is Below<5 Year and second is Above >5 Years so if employee not active until five years the report should show only fields yearly until the date under year he work.

if he work more than five years than first five year fields should appear in 5 lines with each year cut off date under "Below<5 Year" heading and rest appear in "Above >5 Years" heading with each year cut off date and same should be for all other dates and duration of employees work.

I know its sounds little complicated but still can achievable i just need correct direction and start.

Fields that i have on forms detail area for each year one line
[Year] [From] [To] [Total Days] [OpDays] [CurYearDays] [TotalDays]


Above is same for both headings, based on their joining and exit dates

Any help with this is really appreciated.

 

[Gasman]

I would have thought you could do this in a query.
Calculate the years differences in one field
In the report sort by employee and then year difference.
In the detail line have two if statements one for <=5 and one for >5

 

[plog]

Can you provide data to demonstrate what you want to achieve? Provide 2 sets:

A. Starting data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show me what data you expect to end up with when you feed your query the data from A.

Again, data, not explanations. Show me exactly what data should be returned.