What is the correct syntax to validate if a date has been duplicated in a table? I know I can't set the index as no duplicates as the date is also seen as a time. I only care if the date is repeated, not the time.
Validating dates
- Thread starter nick1408
- Start date
Cronk
Registered User.
- Local time
- Tomorrow, 06:34
- Joined
- Jul 4, 2013
- Messages
- 2,776
A Date/Time field in a table stores both the date and time. If you cannot set a unique index (ie duplicates is No) then there is at least one occurrence where two records have the same date and time. Note that if a date is entered without a time, it is deemed by Access to have the time as 12 midnight.
So if I set duplicates to No and only allow the user to enter a date and not a time I should be fine then.
Thanks!
Cronk
Registered User.
- Local time
- Tomorrow, 06:34
- Joined
- Jul 4, 2013
- Messages
- 2,776
No. If the table contains a date 11/29/2016 it is stored as 11/29/2016 00:00 whether it displays the time or not. If you try to enter a second record 11/29/2016 it will be have the the same value and the unique record restriction will stop the record being saved.
You could however store 11/29/2016 00:01 and there would be no duplication violation.
