Date validation rule ms access

[rudolfsun]

Hi

I need to make my date fiel only allow the format to be YYYY-MM-DD HH:MM:SS
I also need the date field to be a text value

I have set the input mask to do this, but it still doesn't stop values to be
populated incorrectly from a query ie 2008-01-01 :23:59:9

I know I can do this by changing the field to date/time, but I need the
field to remain in text

Please help

 

[John W. Vinson]

Hi

I need to make my date fiel only allow the format to be YYYY-MM-DD HH:MM:SS

I also need the date field to be a text value

I have set the input mask to do this, but it still doesn't stop values to be
populated incorrectly from a query ie 2008-01-01 :23:59:9

I know I can do this by changing the field to date/time, but I need the
field to remain in text

Please help

Please explain why the field needs to be in text. If you store it in a
Date/Time field you can use that format to display it (in the table, in
queries, or in the format property of a textbox on a form or report); it will
let you use date ranges or date functions to search it - which you cannot
easily do with a text datatype.

What's the logic???? And what's your input mask -

0000-00-00 00:00:00;;

should prohibit the value that you displayed.

 

[rudolfsun]

On Tue, 26 Feb 2008 15:21:00 -0800, rudolfsun

Hi Jon

I need it to be a text field because the table holds near on a million
contacts that get updated by our own access tools. These tools work on the
premise that the date field is a text data type. We have evaluated that the
man hours, cost to recode the tools and test them outweighed the need so I
was wondering if there is a way to restrict the field if it is in text.

The input mask is indeed 0000-00-00 00:00:00;; and this does restrict the
manual entry of a date, but it does not restrict it if the date is updated or
appended in a query, it allows an incorrect value to be populated. This is a
problem as changing the date to text will have a knock on effect which could
cause a lot of problems, basically the people who designed this database were
novices and didn't think of this at the time and it really p's me off!!


Hope some one can helo me out thanks

 

[John W. Vinson]

Hi Jon

I need it to be a text field because the table holds near on a million
contacts that get updated by our own access tools. These tools work on the
premise that the date field is a text data type. We have evaluated that the
man hours, cost to recode the tools and test them outweighed the need so I
was wondering if there is a way to restrict the field if it is in text.
Ouch!!!

The input mask is indeed 0000-00-00 00:00:00;; and this does restrict the
manual entry of a date, but it does not restrict it if the date is updated or
appended in a query, it allows an incorrect value to be populated. This is a
problem as changing the date to text will have a knock on effect which could
cause a lot of problems, basically the people who designed this database were
novices and didn't think of this at the time and it really p's me off!!

Well, in that case, all I can suggest is "fix the query". I suppose you could
put a Table Validation rule that would prevent addition of an invalid date but
it would be a monstrously complex rule to catch all the permutations (e.g.
"2008-02-27 10:52:46" is valid, but "2008-02-30 26:85:52" isn't.

Where do the queries originate, and why are they producing junk?

 

[rudolfsun]

Well, in that case, all I can suggest is "fix the query". I suppose you could
put a Table Validation rule that would prevent addition of an invalid date but
it would be a monstrously complex rule to catch all the permutations (e.g.
"2008-02-27 10:52:46" is valid, but "2008-02-30 26:85:52" isn't.

Where do the queries originate, and why are they producing junk?

 

[John W. Vinson]

On Thu, 28 Feb 2008 00:18:00 -0800, rudolfsun


<quote>
Maybe some backgorund would help with this. Once a fortnihgt we recieve data
collecte from siebel, webleads sales force etc. This data gets fed into our
clients database and then any exisitng contacts get an update. The dates in
these files are fine but th client may want an additional update done to a
handful of records that do not appear in the data that has arrived. this means
we manually update these records, changing their date to reflect when the
update took place. As this is manual their is a greater chance of human error.
I did have a thought though, could a form be created where you can select the
fields to update and then the form would only allow you to enter a date in a
certain format?
</quote>

That sounds like a good plan. Create a Form based on the table, with a textbox
on the form. An Input Mask might not be sufficient - perhaps you should also
have code like this in the textbox's BeforeUpdate event:

Private Sub txtDatefield_BeforeUpdate(Cancel as Integer)
If Not IsDate(Me!txtDatefield) Then
Msgbox "Please enter a valid date and time", vbOKOnly
Cancel = True
End If
End Sub

 

[rudolfsun]

On Thu, 28 Feb 2008 00:18:00 -0800, rudolfsun


<quote>
Maybe some backgorund would help with this. Once a fortnihgt we recieve data
collecte from siebel, webleads sales force etc. This data gets fed into our
clients database and then any exisitng contacts get an update. The dates in
these files are fine but th client may want an additional update done to a
handful of records that do not appear in the data that has arrived. this means
we manually update these records, changing their date to reflect when the
update took place. As this is manual their is a greater chance of human error.
I did have a thought though, could a form be created where you can select the
fields to update and then the form would only allow you to enter a date in a
certain format?
</quote>

That sounds like a good plan. Create a Form based on the table, with a textbox
on the form. An Input Mask might not be sufficient - perhaps you should also
have code like this in the textbox's BeforeUpdate event:

Private Sub txtDatefield_BeforeUpdate(Cancel as Integer)
If Not IsDate(Me!txtDatefield) Then
Msgbox "Please enter a valid date and time", vbOKOnly
Cancel = True
End If
End Sub

Ill give this a go

Thanks for your help John ill let you know how it goes