Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Sep 2003
    Posts
    67

    Unanswered: Need calc'd values from form in report

    I have a form with three unbound controls -- Interval2004, Interval2005, and Interval2006 -- that calculate the number of months between various dates. The code for this calculation is included below.

    I added three corresponding unbound controls to a report (same names as in form) and set =forms!FormName!Interval2004 (2005, 2006) as the control source, but that didn't work. It pulls in the values from whatever record the form is open to and attaches it to each record in the report.

    What do I need to do in order for the intervals to appear correctly in the report? Please note: I'm not a programmer (nor do I play one on TV), so if the solution involves code, please be very specific, talk slowly, and use small words.

    THANKS!
    _____________________________________

    FORM CODE:

    Option Compare Database

    Private Sub AddNewRecord_Click()
    On Error GoTo Err_AddNewRecord_Click


    DoCmd.GoToRecord , , acNewRec

    Exit_AddNewRecord_Click:
    Exit Sub

    Err_AddNewRecord_Click:
    MsgBox Err.Description
    Resume Exit_AddNewRecord_Click

    End Sub

    Private Sub Date2004_BeforeUpdate(Cancel As Integer)
    funIntervalUpdate
    End Sub

    Private Sub Date2005_BeforeUpdate(Cancel As Integer)
    funIntervalUpdate
    End Sub

    Private Sub Date2006_BeforeUpdate(Cancel As Integer)
    funIntervalUpdate
    End Sub

    Private Sub Form_Current()
    funIntervalUpdate
    End Sub

    Private Sub LastAudit_BeforeUpdate(Cancel As Integer)
    funIntervalUpdate
    End Sub

    Private Function funIntervalUpdate()

    'Set 2004 Interval
    If Not IsDate(Date2004) Then
    Interval2004 = ""
    ElseIf Not IsDate(LastAudit) Then
    Interval2004 = ""
    Else
    Interval2004 = DateDiff("m", LastAudit, Date2004)
    End If

    'Set 2005 Interval
    If Not IsDate(Date2005) Then
    Interval2005 = ""
    ElseIf Not IsDate(Date2004) Then
    If Not IsDate(LastAudit) Then
    Interval2005 = ""
    Else: Interval2005 = DateDiff("m", LastAudit, Date2005)
    End If
    Else: Interval2005 = DateDiff("m", Date2004, Date2005)
    End If

    'Set 2006 Interval
    If Not IsDate(Date2006) Then
    Interval2006 = ""
    ElseIf Not IsDate(Date2005) Then
    If Not IsDate(Date2004) Then
    If Not IsDate(LastAudit) Then
    Interval2006 = ""
    Else: Interval2006 = DateDiff("m", LastAudit, Date2006)
    End If
    Else: Interval2006 = DateDiff("m", Date2004, Date2006)
    End If
    Else: Interval2006 = DateDiff("m", Date2005, Date2006)
    End If

    End Function

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Jay,

    Why aren't you doing this within the report body? This can be done on a detail basis ... Also where are you displaying this difference?

  3. #3
    Join Date
    Sep 2003
    Posts
    67

    Need calc'd values from form in report

    Mike,

    That's what I'm asking ... how do I do this in the report? I want the intervals to show in both the form and the report.

    I've tried copying code from the form (included in the original post) into the report detail, but I'm not copying the right portion or I'm putting it in the wrong place. If the code needs to be modified before adding it to the report, I'd need specifics on that.

    I've attached a Word doc with screenshots of the form and the report. The terms Frequency and Interval mean the same thing -- the number of months from one audit to the next.

    Thanks!
    Attached Files Attached Files

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Jay,

    All you do is add an Edit box(Text Box) that will display the interval in question and put into it the formula for calcing the difference (your datediff function) ... Make sure you use the names of the other text fields in the report form for these calculations ...

  5. #5
    Join Date
    Sep 2003
    Posts
    67
    Mike,

    Does "put into it the formula for calcing the difference (your datediff function)" mean to put this

    If Not IsDate(Date2004) Then
    Interval2004 = ""
    ElseIf Not IsDate(LastAudit) Then
    Interval2004 = ""
    Else
    Interval2004 = DateDiff("m", LastAudit, Date2004)
    End If

    as the control source in the box properties? (All field names are consistent.) It needs the If Not IsDate and ElseIf statements in order to calculate correctly, but when I entered the whole section (above) as the control source, it asked for the parameters for If Not IsDate, etc.

    Thanks,

    Jay

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Basically ... yes. The only difference is that you refernce the text control names inplace of the table field names ... Example:

    Text Control Name: Int2004DtTxt

    Replace Interval2004 with [Int2004DtTxt]

    Get my drift???? Turn you into a programmer yet ...

  7. #7
    Join Date
    Sep 2003
    Posts
    67

    Getting there ...

    OK, I named the text boxes as instructed, then entered the following into the respective control source fields for each year. But I'm getting a syntax error message that the expressions are missing an operator. I tried putting "=" at the beginning, but that didn't have any effect.

    What's missing?? Thanks!

    Box named Int2004DtTxt:

    If Not IsDate[Dt2004Txt] Then
    [Int2004DtTxt] = ""
    ElseIf Not IsDate[LastAuditDtTxt] Then
    [Int2004DtTxt] = ""
    Else
    [Int2004DtTxt] = DateDiff("m", [LastAuditDtTxt], [Dt2004Txt])
    End If

    Box named Int2005DtTxt:

    If Not IsDate[Dt2005Txt] Then
    [Int2005DtTxt] = ""
    ElseIf Not IsDate[Dt2004Txt] Then
    If Not IsDate[LastAuditDtTxt] Then
    [Int2005DtTxt] = ""
    Else: [Int2005DtTxt] = DateDiff("m", [LastAuditDtTxt], [Dt2005Txt])
    End If
    Else: [Int2005DtTxt] = DateDiff("m", [Dt2004Txt], [Dt2005Txt])
    End If

    Box named Int2006DtTxt:

    If Not IsDate[Dt2006Txt] Then
    [Int2006DtTxt] = ""
    ElseIf Not IsDate[Dt2005Txt] Then
    If Not IsDate[Dt2004Txt] Then
    If Not IsDate[LastAuditDtTxt] Then
    [Int2006DtTxt] = ""
    Else: [Int2006DtTxt] = DateDiff("m", [LastAuditDtTxt], [Dt2006Txt])
    End If
    Else: [Int2006DtTxt] = DateDiff("m", [Dt2004Txt], [Dt2006Txt])
    End If
    Else: [Int2006DtTxt] = DateDiff("m", [Dt2005Txt], [Dt2006Txt])
    End If

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Well you gotta do this in SQL speak ...

    For Dt2004Txt:

    Set the RowSource to: =Iif(IsDate([Dt2004Txt]) AND IsDate([LastAuditDtTxt]), DateDiff("m", [LastAuditDtTxt], [Dt2004Txt]),"")

  9. #9
    Join Date
    Sep 2003
    Posts
    67
    OK, that worked for 2004 (thank you!). I tried to translate the other two into SQL following the pattern you provided for 2004, but they're more complex, and I got lost. I'm not seeing the connection between the VB code and SQL -- as in "wherever this appears, replace it with this" -- especially since there are more variables for 2005 and 2006.

    Bottom line: taking the 2004 sample and applying it to the remaining two years is beyond my training and understanding of both VB and SQL.

    I'd be VERY grateful for your help on the syntax needed for the other two boxes.

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Jay,

    Here's what you need to do for me: Clean up the conditionals (if statements) so that I can correctly interpret the ordering ... Right now I can't tell what else goes with what if ...

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Jay,

    Here's the 2nd formula:

    iif(isdate([Dt2005Txt]) AND isdate([Dt2004Txt]),DateDiff("m", [Dt2004Txt], [Dt2005Txt]),iif(not isdate([Dt2004Txt]) AND isdate([LastAuditDtTxt]),DateDiff("m", [LastAuditDtTxt], [Dt2005Txt]),""))

    Try that ...

  12. #12
    Join Date
    Sep 2003
    Posts
    67
    Sorry ... is this better?

    Box named Int2005DtTxt:

    If Not IsDate[Dt2005Txt] Then
    .....[Int2005DtTxt] = ""
    ElseIf Not IsDate[Dt2004Txt] Then
    .....If Not IsDate[LastAuditDtTxt] Then
    ..........[Int2005DtTxt] = ""
    ..........Else: [Int2005DtTxt] = DateDiff("m", [LastAuditDtTxt],
    ..........[Dt2005Txt])
    .....End If
    Else: [Int2005DtTxt] = DateDiff("m", [Dt2004Txt], [Dt2005Txt])
    End If

    Box named Int2006DtTxt:

    If Not IsDate[Dt2006Txt] Then
    .....[Int2006DtTxt] = ""
    ElseIf Not IsDate[Dt2005Txt] Then
    .....If Not IsDate[Dt2004Txt] Then
    ..........If Not IsDate[LastAuditDtTxt] Then
    ...............[Int2006DtTxt] = ""
    ...............Else: [Int2006DtTxt] = DateDiff("m", [LastAuditDtTxt],
    ...............[Dt2006Txt])
    ..........End If
    ..........Else: [Int2006DtTxt] = DateDiff("m", [Dt2004Txt], [Dt2006Txt])
    ..........End If
    ..........Else: [Int2006DtTxt] = DateDiff("m", [Dt2005Txt], [Dt2006Txt])
    ..........End If

  13. #13
    Join Date
    Sep 2003
    Posts
    67
    BTW, I tried the formula for 2005, but got an error message that there was an extra paren in the expression. They all look logical to me (for what that's worth), but I tried experimenting by removing one from here or there, but still got the same message.

  14. #14
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Jay,

    You set the RowSource of Dt2005Txt to:

    =IIf(IsDate([Dt2005Txt]) And IsDate([Dt2004Txt]), DateDiff("m", [Dt2004Txt], [Dt2005Txt]), IIf(Not IsDate([Dt2004Txt]) And IsDate([LastAuditDtTxt]), DateDiff("m", [LastAuditDtTxt], [Dt2005Txt]), ""))

    Right? and you got errors? I just assigned the conditional to a variable and no syntax errors here ...

  15. #15
    Join Date
    Sep 2003
    Posts
    67
    OK, 2005 works. I left out the = sign (*blush*).

    I made a valiant attempt to go ahead with 2006, and here's what I came up with (no laughing):

    =IIf(IsDate([Dt2006Txt]) And IsDate([Dt2004Txt]),
    DateDiff("m",[Dt2004Txt],[Dt2006Txt]),
    IIf(Not IsDate([Dt2004Txt]) And IsDate([LastAuditDtTxt]),
    DateDiff("m",[LastAuditDtTxt],[Dt2005Txt]),""))

    I don't get any error messages, and it calcs the difference between 2004 and 2006 correctly, but it doesn't calculate the difference between 2006 and LastAuditDate at all, and it repeats the 2005 interval in the 2006 interval column.

    Where'd I botch this one?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •