Iff statement (1 Viewer)

rainbows

Registered User.
Local time
Yesterday, 21:50
Joined
Apr 21, 2017
Messages
425
HI

This is a formula that I use but i have discovered a problem with my form

IIf([Status]="closed",[Action Taken] Is Not Null

i have discovered that i also need to have the "date closed " field also filled in

i have tried to modify this with out success

both field must be filled in before they can go to the next record

any help appreciated

steve
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 00:50
Joined
Feb 19, 2002
Messages
42,981
The expression you posted is invalid. What is the actual expression?
 

rainbows

Registered User.
Local time
Yesterday, 21:50
Joined
Apr 21, 2017
Messages
425
hi

this is a validation rule in the table in the status field properties

this works ok just for that one field "action taken"

IIf([Status]="closed",[Action Taken] Is Not Null)

steve
 

lpapad

Registered User.
Local time
Today, 06:50
Joined
Jul 7, 2018
Messages
47
Your commands is not valid command.

Syntax

IIf ( expr , truepart , falsepart )

expr Required. Expression you want to evaluate.

truepart Required. Value or expression returned if expr is True.

falsepart Required. Value or expression returned if expr is False.
 

isladogs

MVP / VIP
Local time
Today, 04:50
Joined
Jan 14, 2017
Messages
18,186
As others have already said, your IIf statement is incorrect.
But even if you had entered a false part as required it makes no sense..

You could set the ActionTaken field to some value or other BUT you can't set it to 'Not Null'

Am I correct in thinking you originally wanted the following:
If ActionTaken is not null then Status = Complete otherwise Status = Incomplete (or similar or just blank)

If so, your IIF statement is the wrong way round.
Once that is clarified it's easy enough to add the DateClosed part.

BUT my gut feeling is that is still not the way to go
I would instead use an If ...End If statement and an update query.

Code:
If Nz(ActionTaken,'')<>'' And Not IsNull(DateClosed) Then
       'Run update query to set Status =Complete
End If

Am I on the right lines?
 
Last edited:

Cronk

Registered User.
Local time
Today, 15:50
Joined
Jul 4, 2013
Messages
2,770
rainbows, you seem to want to test if [Status] is closed and DateClosed is not null


If so then
Code:
if [Status]="closed and not isnull(DateClosed) then
   whatever you want to happen if condition is met

else
   whatever you want to happen if condition not met

endif
This can also be achieved with
Code:
iif( [Status]="closed and not isnull(DateClosed),whatever you want to happen if condition is met, whatever you want to happen if condition not met
endif)
I only use the latter method if the expressions are short and simple. The first method is otherwise more readable and understood.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 00:50
Joined
Feb 19, 2002
Messages
42,981
Rather than having us guess what you are trying to do, please tell us in business terms what you want this expression to do.
 

rainbows

Registered User.
Local time
Yesterday, 21:50
Joined
Apr 21, 2017
Messages
425
hi

I have 3 fields in my table called
1, status
2 date closed
3 action taken


in the status field there are two choices ( active or closed ) it defaults to active

when a user selects " closed " in the status field the other 2 fields "action taken " and "date closed " must be completed before he can leave that record. if not completed a rompt telling them they need to fill them
you can see the iff statement in the doc attached

at present it all works fine for the "action taken " field if is not completed and the status field has been set to closed
it just adding the "date closed " field to do the same

thanks for your help

steve
 

Attachments

  • Doc1.zip
    250.9 KB · Views: 116

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 23:50
Joined
Feb 28, 2001
Messages
27,001
In general, what you described is handled by writing a Form_BeforeUpdate event that allows you to Cancel the update. Since closing a form implies an update, you can block form closure by blocking the update that would precede it.

Inside the BeforeUpdate event code, you can write as many tests as you want. Here is a VERY simple-minded example.

Code:
Private Sub Form_BeforeUpdate( Cancel as Integer)

    If [Status] = "Closed" Then
        If Len([ActionTaken]) = 0 Then Cancel = True
        If Nz([DateClosed],0) = 0 Then Cancel = True 
    End If

End Sub

The tests (written this way) simply block the event. You can add message boxes that pop up to tell you what is wrong. That's up to you.

Here is more about the event:

https://msdn.microsoft.com/en-us/vba/access-vba/articles/form-beforeupdate-event-access
 
Last edited:

rainbows

Registered User.
Local time
Yesterday, 21:50
Joined
Apr 21, 2017
Messages
425
hi ,
thanks for your help and it is working great
I have tried to put a message box on. to any od the 2 fields but I ma not good at that and I have failed to do it
when I did try it just let the record update even If I did not put a date or action in

thanks for your help

steve
 

Gasman

Enthusiastic Amateur
Local time
Today, 04:50
Joined
Sep 21, 2011
Messages
14,048
hi ,
thanks for your help and it is working great
I have tried to put a message box on. to any od the 2 fields but I ma not good at that and I have failed to do it
when I did try it just let the record update even If I did not put a date or action in

thanks for your help

steve


Something along the lines of ?

Code:
Private Sub BeforeUpdate(Cancel As Integer)
Dim strMsg As String
    If [Status] = "Closed" Then
        If Len([actiontaken]) = 0 Then
            strMsg = "Action Taken is mandatory " & vbCrLf
            Cancel = True
        End If
        If Nz([dateclosed], 0) = 0 Then
            strMsg = "Date Closed is mandatory"
            Cancel = True
        End If
        If Cancel = True Then
            MsgBox strMsg
        End If
    End If

End Sub
 

rainbows

Registered User.
Local time
Yesterday, 21:50
Joined
Apr 21, 2017
Messages
425
hi thanks

if I put the date closed in first it will allow the record to go thro
if I put the action taken in first it will prompt for the date

steve
 

Gasman

Enthusiastic Amateur
Local time
Today, 04:50
Joined
Sep 21, 2011
Messages
14,048
So walk through the code with F8 and see what is in each control. ?


Did you copy and paste my code or type it in yourself?


Please post up the code you have within code tags.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 00:50
Joined
Feb 19, 2002
Messages
42,981
The

If [Status] = "Closed" Then


Needs an Else so you can ensure that there is no closed date.
 

rainbows

Registered User.
Local time
Yesterday, 21:50
Joined
Apr 21, 2017
Messages
425
hi
this is the code I have in the before update event
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)


