Validate Date Field

[Gwen H]

I have a table with a date field, which has an input mask of

99/99/0000;;_

I know from reading other posts in the discussion groups that even with the
input mask, the field will also store the time. Is there some way to ensure
the time isn't stored and/or entered - a validation rule perhaps?

I'm asking because I have a form from which users print reports for a
specific date. This form lists all the dates in the table. One particular
date might have multiple entries in the table, but it shows up only once on
the form. Users choose the date from a list box, and then print the report.
When the date gets stored in the table along with the time, then a particular
date will show up more than once. Of course, I can go back and manually fix
the dates in the table, but I would like to avoid this because I keep
forgetting to do it!

Thanks,
GwenH

 

[Brendan Reynolds]

I haven't extensively tested this, but off-hand I think the following
validation rule should do it, at least for the range of valid dates you'd
expect to find in most business applications ...

=Int([TestDate])

.... where 'TestDate' is the name of your Date/Time field.

Or, perhaps safer ...

TimeSerial(Hour([TestDate]),Minute([TestDate]),Second([TestDate]))=0

The above would need to be a table-level rather than a field-level
validation rule.

 

[Rick Brandt]

I have a table with a date field, which has an input mask of

99/99/0000;;_

I know from reading other posts in the discussion groups that even
with the input mask, the field will also store the time. [snip]

Incorrect. If you don't enter a time *a* time is still stored, but it is
midnight, not the current time. For all intents and purposes in an Access
app a time of midnight is the same as "no time".

 

[Gwen H]

Found the answer to my question! If I enter the validation rule

=Format([Meeting_Date],"mm/dd/yyyy")

for the field in question, it will not allow users to enter the date with
the time. This same field had a default value of Now(), which was the main
culprit for the time being entered along with the date. I fixed this by
changing the default value to:

=Format(Now(),"mm/dd/yyyy")

Just FYI - Thanks for your help!

GwenH

I haven't extensively tested this, but off-hand I think the following
validation rule should do it, at least for the range of valid dates you'd
expect to find in most business applications ...

=Int([TestDate])

.... where 'TestDate' is the name of your Date/Time field.

Or, perhaps safer ...

TimeSerial(Hour([TestDate]),Minute([TestDate]),Second([TestDate]))=0

The above would need to be a table-level rather than a field-level
validation rule.

--
Brendan Reynolds

I have a table with a date field, which has an input mask of

99/99/0000;;_

I know from reading other posts in the discussion groups that even with
the
input mask, the field will also store the time. Is there some way to
ensure
the time isn't stored and/or entered - a validation rule perhaps?

I'm asking because I have a form from which users print reports for a
specific date. This form lists all the dates in the table. One particular
date might have multiple entries in the table, but it shows up only once
on
the form. Users choose the date from a list box, and then print the
report.
When the date gets stored in the table along with the time, then a
particular
date will show up more than once. Of course, I can go back and manually
fix
the dates in the table, but I would like to avoid this because I keep
forgetting to do it!

Thanks,
GwenH

 

[peregenem]

=Format([Meeting_Date],"mm/dd/yyyy")

To ensure the minimum and maximum time values are used for start_date
and end_date respectively based on the granularity of the product's
TIMESTAMP data type, which for practical purposes with Access/Jet's
DATETIME type is 1 second, I use the following:

CONSTRAINT
start_date_min_time
CHECK (
HOUR(start_date) = 0
AND MINUTE(start_date) = 0
AND SECOND(start_date) = 0
)

CONSTRAINT
end_date_max_time
CHECK (
HOUR(end_date) = 23
AND MINUTE(end_date) = 59
AND SECOND(end_date) = 59
)

Constraining dates in this way makes constructs such as

BETWEEN start_date AND end_date

more intuitive.

This same field had a default value of Now(), which was the main
culprit for the time being entered along with the date. I fixed this by
changing the default value to:

=Format(Now(),"mm/dd/yyyy")

You could change your default to use DATE() in place of NOW().

 

[John Vinson]

for the field in question, it will not allow users to enter the date with
the time. This same field had a default value of Now(), which was the main
culprit for the time being entered along with the date. I fixed this by
changing the default value to:

=Format(Now(),"mm/dd/yyyy")

Simpler and faster: change the Default Value to

=Date()

John W. Vinson[MVP]