Navigation:  Articles > Sep-1997 >

Tip: Controlling Time

Previous pageReturn to chapter overviewNext page

Stephen Sarre          
 
For a Date/Time field, if you don't use an input mask (and I generally don't to allow greater user flexibility), a user might enter a time of day together with the date. A Format property such as Short Date might prevent the time from displaying, yet the time element will prevent your application from behaving as expected. For instance, this SQL query won't find any records if DateField has a value such as 06/06/97 08:33:12:
 

SELECT tblDate.ID, tblDate.DateField

FROM tblDate

WHERE (tblDate.DateField)=#6/06/97#

 
 
This SQL query will contain more groups than you might want:
 

SELECT tblDate.DateField

FROM tblDate

GROUP BY tblDate.DateField

 
 
You can prevent this sort of data from creeping into your application by setting the Table Field properties, as follows:
 

Validation Rule: CLng([DateField])

Validation Text: Please specify the date to the nearest day

 
 
The rule requires the entered date to match the value returned by feeding the Field to the CLng function. Because dates are stored as numbers with the time as a decimal, the CLng function strips off the decimal portion, effectively removing the time. When the field doesn't match itself with the time component removed, you know that your users have entered a time with their date.