Adding <Date() to Criteria

[oxicottin]

Hello, I have a query that I need to add one more criteria to and I cant get it to work. I have it look at [Forms]![frm_YearCalendar]![cboYear] for the year but I also need to only look up to todays date. I tried adding <Date() but it didint work or I must be inserting it incorectly. Thanks!

SELECT tbl_YearCalendar.EmployeeID, Sum(Nz([AbsenceTime],0)) AS SumOfVacationUsed
FROM tbluAbsenceCodes INNER JOIN tbl_YearCalendar ON tbluAbsenceCodes.AbsenceID = tbl_YearCalendar.AbsenceID
WHERE (((DatePart("yyyy",[AbsenceDate]))=([Forms]![frm_YearCalendar]![cboYear])) AND ((tbluAbsenceCodes.AbsenceCode)="V" Or (tbluAbsenceCodes.AbsenceCode)="VFML"))
GROUP BY tbl_YearCalendar.EmployeeID;

 

[smig]

Is cboYear a Date or Year ?
If its year try < Year() in your criteria

 

[Galaxiom]

You need to explain what you want more clearly.

BTW This is a very inefficient criterion.

WHERE (((DatePart("yyyy",[AbsenceDate]))=([Forms]![frm_YearCalendar]![cboYear]))

This is much more efficient:

WHERE [AbsenceDate] Between DateSerial([Forms]![frm_YearCalendar]![cboYear],1,1) And DateSerial([frm_YearCalendar]![cboYear],13,0)

(Assuming the cbo is just the four digit year)

 

[Brianwarnock]

I was interested that used

DateSerial([frm_YearCale'ndar]![cboYear],13,0)

Rather than

DateSerial([frm_YearCale'ndar]![cboYear],12,31)

Since the number of days in December does not vary.

Brian

 

[Galaxiom]

I was interested that used

DateSerial([frm_YearCale'ndar]![cboYear],13,0)

Rather than

DateSerial([frm_YearCale'ndar]![cboYear],12,31)

First thing that came into my head.

 

[Brianwarnock]

Fair enough, just wanted to make sure that there wasn't a subtle difference I didn't know about.

Brian

 

[smig]

I was interested that used

DateSerial([frm_YearCale'ndar]![cboYear],13,0)

Rather than

DateSerial([frm_YearCale'ndar]![cboYear],12,31)

Since the number of days in December does not vary.

Brian

Using DateSerial([frm_YearCale'ndar]![cboYear],Month+1,0) is good for any month. No need to remember number of days in month

 

[Brianwarnock]

Using DateSerial([frm_YearCale'ndar]![cboYear],Month+1,0) is good for any month. No need to remember number of days in month

Yep I know but this was very specific , as I stated, covering a full year so it seemed unnecessary and may confuse those who do not know. I have no quarrel with it but wondered if there was a subtle difference under the covers.

Brian

 

[oxicottin]

Ok, I been trying to figure out what it is exactly I need and I think I have to first find the year im searching for ([Forms]![frm_YearCalendar]![cboYear]) but only search for "V" Or "VFML" within the field AbsenceCode and its [AbsenceDate] which is in mm/dd/yyyy format thats why I used the DatePart BUT only search for it up to the current date. so for example this is for my attendance database and I want to add a section that shows "Used Days" so it would only show days that have past so if I scheduled days off for next week it wouldnt show in the "Used Days" column.

Thanks!

 

[Brianwarnock]

Ok so in the criteria supplied by Galaxiom replace the bit after the AND by <Date() or <= Date() depending on whether you want to include today or not.

Brian