Please, I need help with date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working in access 2002, I have a data sheet form that has a date field
and a text field called "Unknowndate". If part of the date is unknown such as
month or day or year the user would enter "y" for the unknown value like
yy/05/2005 or 23/yy/2005. I would like to have the user enter all kind of
values(legitimate date and unknown date) in the date field, I understand that
the date field will not accept a text. however, I am trying to do the
following, upon the entery of text and before the error message "value is not
appropriate for this field" I would like to capture that text values put it
in the "Unknowndate" field and delete it from the date field. I have tried so
many variations of placing the code in different events, however, the error
message beat me. Is there a way to accomplish this?
thanks
Al
 
Why not have the user type the entry in your TEXT field, then check it for
validity. If valid, move the entry to the date field. If not a valid date,
leave it where it is.

Then you won't get an error.
 
I would agree that Rick B's approach is the better way to do it; howevr,
every few years, you will run across a user who will make unreasonable
demands. No, I didn't make it up, it really happens :)

Use the Before Update event of the Date field text box to determine whether
the date is a validate date. If it is not, put it in the Unknown text box:

If Not IsDate(Me.txtDateField) Then
Me.txtUnknownDate = Me.txtDateField
Me.DateField = Null
Cancel = True
End If
 
Hi Klatuu,
I agree with you and this is the case, my client wanted so that we can hide
the text field and the user does not get confused and just stick to one field
to enter every thing. The code would work, however, the error message ("The
value you entered isn't valid for this field") always popped before the code
fires. I tried the before update event and did not work. I think we need to
catch it on error message but I am not sure how? thanks
 
I think my code has an error, it should be Cancel = True

That should avoid the error popping up. The error will only pop up if you
try to update the field. If you don't cancel the Before Update, you will get
the error. This is the sort of thing the control's Before Update event is
for.
 
Your code did not have that error, you had cancel = true. it is still
popping. I even tried to test it with keydown if key was Return or tab. I
still got the error message?
Al
 
I will have to do some testing and get back to you. This doesn't seem right.
You are in the Before Update event of the control and not the form, right?
 
Sorry, Al, I can't seem to recreate the problem. It is puzzling, because
this is a very common use of the Before Update event. The only other thing I
can think of is if you have an input mask on that control.
 
no, here is what is happening. when I place this code in the before update
event and then type the wrong data type in the date field, the on error
events of the form fires first and it never gets to the before update. I
place the following code in the form error events:
Const aesErrDataType = 2113
Select Case DataErr
Case aesErrDataType
If Not IsDate(Me.MedCondStartDate) Then
Call Form_BeforeUpdate(0)
Me.MedCondStartDate.Undo
End If
Case Else
Stop
End Select
Response = acDataErrContinue
*****************************
if you noticed that the code calls for the Form_BeforeUpdate which has the
following line of code:

Me.UnknownMedCondDate = Me.MedCondStartDate

which suppose to copy the value over. here is another problem is that since
the value was not committed to the field yet, the field = null and that is
what is copied over to the UnknownMedCondDate field. I think that what I need
to resolve.
any idea?
thanks
Al
 
Klatuu,
I finally figured it out, here is the change I made to the previous code I
just posted and that made it work:

Me.UnknownMedCondDate = Me.MedCondStartDate.Text

Just added ".Text". that took care of coping what was in the date field.
thank for you deligent help.
Al
 
Then, the only thing I can think of to put the data in the correct control
depending on whether it is a valid date or not is to hide both bound
controls. Use an unbound text box to enter the data. In the After Update
event of the unbound text box, put the value in the appropriate hidden
control depending on whether it is a valid date.

If you use this approach, there are two other things you will need to
address. First, to get the value in one of the fields in the unbound text
box, use the form's Current event and put the value of the hidden control
that is not null.

The other is if the user changes the value from an unknown date to a real
date, you will have to null out the unknown date control. That should also
happen in the After Update event of the unbound control.
 
Back
Top