Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

dlookup and checking dates works unexpectedly

Avatar of ghaphis
ghaphis asked on
Microsoft AccessProgrammingSQL
15 Comments1 Solution566 ViewsLast Modified:
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 & "#"
    'MsgBox (dcriteria)
    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
        Exit For
    End If

End If
End If
End If
Next ctl

Any ideas why this may be happening?
ASKER CERTIFIED SOLUTION
Avatar of rockiroads
Commented:
This problem has been solved!
Unlock 1 Answer and 15 Comments.
See Answers