Is it Date type ?

[Ari via AccessMonster.com]

Hi
I have a text box on a form which has no control source. It is for inputting
the date like e.g. 11/08/2006 which is used for further calculations.
The Input Mask of text box: 99/99/0000;0;_
I want to make some check that numbers entered into the text box by user are
a valid date type. Therefore I tried to put following into the validation
rule:

=IIf(IsDate([txtDateStart].[Value]),[txtdatestart].[Value],0)

But no success. Any idea how to check it ?

I do not want create another table with date field and assign it to the
control source just because of checking the entry in one field.

 

[Jeff Boyce]

Ari

Another approach might be to use the control's BeforeUpdate event and test
for IsDate() there.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Hi
I have a text box on a form which has no control source. It is for inputting
the date like e.g. 11/08/2006 which is used for further calculations.
The Input Mask of text box: 99/99/0000;0;_
I want to make some check that numbers entered into the text box by user are
a valid date type. Therefore I tried to put following into the validation
rule:

=IIf(IsDate([txtDateStart].[Value]),[txtdatestart].[Value],0)

But no success. Any idea how to check it ?

I do not want create another table with date field and assign it to the
control source just because of checking the entry in one field.

 

[Van T. Dinh]

Another way is to enter in the Validation rule of the Control:

IsDate([TextBoxName]) = True

 

[Wayne-I-M]

Hi Ari

Following on from Jeff's post - which is a good idea. You could use the
before update event of your text box like this.


Private Sub TextBoxName_BeforeUpdate(Cancel As Integer)
If (IsDate(TextBoxName)) = False Then
Beep
MsgBox "This is not a date", vbQuestion, "Incorrect date input"
End If
End Sub


Hope this helps


--
Wayne
Manchester, England.
Enjoy whatever it is you do
Scusate,ma il mio Inglese fa schiffo
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.

Ari

Another approach might be to use the control's BeforeUpdate event and test
for IsDate() there.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Hi
I have a text box on a form which has no control source. It is for inputting
the date like e.g. 11/08/2006 which is used for further calculations.
The Input Mask of text box: 99/99/0000;0;_
I want to make some check that numbers entered into the text box by user are
a valid date type. Therefore I tried to put following into the validation
rule:

=IIf(IsDate([txtDateStart].[Value]),[txtdatestart].[Value],0)

But no success. Any idea how to check it ?

I do not want create another table with date field and assign it to the
control source just because of checking the entry in one field.

 

[arista via AccessMonster.com]

Unfortunately the validation rule: IsDate("txtDateStart")=True does not work.
E.G. for 11/14/2006 it is taken as a date which is nonsence (14/11/2006 also
as a date).
Same with Before Update procedure.
I was trying later format function:
If ((IsDate(Format(xTextBox, "dd/mm/yyyy"))) = False) Then ...
but with same result
Any solution ?

 

[Douglas J. Steele]

Formatting isn't going to make a difference.

Unfortunately, Access bends over backwards trying to accept your date as
being valid, so if it doesn't like it as dd/mm/yyyy, it'll try mm/dd/yyyy
(and vice versa).

You might be forced to write your own validation function that substrings
the fields and checks that the day associated with the accepted date
corresponds to the day you expect, the month associated with the accepted
date corresponds to the month you expect and so on.

 

[arista via AccessMonster.com]

I do not like to say it but I gave it up.

This is not the first stupid problem I encouterred here and due to I lost
plenty of time. Shame

 

[Van T. Dinh]

In you want to restrict the supposedly "month" component to less than or
equal to 12, you can use an additional criterion and the Validation rule
becomes:

(IsDate(Forms!Form1.Text0) = True) AND
(CInt(Mid(Forms!Form1.Text0, _
InStr(Forms!Form1.Text0, "/")+1, _
InStrRev(Forms!Form1.Text0,"/") - InStr(Forms!Form1.Text0, "/") - 1)) <=
12)

(A2K or later). This may be too long for the Validation Rule property and
if this is the case, use the Control_BeforeUpdate to test using the same
criteria.

OTOH, this still does not stop the case where the user enters "10/12/2006"
but actually means to enter the value Oct 12 but your system will interpret
as Dec 10 ... That's why I tend to provide a Calendat Control for the use
to enter the data values.

 

[Jeff Boyce]

?Perhaps Access is treating "11/14/2006" as November 14, 2006 (the common US
date formatting)?

What settings for date do you have in your Windows OS?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[arista via AccessMonster.com]

Hi Jeff
it does not matter. See answer from Douglas J. Steele - 11 Dec 2006 16:37 GMT
It plays both ways dd/mm/yyyy and mm/dd/yyyy. When one of it is a date then
the condition IsDate is true. No chance