Results 1 to 5 of 5
  1. #1
    Join Date
    May 2002
    Posts
    157

    Unanswered: If and If then (If something and if something then do something)

    Hi there,
    Sorry about the previous lodgements of this - for some reason the system seemed to hang and they would not load up.

    I have a form which contains the following fields:
    [Active/Inactive] and [EndDateOfPlacement]

    I want to check to see:
    if the [Active/Inactive] field value = Inactive
    and the [EndDateOfPlacement] is null
    then a message is shown.

    I have written some code (see below) but it does not completely behave the way I want it Ie:

    If [Active/Inactive] = Inactive and the [EndDateOfPlacement] is null
    Then the message is shown. This is what I want to have happen.

    If [Active/Inactive] = Active and the [EndDateOfPlacement] is null
    Then the message is shown. This should not happen.

    If [Active/Inactive] = Inactive and the [EndDateOfPlacement] has a date entered
    Then the message is shown. This should not happen.


    The code I have written is as follows, can someone tell me what I am doing wrong? - Thanks Heaps

    Private Sub Form_Close()
    On Error GoTo Form_Close_Err

    If Me.[Active/Inactive].Value = "Inactive" Then
    If Not Me.[EndDateOfPlacement] Then
    Call MsgBox("This record is showing as 'Inactive' but there is no END DATE entered." _
    & vbCrLf & "" _
    & vbCrLf & "Please enter the PLACEMENT END DATE or change the record back to 'Active'." _
    , vbExclamation, "INACTIVE RECORD WITH NO END DATE")
    End If
    End If

    Form_Close_Exit:
    Exit Sub

    Form_Close_Err:
    MsgBox Error$
    Resume Form_Close_Exit

    End Sub


    Thanks heaps

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by Karen Day View Post
    Hi there,
    Sorry about the previous lodgements of this - for some reason the system seemed to hang and they would not load up.

    I have a form which contains the following fields:
    [Active/Inactive] and [EndDateOfPlacement]

    I want to check to see:
    if the [Active/Inactive] field value = Inactive
    and the [EndDateOfPlacement] is null
    then a message is shown.

    I have written some code (see below) but it does not completely behave the way I want it Ie:

    If [Active/Inactive] = Inactive and the [EndDateOfPlacement] is null
    Then the message is shown. This is what I want to have happen.

    If [Active/Inactive] = Active and the [EndDateOfPlacement] is null
    Then the message is shown. This should not happen.

    If [Active/Inactive] = Inactive and the [EndDateOfPlacement] has a date entered
    Then the message is shown. This should not happen.


    The code I have written is as follows, can someone tell me what I am doing wrong? - Thanks Heaps

    Private Sub Form_Close()
    On Error GoTo Form_Close_Err

    If Me.[Active/Inactive].Value = "Inactive" Then
    If Not Me.[EndDateOfPlacement] Then
    Call MsgBox("This record is showing as 'Inactive' but there is no END DATE entered." _
    & vbCrLf & "" _
    & vbCrLf & "Please enter the PLACEMENT END DATE or change the record back to 'Active'." _
    , vbExclamation, "INACTIVE RECORD WITH NO END DATE")
    End If
    End If

    Form_Close_Exit:
    Exit Sub

    Form_Close_Err:
    MsgBox Error$
    Resume Form_Close_Exit

    End Sub


    Thanks heaps
    Hi

    Perhapse this
    Code:
    If Me.[Active/Inactive].Value = "Inactive" Then
            If IsNull(Me.[EndDateOfPlacement]) Then
                Call MsgBox("This record is showing as 'Inactive' but there is no END DATE entered." & vbCrLf & vbCrLf & _
                "Please enter the PLACEMENT END DATE or change the record back to 'Active'." _
                , vbExclamation, "INACTIVE RECORD WITH NO END DATE")
            End If
        End If
    ??

    ie. Use IsNull()

    MTB

  3. #3
    Join Date
    May 2002
    Posts
    157

    Sorry the code you supplied does not seem to work

    I cut and pasted your suggested code into my code.

    Now I am not getting any msgbox messages.

    Thanks for trying

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Will there ever be a status other than active or inactive stored in your [Active/Inactive] field? If not, I would urge you to go with an "Active" field set to a bit (yes/no) field. This will make reporting much easier as well as making it a good deal more intuitive for any future maintenance issues.

    If you were to go this route, you could test for EndDateOfPlacement date and active bit like so:

    Nz(enddateOfPlacement, active) = false
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Karen,
    You cannot test for a Null value with the Not operator, you have to use NZ() or IsNull() instead.
    Have a nice day!

Posting Permissions

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