Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form / Subform Date Comparisons (A2K)

    I'm trying to compare two dates between a Main Form and a SubForm in order to have the Subform date contain the most current of the two.

    This works when both dates are on the MainForm just to establish that the comparison of two dates routine works:

    =IIf([MF01 Date]>[ MF02Date],[MF01 Date],[MF02Date])


    This DOES NOT work when comparing the MF01 Date on MainForm to SBF02 Date on SubForm

    =IIf([Forms]![MainForm]![MF01 Date]>[Forms]![SubForm]![SBF02 Date],[Forms]![MainForm]![MF01 Date],[Forms]![SubForm]![SBF02 Date])

    Somewhere I have done grievious bracket injuries and I'm not sure where. Would appreciate any help,

    Andy
    Cheers,
    Andy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form / Subform Date Comparisons (A2K)

    A subform is not part of the Forms collection, only main forms are. Where do you want to use this expression?

    1. On the main form:

    =IIf([MF01 Date]>[SubForm]![SBF02 Date],[MF01 Date],[SubForm]![SBF02 Date])

    Note: you must use the name of the subform as a control on the main form; this is not necessarily the same as the name it has in the database window.

    2. On the subform:

    =IIf([Parent]![MF01 Date]>[SBF02 Date],[Parent]![MF01 Date],[SBF02 Date])

    3. From another form:

    =IIf([Forms]![MainForm]![MF01 Date]>[Forms]![MainForm]![SubForm]![SBF02 Date],[Forms]![MainForm]![MF01 Date],[Forms]![MainForm]![SubForm]![SBF02 Date])

    See Refer to Form and Subform properties and controls on Dev Ashish's Access Web for a comprehensive overview of referring to forms, subforms and their controls.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form / Subform Date Comparisons (A2K)

    If at first I don't succeed, confuse, confuse again. OK, this time I'll try to be a little more clear as to what everything is named. I'm even going to change the names just to cloud the issue. I did check Dev's site and it certainly bears further study, but seeing as I'm on a downhill roll, I think I'd like to clarify the current situation before I cloud my mind with even more valuable information. So,

    MAIN FORM
    Main Form Name: frm DPM Projects
    Date Control Name: txtStatRep01
    Cheers,
    Andy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form / Subform Date Comparisons (A2K)

    If the expression is the Control Source of a control on the subform, use this:

    =IIf([Parent]![txtStatRep01 - Date]>[txt CurrentReportDate],[Parent]![txtStatRep01 - Date],[txt CurrentReportDate])

    Note that I use Parent to refer to the main form, and that I use the names of the controls, not the control sources.

    Recommendation for the future: avoid using spaces in the names of tables, queries, forms, fields, controls etc. Working with them in expressions and in Visual Basic is much easier if each name is one word. You can still use spaces in the captions of labels etc.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form / Subform Date Comparisons (A2K)

    Hans,

    It is not a good thing to go into a weekend with something like this hanging over my head. Here is the latest version which reflects your wise suggestion to drop spaces

    PARENT FORM
    Form Name: frmDPMProjects ( was frm DPM Projects )
    Date Control Name: txtStatusDate ( was txtStatRep01
    Cheers,
    Andy

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form / Subform Date Comparisons (A2K)

    I didn't use Parent for nothing. Try

    =IIf([Parent]![txtStatusDate]>[txtCurrentDate],[Parent]![txtStatusDate],[txtCurrentDate])

  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form / Subform Date Comparisons (A2K)

    Hans,

    Is there any way that you could possibly email me some eggs so that I could put them on my face? I know that if I stare at something long enough, I'll never get it. Thanks again for your help and obvious patience.

    Have a good weekend,

    Andy
    Cheers,
    Andy

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form / Subform Date Comparisons (A2K)

    Hans

    Last note on this problem in case anybody else was interested enough to watch me go down in flames. The routine worked perfectly once I followed your 'code' sample exactly. It therefore set up a scenario wherein, if the Current Date on the subform was less than the new Date on the 'Parent' form, the new date would be calculated and displayed on the subform in field other than the Current Date. However, to make it really useful, it was necessary for this new date to replace the Current Date, so
    Cheers,
    Andy

Posting Permissions

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