Solved

dlookup and checking dates works unexpectedly

Posted on 2010-11-13
15
542 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
[X]
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
15 Comments
 
LVL 40

Expert Comment

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

Expert Comment

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

Author Comment

by:ghaphis
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ghaphis
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"
0
 
LVL 77

Expert Comment

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

Expert Comment

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


0
 
LVL 44

Expert Comment

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

Expert Comment

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

Author Comment

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

 eedb.mdb
0
 
LVL 65

Expert Comment

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

Author Comment

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

Author Comment

by:ghaphis
ID: 34143305
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
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
0
 
LVL 44

Expert Comment

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

Author Closing Comment

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

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

739 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