Link to home
Start Free TrialLog in
Avatar of ghaphis

asked on

dlookup and checking dates works unexpectedly

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

Open in new window

Any ideas why this may be happening?
Avatar of als315
Flag of Russian Federation image

You can add to table combined index with IPID and immunization date and set it as unique.
I think I'm confused by the code.

The code  says that you have a table called 'Immunization Log Form'  and a form called 'Immunization log'.
This sounds either wrong or quite bizarre.
Can you clarify..
Avatar of ghaphis


That doesn't affect the issue of different combinations of data - the table structure for this particular part is:

ipid      text
rounddate      date/time
bcgdate      date/time
dptdate      date/time
opvdate      date/time
mmrdate      date/time
mmrboosterdate      date/time

Here's a screenshot of what data could look like:
 User generated image
Avatar of ghaphis


@Peter57r - yes, cruft from previous naming choices. It's bizarre naming, but the table is "Immunization log form" and the form is "Immunization log"
Looking at your form image - so despite where you are and your US spelling you are using UK date format , not US date format???

In that case you need..
& ctl.Name & " = #" & format(ctl, "yyyy-mm-dd")  & "#"
to make your ctl text entry into a date you could type convert it. That should add leading zeros

dcriteria = "[Immunization Log Form]!IPID = '" & Forms![Immunization log].IPID & "' AND [Immunization Log Form]!" & ctl.Name & " = #" & cdate(ctl.value) & "#"

You say you do not get the desired effect if the day of the date is less than 10.  Then you show the date 03/10/2010 which in the US is 10 March 2010.  Are you using a dd/mm/yyyy format?
I agree with GRayL....

The solution to this problem could be as simple as enforcing your date format mm/dd/yyyy before you process this code. I would look in two places. On the form itself I would make sure the property box on the form includes a date mask enforcing the full 8 digit format. Then I would add code prior to the loop so that if there are less than 8 digits (or 10 if you include slashes) then you check for the day format and pad with a 0 if needed.

This type of error is also the reason that many high level databases store dates in numerical (Julian format) then add conversion utilities to the main body of their code!
Avatar of ghaphis


Still tearing out my hair - I tried using Format() to ensure identical comparison formats, but still no luck. I attached a very skeleton database here in the hopes that others can try and see what I am doing that's not working.

To try it out - open up the "Child Lookup" form, select villageid 02 and enter record 001, then hit the search button. Then click on the enter immunization data button and try entering a bcg date that is identical to the one above (03/01/2010).

For what *should* be the correct warning, try entering DPT date of 10/10/2009.

Looking at your db, the default mask in the table design is set to dd/mm/yyyy

Change your dcriteria line to check for a date like this

    dcriteria = "IPID = '" & Me.IPID & "' AND CDate(" & ctl.Name & ") = '" & ctl & "'"

or this

    dcriteria = "IPID = '" & Me.IPID & "' AND CDate(" & ctl.Name & ") = '" & CDate(ctl) & "'"

or perhaps this

    dcriteria = "IPID = '" & Me.IPID & "' AND Format(" & ctl.Name & ",'Short Date') = '" & CDate(ctl) & "'"

or even this

    dcriteria = "IPID = '" & Me.IPID & "' AND Format(" & ctl.Name & ",'Short Date') = '" & Format(ctl, "short date") & "'"

And see if that makes a difference

As you are querying the one table and your code is getting data from that form, there is no need to specify the full form path.
Avatar of ghaphis


rockiroads, that doesn't seem right, as isn't a date - it's just a variable name - and then ctl isn't getting formatted at all. I think I want something that looks like

"IPID = '02001' AND bcgdate=#03/01/2010#"

Did you mean:

  dcriteria = "IPID = '" & Me.IPID & "' AND " & ctl.Name & " = #" & CDate(ctl) & "#"

  dcriteria = "IPID = '" & Me.IPID & "' AND " & ctl.Name & " = #" & Format(ctl, "Short Date") & "#"

and the like?

Regardless, even with those changes, I tried it and still no luck.
Avatar of ghaphis


also, as a side note, it seems that dresult is NULL in those instances...
Avatar of rockiroads
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Please advise what you have as a setting for the date format in the Regional settings of the Control Panel - mm/dd/yyyy or dd/mm/yyyyy ?
Avatar of ghaphis


Worked and appreciated the follow-up comments on checking out the code provided.