Im trying to create a formula for a date range within the current year.

[smc678]

Hello, I have an attendance database that tracks all associates attendance. My issue is I need to view only records from now() - [hire date]. This is the problem how do I right the correct syntax if Now = 6/18/2013 and I have an associate with a hire date of 6/1/2013 it will query only those 18 days, same thing if I have an associate with a hire date of 6/18/2010 I want to see only the 18 days not the 3yrs and 18days? any Ideas? There attendance resets after there hire date each yr.

[orange]

So it isn't hire date, seems you're talking about HireDateMonth and Hiredateday, but Year today.
see dateserial
syntax is
Dateserial(year,Month,Day)
so you want something like DateToCheck = DateSerial(Year(Date),Month(HireDate),Day(HireDate ))

and if HireDate is 6/18/2010
DateToCheck would be
Year(6/18/2013) = 2013
Month(6/18/2010) = 6
Day(6/18/2010) = 18

so DatetoCheck =DateSerial(2013,6,18)

so your comparison would be Between DateToCheck and Date
I hope I have understood correctly, and hope this helps.

[smc678]

How would I or can I write that in the criteria in the Query It would be in the Date collum?

[smc678]

So I put it in a new collum and it sorta works. but for example- Hire date is 7/11/2011 the new date is 7/11/2013. since we have not passed 7/11/2013 it needs to display 7/11/2012

[orange]

Post the SQL view of a query you are working with. Or post a compacted copy of your database in zip format.

[smc678]

SELECT tblAttendance.[Transaction ID], tblAttendance.[Assoc Nm], tblAttendance.[Assoc ID], tblAttendance.[Dpt Nbr], tblAttendance.[Dpt Nm], tblAttendance.[Job Ttl Cd], tblAttendance.[Emp Ctg], tblAttendance.[A/I], tblAttendance.[Hire Date], tblAttendance.MonthsatHD, tblAttendance.Shift, tblAttendance.Reason, DateSerial(Year([Date]),Month([Hire Date]),Day([Hire Date])) AS [Reset Date], tblAttendance.Category, tblAttendance.Date, tblAttendance.[Hours Missed], tblAttendance.Leader, tblAttendance.DocumentGiven, tblAttendance.DocumentReturned, tblAttendance.Comments, tblAttendance.InputBy, tblAttendance.Excused, tblAttendance.ExcusedBy
FROM tblAttendance
WHERE (((tblAttendance.Date) Is Not Null));

[Dal Jeanis]

This code won't work when the guy was hired in October and the review is in March. It will end up having a future Reset Date.

DateSerial(Year([Date]),Month([Hire Date]),Day([Hire Date])) AS [Reset Date]

Another option -

DateAdd("YYYY", DateDiff("YYYY", [Hire Date] - 1, Date), [Hire Date]) AS [ResetDate]

I'm not sure whether Access will let you subtract 1 day from the second parm of DateDiff or not. It would work in Excel. If that works, then this code handles the exact-date-of-hire issue. If not, you may need an IIF() to handle it.

By the way, this field name is very risky... tblAttendance.Date.

[orange]

Simplifying your query -- since you only deal with 1 table, you do NOT need to qualify each field with the TableName

SELECT [Transaction ID]
, [Assoc Nm]
, [Assoc ID]
, [Dpt Nbr]
, [Dpt Nm]
, [Job Ttl Cd]
, [Emp Ctg]
, [A/I]
, [Hire Date]
, MonthsatHD
, Shift
, Reason
, DateSerial(Year([Date]),Month([Hire Date]),Day([Hire Date])) AS [Reset Date]
, Category
, Date
, [Hours Missed]
, Leader
, DocumentGiven
, DocumentReturned
, Comments
, InputBy
, Excused
, ExcusedBy
FROM tblAttendance
WHERE (((Date) Is Not Null));

