Update a Date Text Box with Code

[brfender]

Hi. I am using Access 2003.

I have a text box that is populated by the user with a date. After the date
is updated, another date in a different text box is calculated based on a
number of select statements. The calculations work fine. However, when a
date is removed from the user's text box, I would like the calculated text
box to empty. I can't seem to get it to empty. There are no errors; the old
calculated date remains.

Here is a sample of the code I am using:


Private Sub txtActualIntro_AfterUpdate()

If Me.txtStart = "" Then

Me.txtPlanComplete = ""

ElseIf Me.txtStart <> "" Then

'Select Case statements choose the calculation _
at this point if a date is present in txtStart _
(Too long to list, but they work correctly)

End If

End Sub

Again there are no errors. I just want txtPlanComplete to be blank if
txtStart is removed.

Thanks for the help.

 

[brfender]

Private Sub txtActualIntro_AfterUpdate() should actually be

Private Sub txtStart_AfterUpdate()

Sorry for the confusion.

 

[Klatuu]

The default value, unless otherwise specified, is Null; however, we can't
always depend on it being null if a user enters the control and does anything
to change the value. So, we have to check for what else it might be:

If Len(Trim(Nz(Me.txtStart, ""))) = 0 Then
Me.txtPlanComplete = Null
Else 'The ElseIf is not necessary here
'Select Case statements choose the calculation _
at this point if a date is present in txtStart _
(Too long to list, but they work correctly)

End If

 

[Jeff L]

Actually when you delete the date from the text box, the value is Null
and not "". So change your IF statement a little bit to :

If Me.txtStart = "" Or IsNull(Me.txtStart) Then

Also in your ELSE you don't need the If Me.txtStart <> "" Then because
it is the opposite of the IF statement and will execute when the If
Statement is False. Just some coding advise there.

 

[HiTechCoach via AccessMonster.com]

Normally you should not be storing any data that is calculated or depending
on other data in the table. You just calculate it as needed. If you follow
this rule of Data Normalization, you would not have this issue.


To fix your code tTry:

If IsNull( Me.txtStart ) or Not IsDate( Me.txtStart ) Then

Me.txtPlanComplete = Null

Else ' ** No ned to check it again .
If Me.txtStart <> "" then

'Select Case statements choose the calculation _
at this point if a date is present in txtStart _
(Too long to list, but they work correctly)

End If

 

[brfender]

Klatuu,
Thank you. This bit of information helped and it all works correctly.