Solved

dlookup and checking dates works unexpectedly

Posted on 2010-11-13
15
527 Views
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?
0
Comment
Question by:ghaphis
  • 6
  • 3
  • 2
  • +3
15 Comments
 
LVL 39

Expert Comment

by:als315
Comment Utility
You can add to table combined index with IPID and immunization date and set it as unique.
0
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
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..
0
 

Author Comment

by:ghaphis
Comment Utility
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
0
 

Author Comment

by:ghaphis
Comment Utility
@Peter57r - yes, cruft from previous naming choices. It's bizarre naming, but the table is "Immunization log form" and the form is "Immunization log"
0
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
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")  & "#"
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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) & "#"


0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
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?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Expert Comment

by:jadamski
Comment Utility
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!
0
 

Author Comment

by:ghaphis
Comment Utility
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.

 eedb.mdb
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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.
0
 

Author Comment

by:ghaphis
Comment Utility
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.
0
 

Author Comment

by:ghaphis
Comment Utility
also, as a side note, it seems that dresult is NULL in those instances...
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 250 total points
Comment Utility
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
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
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 ?
0
 

Author Closing Comment

by:ghaphis
Comment Utility
Worked and appreciated the follow-up comments on checking out the code provided.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This is about my first experience with programming Arduino.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now