Dim strMsg As String
    If [Status] = "Closed" Then
        If Len([Action Taken]) = 0 Then
            strMsg = "Action Taken is mandatory " & vbCrLf
            Cancel = True
        End If
       
        If Nz([Date closed], 0) = 0 Then
            strMsg = "Date Closed is mandatory"
            Cancel = True
        End If
        If Cancel = True Then
            MsgBox strMsg
        End If
    End If


 
End Sub
like I said earlier

if I put the action taken in first and try to update if stops me until the closed date is entered

if I put the closed date in first it let me update without the action taken field being completed which is wrong

thanks for your help steve
 

Gasman

Enthusiastic Amateur
Local time
Today, 04:50
Joined
Sep 21, 2011
Messages
14,048
Right, I see an error on my part, but cannot see how it allows Actiontaken to be empty.

Change
Code:
 strMsg = "Date Closed is mandatory"
to
Code:
 strMsg = strMsg & "Date Closed is mandatory"
Walk through the code and inspect Actiontaken


Code:
Len([Action Taken]) = 0
cannot be a good test?
 

Cronk

Registered User.
Local time
Today, 15:50
Joined
Jul 4, 2013
Messages
2,770
but cannot see how it allows Actiontaken to be empty.
If ActionTaken is null then the result of the If statement is false and Cancel will not be set to True


Use
Len([Action Taken] & "") =0
 

rainbows

Registered User.
Local time
Yesterday, 21:50
Joined
Apr 21, 2017
Messages
425
hi

thanks for your help
I changed the code to this
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)


Dim strMsg As String
    If [Status] = "Closed" Then
    
        If Len([Action Taken] & "") = 0 Then
            strMsg = "Action Taken is mandatory " & vbCrLf
            Cancel = True
        End If
        Else
        If Nz([Date closed], 0) = 0 Then
            strMsg = "Date Closed is mandatory"
            Cancel = True
        End If
        If Cancel = True Then
            MsgBox strMsg
        End If
    End If


 
End Sub

this let me now put the date in and when I try to update I get a error message saying you cannot go to the specified record and waits for me to put the Action request details in

I am surprised it did not ask me for the message we set


if I don't put any thing in the date closed field it still lets me thro

steve
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 15:50
Joined
Jan 20, 2009
Messages
12,849
I have 3 fields in my table called
1, status
2 date closed
3 action taken

in the status field there are two choices ( active or closed ) it defaults to active

when a user selects " closed " in the status field the other 2 fields "action taken " and "date closed " must be completed before he can leave that record.

You are working around a normalization error. The Status field should not exist in the table. The presence of a DateClosed value indicates that it is closed.

You should be validating the presence of ActionTaken if there is a DateClosed value.

The presence of an DateClosed value can be assured wherever there is an ActionTaken value by disabling the ActionTaken control until a valid date is entered in DateClosed.
 

Users who are viewing this thread

Top Bottom