dlookup and checking dates works unexpectedly

Posted on 2010-11-13
Last Modified: 2012-05-10
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?
Question by:ghaphis
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +3
LVL 40

Expert Comment

ID: 34126922
You can add to table combined index with IPID and immunization date and set it as unique.
LVL 77

Expert Comment

ID: 34126939
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..

Author Comment

ID: 34126946
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:
 form ss
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.


Author Comment

ID: 34126950
@Peter57r - yes, cruft from previous naming choices. It's bizarre naming, but the table is "Immunization log form" and the form is "Immunization log"
LVL 77

Expert Comment

ID: 34126963
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")  & "#"
LVL 65

Expert Comment

ID: 34127644
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) & "#"

LVL 44

Expert Comment

ID: 34128166
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?

Expert Comment

ID: 34131511
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!

Author Comment

ID: 34135428
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.

LVL 65

Expert Comment

ID: 34142356
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.

Author Comment

ID: 34143237
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.

Author Comment

ID: 34143305
also, as a side note, it seems that dresult is NULL in those instances...
LVL 65

Accepted Solution

rockiroads earned 250 total points
ID: 34146003
I ran it with that and it told me entry already there. I used your db which is why I found out about your datemask.

last one to try

   dcriteria = "IPID = '" & Me.IPID & "' AND Format(" & ctl.Name & ",'YYYYMMDD') = " & Format(ctl, "YYYYMMDD")

if u look at the code carefully, I am not formatting but the name of the variable i.e. format(rounddate or cdate(rounddate
LVL 44

Expert Comment

ID: 34146019
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 ?

Author Closing Comment

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

Featured Post

Setting up LaraDock for Laravel

Learn how to set up LaraDock in a Laravel project - LaraDock gives us an easy way to run a Laravel application using Docker in a single command.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
Part One of the two-part Q&A series with MalwareTech.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Introduction to Processes
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question