I have checked with work colleagues and am completely puzzled - hope someone can help.
We have fields in our access database with a datetime format for immunization records which are entered using a form. Data (in terms of dates) is in multiple rows for one person (with 11/11/1111 meaning no immunization data), and we want to check to make sure the data wasn't accidentally entered in a previous, saved row before adding a new row. For example, a row could have the following data:
Rounddate Polio immunization date MMR immunization date
03/10/2010 03/10/2010 11/11/1111
if we entered a new row that also included the 03/10/2010 polio immunization date, we would want an error message to appear and a warning to check the row before allowing to continue. The code we have, strangely enough, works for dates only when the first (day) digit is 10 or greater. So, the warning comes up if the date to check is 10/10/2010 or 11/03/2010, but not a date starting with a zero like 03/10/2010.
Here's the code we used. Ipid is our primary key, btw:
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If Right(ctl.Name, 4) = "date" Then
If ctl <> #11/11/1111# Then
dcriteria = "[Immunization Log Form]!IPID = '" & Forms![Immunization log].IPID & "' AND [Immunization Log Form]!" & ctl.Name & " = #" & ctl & "#"
dresult = DLookup(ctl.Name, "Immunization Log Form", dcriteria)
If dresult = ctl.Value Then
MsgBox "This " & ctl.Name & " has already been entered. It cannot be entered two times."
Me(Replace(ctl.Name, "date", "") & "_day").SetFocus
Cancel = True
Any ideas why this may be happening?