Getting #Error in Text Box

[Guest]

I followed the directions in this Microsoft article:
http://office.microsoft.com/en-us/assistance/HA010550721033.aspx
to create one text box (except that I am using a combo box with a calendar
control) that I enter a date in and one text box that calculates a date 6
months in the past.

The Control Source of txtPastDate is:
=DateSerial(Year([cboMetricDate]),Month([cboMetricDate])-6,Day([cboMetricDate]))

When I first open the form, txtPastDate has "#Error" in it. After I enter a
date in cboMetricDate, txtPastDate has the correct date. But other users
will be using this form to generate metrics and I don't want #Error showing
up when they first open the form.

I have tried hiding the txtPastDate text box until cboMetricDate is
populated with no success (couldn't find the right place to make the text box
visible).

If someone has experience with this, I would sure appreciate your suggestions.
Thank you,
Judy

 

[Duncan Bachen]

I followed the directions in this Microsoft article:
http://office.microsoft.com/en-us/assistance/HA010550721033.aspx
to create one text box (except that I am using a combo box with a calendar
control) that I enter a date in and one text box that calculates a date 6
months in the past.

The Control Source of txtPastDate is:
=DateSerial(Year([cboMetricDate]),Month([cboMetricDate])-6,Day([cboMetricDate]))

When I first open the form, txtPastDate has "#Error" in it. After I enter a
date in cboMetricDate, txtPastDate has the correct date. But other users
will be using this form to generate metrics and I don't want #Error showing
up when they first open the form.

I have tried hiding the txtPastDate text box until cboMetricDate is
populated with no success (couldn't find the right place to make the text box
visible).

If someone has experience with this, I would sure appreciate your suggestions.
Thank you,
Judy

It's displaying #Error because its trying to do a calculation using data
that doesn't exist.. hence why it works after you select a date in
cboMetricDate.

If you can't populate cboMetricDate at startup with a value from a
record, or setting its default value to Date() so it uses today's date,
you're going to have to hide the text.

Use the Form's Current event to do this.

If IsNull(cboMetricDate) Then
txtPastDate.Visible = False
Else
txtPastDate.Visible = True
End If

You will also need to include this code in the AfterUpdate event of the
cboMetricDate. This way it will become visible after a date is chosen.