Coding query (1 Viewer)

Valient

Member
Local time
Today, 10:29
Joined
Jun 21, 2021
Messages
48
Hello,

Can someone please help me to correct the codes below as always ended up error:


Private Sub Date_AfterUpdate()
If Me.Date = "IS NULL" Then Me.Completed = "No"
Else
If Me.Date = "IS NOT NULL" Then Me.Completed = "Yes"
End Sub


The intention of the code is to have an automation value for "Yes and No" whenever the Date field is changed.
If the date field has value then it should be Yes and if null it should be "NO".
 

cheekybuddha

AWF VIP
Local time
Today, 07:29
Joined
Jul 21, 2014
Messages
2,237
Hi,

Try it this way:
Code:
Private Sub Date_AfterUpdate()

  Me.Completed = IIf(IsDate(Me.[Date]), "Yes", "No")

End Sub

Really consider renaming your field "Date" as it's a reserved word in Access.

hth,

d
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:29
Joined
May 7, 2009
Messages
19,169
Code:
Private Sub Date_AfterUpdate()
If IsNull(Me.Date) Then
   Me.Completed = "No"
Else
   Me.Completed = "Yes"
End Sub

Private Sub Form_Current()
Call Date_AfterUpdate
End Sub

Test this in immediate window:

?IsDate("3a1-2-3")
 

Minty

AWF VIP
Local time
Today, 07:29
Joined
Jul 26, 2013
Messages
10,355
Further to David's suggestion, change the field name to DateCompleted, and do away with your completed field.
The DateCompleted tells you two things, is it completed and when, your Completed field is redundant and needs maintaining based on changes to the DateCompleted.

Generally, a Date field is much more valuable from a data perspective than a Boolean Yes or No.
ReviewedDate vs ReviewedYes_No etc. etc.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:29
Joined
May 7, 2009
Messages
19,169
Whenever you have Reserved word as Fieldname/controlname,
make sure to Qualify it, ie Me.Date, TableName.Date therefore
it is an explicit directive that the Date there is not the Date function
but rather a member of the Qualifier.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:29
Joined
May 7, 2009
Messages
19,169
the problem with IsDate() is it does not test if the expression
inside is a Valid date?
what it does is Evaluate if the expression can result to a Valid
date.

but i would go with your formula, since nobody in their right
mind would ever enter "3a1-2-3" in the textbox.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 03:29
Joined
Apr 27, 2015
Messages
6,286
Further to David's suggestion, change the field name to DateCompleted, and do away with your completed field.
The DateCompleted tells you two things, is it completed and when, your Completed field is redundant and needs maintaining based on changes to the DateCompleted.

Generally, a Date field is much more valuable from a data perspective than a Boolean Yes or No.
ReviewedDate vs ReviewedYes_No etc. etc.
Agree completely. It is good to learn the correct syntax in a case like this, but in the grand scheme of things, Minty's suggestion is the best advice given on this issue.
 

Valient

Member
Local time
Today, 10:29
Joined
Jun 21, 2021
Messages
48
Hello Everyone,

The codes provided are working fine.
I'm still new in access, your suggestions helps a lot
 

Users who are viewing this thread

Top Bottom