Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Jacksonville, North Carolina, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If then or expressions (Access 2000 v 9.0.2720)

    Please see the attached. Any help would be appreciated. Thanks!

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: If then or expressions (Access 2000 v 9.0.2720)

    <P ID="edit" class=small>(Edited by RudiS on 21-Oct-04 09:41. Sorry...in re-inspecting your query, I see i did not work out the dates dynamically from the Inspections date field. The expression will probably contain a DATEADD function nested into the IIF. Let me rework it and post a new reply!)</P>I have not tested this at all, so not sure if it works...but it might give you a start on syntax!
    Try this...
    =IIF([Type]=
    Regards,
    Rudi

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: If then or expressions (Access 2000 v 9.0.2720)

    Here is the new version...still untested...but try it out.
    =IIF([Type]=
    Regards,
    Rudi

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

    Re: If then or expressions (Access 2000 v 9.0.2720)

    If the LRRD and SRRD are always derived from the Type and Inspection Date, and cannot be modified by the user, you don't need them as fields in a table, since they can be calculated in a query. You can use expressions like these:

    LRRD: DateAdd("m",Switch([Type]="IV",36,[Type]="V",12,[Type]="VI",6),[Inspection Date])

    SRRD: DateAdd("m",Switch([Type]="IV",12,[Type]="V",6,[Type]="VI",3),[Inspection Date])

    If the user should be able to modify the LRRD and SRRD, you do need fields in the table. You can't use calculated expressions for them; instead, you must set their value in the After Update event of the Type and Inspection Date controls on a form bound to the table, for example:

    Private Sub Type_AfterUpdate()
    Me.LRRD = DateAdd("m", Switch(Me.[Type] = "IV", 36, Me.[Type] = "V", 12, Me.[Type] = "VI", 6), [Inspection Date])
    Me.SRRD = DateAdd("m", Switch(Me.[Type] = "IV", 12, Me.[Type] = "V", 6, Me.[Type] = "VI", 3), [Inspection Date])
    End Sub

    You can also use a Select Case statement to make the code more readable.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If then or expressions (Access 2000 v 9.0.2720)

    As additional comment to Hans's and Rudy's answer, I would like to suggest to not use Type as a field name. This is a reserved word (statement) and could you bring in problems soon or later.
    Francois

Posting Permissions

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