DateAdd data type ?

[sarahb845]

I am having a problem with the following 2 expressions.

The 1st expression contains a DateAdd function and is used in the 2nd expression.


Expr1: IIf([FLSAStatus]="N",Format(DateAdd("m",6,[HireDate]),"mm/dd"),"07/01")

Expr2: CDate(IIf((Format(Date(),"mm/dd")<=[Expr1]),Format(([Expr1] & "/" & Year(Date())),"mm/dd/yyyy"),Format(([Expr1] & "/" & (Year(Date())+1)),"mm/dd/yyyy")))


When I try to sort on Expr2, I get a Data Type mismatch error. I think the problem is that the first expression has the DateAdd function in it. When I remove it, I no longer get the error. I assumed that Expr1 would be a String datatype b/c of the Format() function. And, I assumed that Expr2 was also a String datatype - that is why I converted it with the CDate() function.

I don't care what datatype Expr1 is, but I need to use the DateAdd function to add 6 months (using [HireDate] + 180 is NOT what I want). And, Expr2 needs to be Date datatype.

I'm not sure how to fix my problem.

Anyone??

 

[Jon K]

When you sort on Expr2, the Select statement (in the query's SQL View) becomes:

SELECT IIf([FLSAStatus]="N",Format(DateAdd("m",6,[HireDate]),"mm/dd"),"07/01") AS Expr1,
CDate(IIf((Format(Date(),"mm/dd")<=[Expr1]),Format(([Expr1] & "/" & Year(Date())),"mm/dd/yyyy"),Format(([Expr1] & "/" & (Year(Date())+1)),"mm/dd/yyyy"))) AS Expr2
FROM yourTable
ORDER BY CDate(IIf((Format(Date(),"mm/dd")<=[Expr1]),Format(([Expr1] & "/" & Year(Date())),"mm/dd/yyyy"),Format(([Expr1] & "/" & (Year(Date())+1)),"mm/dd/yyyy")));


Here Expr1 is used three times in the Order By clause. However, before the query is run, Access has no idea what Expr1 is, so it will pop up an input box asking the user to supply the value for Expr1.


You have two alternatives here. In SQL View,
1) replace the three Expr1's in the Order By clause with its expression, or
2) change the Order By clause to the number representing the position of Expr2 in the Select clause. In the example above, Expr2 occupies the second position in the select clause, so change the Order By clause to ORDER BY 2 or ORDER BY 2 DESC


Hope this helps.

(Remarks: When I ran the above query in Access 97, I didn't get any data type mismatch error.)

 

[sarahb845]

Hmmm, I'm not sure if this is really what I wanted.

When I say I want to sort on Expr2, I don't want it to affect the SQL statement. I am trying to do column sorting in the datasheet view (selecting either Sort Ascending or Sort Descending when I right click on the column).

I have used these same formulas - Expr1 and Expr2 in another query. The only difference between this query (qselDoesNotWork) that is giving me the datatype mismatch problem, and the other one that does work (qselDoesWork), is that there is no DateAdd function being used in Expr1 of qselDoesWork. So, when I remove the DateAdd function from Expr1 in the qselDoesNotWork, I am able to do the column sorting on Expr2 that I talked about.

I guess my question is why DateAdd has this affect?? And, what can I do to make the datatypes match?? I am uncomfortable leaving the formulas as is, with unmatching datatypes.

Help....

 

[sarahb845]

Ok, I figured out the problem.

Expr1 was adding 6 months to the HireDate, and then formatting that to be mm/dd.

Expr2 was using Expr1 to add the correct year (either this year if month/day had not yet passed, or the next year, if it had passed).

What was happening was that when HireDate was 8/30/1999, adding 6 months to that gave 2/29/2000. ** LEAP YEAR ** Then formatting that to 2/29. When trying to figure out the what year is next for that date, Expr2 was trying to make it 2/29/2003, which is NOT a LEAP YEAR. When converting that using CDate, an #Error occured. That is why I could not sort.

Problem solved by adding another column to query called HireDateMMDD which did: Format([HireDate],"mm/dd"). Then replacing the [HireDate] in Expr1 with [HireDateMMDD].

BUT - did I cause a new problem??

Question: Is this new format going to cause problems when the next date should fall on a leap year day? For example, in 2004, will this make Expr2 equal to 2/29/2004? or will it be 2/28/2004????? If so, how do I resolve all of this leap year stuff????????

 

[sarahb845]

Sort of -

What I am trying to do is figure out when mid-year performance reviews are due, which are based on an employee's status (either Exempt or Non-Exempt).

If Status = Exempt, then review will occur on 07/01/OfEachYear. OfEachYear needs to roll over to the next year when it passes for this year. So, all Exempt employee's next mid-year review will be due on 07/01/2003 (because 07/01 for this year has passed.)

If Status = Non-Exempt, then review will occur every year on the 6 month anniversary of their hire date. So if an employee is hired on 3/12/1999, their next mid-year review is due on 9/12/2002. Just adding six months to their HireDate will not work. If I did DateAdd("m",6,[HireDate]), where HireDate is 3/12/1999, the result would be 9/12/2000. Not what I want.

Because the year's are always changing, I thought it was best to Format([HireDate],"mm/dd") to get just month/day. But, then I run into the Leap Year problem....

 

[jaylee]

In vb create a function

Function NextReviewDate(HireDate As Date, strStatus As String)

If strStatus = "Exempt" Then
NextReviewDate = DateSerial(Year(Date) + IIf(Month(Date) < 7, 0, 1), 7, 1)
Else
NextReviewDate = HireDate
Do Until NextReviewDate > Date

NextReviewDate = DateAdd("m", 6, NextReviewDate)

Loop

End If


End Function