However, Date is a Access reserved word (a function that returns today's Date) and today's Date can never be Null so your WHERE condition is doing nothing, as I see it.

I'm not sure now of what exactly you want to display, but I think you'll need an IIF (immediate if) along these lines

IIF( the date this year has passed then
use the date for this year
else
use the date from last year)

IIF(DateDiff("d",Date,DateSerial(Year([Date]),Month([Hire Date]),Day([Hire Date])) > 0 ,
DateSerial(Year([Date]),Month([Hire Date]),Day([Hire Date])),
DateSerial(Year([Date]) - 1 ,Month([Hire Date]),Day([Hire Date]))) as ResetDate

and i think this IIF statement would replace this line in your query

DateSerial(Year([Date]),Month([Hire Date]),Day([Hire Date])) AS [Reset Date]

Good luck.

[smc678]

I think your on to somthing!! I placed this into the query and it says its missing a closing parenthesis, bracket or vertical bar. I dont see one missing do you?

[smc678]

Orange- Am I missing somthing? I placed this into the query and it says its missing a closing parenthesis, bracket or vertical bar.

[orange]

Add another closing parenthesis before "as ResetDate"

eg

IIF(DateDiff("d",Date,DateSerial(Year([Date]),Month([Hire Date]),Day([Hire Date])) > 0 ,
DateSerial(Year([Date]),Month([Hire Date]),Day([Hire Date])),
DateSerial(Year([Date]) - 1 ,Month([Hire Date]),Day([Hire Date])))) as ResetDate

Good luck.

[smc678]

The expression you entered has a function containing the wrong number of arguments error

[Dal Jeanis]

IIF(DateDiff("d",Date,DateSerial(Year([Date]),Month([Hire Date]),Day([Hire Date]))) > 0 ,
DateSerial(Year([Date]),Month([Hire Date]),Day([Hire Date])),
DateSerial(Year([Date]) - 1 ,Month([Hire Date]),Day([Hire Date]))) as ResetDate

Kind of old school, I had to do this to find it-

IIF
(
DateDiff
    ("d",
    Date,
    DateSerial
       (Year([Date]),
        Month([Hire Date]),
        Day([Hire Date])
        ) 
    ) > 0,
DateSerial
   (Year([Date]),
    Month([Hire Date]),
    Day([Hire Date])
    ),
DateSerial
   (Year([Date]) - 1,
    Month([Hire Date]),
    Day([Hire Date])
    )
) as ResetDate

[smc678]

So that fixed the error Thank you!! But its still not returning whats needed.

IIF( the date this year has passed then
use the date for this year
else
use the date from last year)

this is what the data should return the [date] collum refers to the date of the attendance error

This is whats returning
Hire Date	ResetDate	Date
2/18/2013	2/18/2012	5/29/2013
12/13/2010	12/13/2013	5/27/2013
This is what it should return
Hire Date	ResetDate	Date
2/18/2013	2/18/2013	5/29/2013
12/13/2010	12/13/2012	5/27/2013

[orange]

Change the > 0 to <= 0 and see if that reverses the logic.

Sorry about the missing bracket, I was just trying to match brackets. Thanks Dal for laying the IIF out so it was more readable.
The only thing I can see now is that the logic is reversed, so I'd try <= 0 instead of > 0.

Here's a sample test routine with results:

'---------------------------------------------------------------------------------------
' Procedure : HiredateStuff
' Author    : Jack
' Date      : 21-06-2013
' Purpose   : Routine to test the HireDate based on
'   https://www.accessforums.net/queries/im-trying-create-formula-date-range-within-35776.html#post177973
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Sub HiredateStuff()
Dim HDate1 As Date
Dim HDate2 As Date
Dim HireDate As Date
Dim ResetDate As Date
   On Error GoTo HiredateStuff_Error

HDate1 = #2/18/2013#
HDate2 = #12/13/2010#
For i = 1 To 2
    If i = 1 Then
        HireDate = HDate1
    Else
        HireDate = HDate2
    End If
    ResetDate = IIf(DateDiff("d", Date, DateSerial(Year(Date), Month([HireDate]), Day([HireDate]))) <= 0, _
     DateSerial(Year(Date), Month([HireDate]), Day([HireDate])), _
     DateSerial(Year(Date) - 1, Month([HireDate]), Day([HireDate])))
    Debug.Print "With HireDate = " & HireDate & " ,  the resetdate is  " & ResetDate
Next i

   On Error GoTo 0
   Exit Sub

HiredateStuff_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure HiredateStuff of Module AWF_Related"
End Sub

And here is the output from Debug.print

With HireDate = 2/18/2013 ,  the resetdate is  2/18/2013
With HireDate = 12/13/2010 ,  the resetdate is  12/13/2012

You should mark these as reference sources:

1. http://www.techonthenet.com/access/f...e/datediff.php MSAccess functions with examples
2. http://www.cpearson.com/excel/DebuggingVBA.aspx Chip Pearson Debugging techniques

Good luck with your project.