DateAdd("yyyy" problem 1

[Dawnit]

Hi gurus!

If I have this formula in my update query:

IIf([Descriptions Retentions]![Perm]=Yes,Null,DateAdd("yyyy",([Descriptions Retentions]![DeptRet]+[Descriptions Retentions]![WrhseRet]),[A/P Records]![DateTo]))

How can I get it to return 12/31/yy no matter what date is in the [DateTo] field?

FYI - [Perm] is a check box field

Thanks a bunch!

 

[MichaelRed]

DateSerial(YYYY, 12, 31)

Where YYYY is the Four Digit Year Value.

If you want to "simplify" this just put in the two digit year. It will be O.K. till ~~~ 2035?

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[tlbroadbent]

You can use the DateSerial function to return 12/31 of the year in this manner.

DateSerial(Year([A/P Records]![DateTo]),12,31))

Return 12/31 of the current year.

DateSerial(Year(date()),12,31))

Let me know if this was what you needed. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[Dawnit]

What I am trying to do is add to the year of [DateTo] by [DeptRet]+[WrhseRet] AND have the final answer be 12/31/yy

Example:
if Perm] is Null
DateTo is 6/30/00
and DeptRet]+[WrhseRet] is 2 + 5
I want the result to be 12/31/07

Please read my original formula again if it helps.

Thanks again!

 

[tlbroadbent]

The following determines the year in the field [DateTo] and adds the values [DeptRet] and [WrhseRet] to the year. The DateSerial Function takes the resultant year, month 12 and day 31 as paraemeteres to return a reult of 12/31/year.

DateSerial(Year([A/P Records]![DateTo]) +
[Descriptions Retentions]![DeptRet] +
[Descriptions Retentions]![WrhseRet]), 12, 31)) Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[tlbroadbent]

I saw typos just as I hit submit.

DateSerial(Year([A/P Records]![DateTo]) +
[Descriptions Retentions]![DeptRet] +
[Descriptions Retentions]![WrhseRet], 12, 31) Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[MichaelRed]

Dawnit contacted me and requested that I follow up on this thread. To simplify my effort, I used a procedure, with the calling arguments of the field names referenced in the IIF statement supplied. In glancing at the overall thread, I did notice that collectively, we appeared to be ignoring the conditional part, perhaps in the expectation that Dawnit was experienced and the real issue lay in the calculation. From the initial post, it appears that [Perm] is a flag / boolean, [DateTo] is a date while [DeptRet] and [WrhseRet] are both integers. Hopefully, this post is superfolous, however since I went to the effort to generate and check it, I'll post it anyway.

Public Function basDateAdd(Perm As Boolean, _
                           DateTo As Date, _
                           DeptRet As Integer, _
                           WrhseRet As Integer) As Variant

    'To return #12/31/YYYY#, or NULL
    Dim MyDateAdd As Date

    If (Perm) Then
        MyDateAdd = DateSerial(Format(DateTo, "YY") + DeptRet + WrhseRet, 12, 31)
        basDateAdd = Format(MyDateAdd, "mm/dd/yyyy")
    End If

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Dawnit]

Thanks MichselRed for your perseverence!

Here is what finally worked:

IIf([Descriptions Retentions]![Perm]=Yes,Null,DateSerial(Year([A/P Records]![DateTo])+[Descriptions Retentions]![DeptRet]+[Descriptions Retentions]![WrhseRet],12,31))

And, I have to make sure this is a date in the DateTo field, otherwise I get a data mismatch error.

I really appreciate your help!!