Go Premium for a chance to win a PS4. Enter to Win


dlookup and checking dates works unexpectedly

Posted on 2010-11-13
Medium Priority
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
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


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 ctl.name 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 1000 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 ctl.name 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Starting up a Project

916 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