Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2009
    Posts
    30

    Unanswered: DoCmd.OpenForm filter problem

    I received wonderful help through this forum a few months ago and probably couldn't have gotten this on my own. As our Physicians have started using the database, we discovered a design issue and I need just a little bit more help to make it more functional. I have a form where I'm listing patients and when they double click the Medical Record Number [MRN] it takes them to "Hospital Round Form" and they can fill out their information. The problem is, the MRN is linked to that patient, not the visit, in other words, that patient may have been here several times and his MRN will always be the same. Thus my screen comes up and it may be on the wrong visit, the Dr. may not see the arrows at the bottom to arrow to the other visits. I need to add another filter in the Where claus that states if the [Date of Discharge] is not null, then only show that record. Supposedly, only one of the multiple visits will not have a discharge date, all others will have discharge dates. I'm just not understanding the filter or Where clause and all experiments have failed. Something you can explain in 2 minutes could save me hours. As always, thanks in advance for your help. The following is my code.

    DoCmd.OpenForm "Hospital Round Form", , , "[MRN] = """ & Me![MRN] & """"

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    See if this works:

    DoCmd.OpenForm "Hospital Round Form", , , "[MRN] = """ & Me![MRN] & """ AND [Date of Discharge] is not null"

    I would normally do this:

    DoCmd.OpenForm "Hospital Round Form", , , "[MRN] = '" & Me![MRN] & "' AND [Date of Discharge] is not null"
    Paul

  3. #3
    Join Date
    Feb 2009
    Posts
    30
    Fantastic, thanks so much. My mistake was putting NOT null, I really need if it is null, so I just changed that. My other problem was that I was using
    & instead of AND. I'm good to go, very helpful.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Ah; I read the thread and knew what you wanted, but just copy/pasted the example. Sorry.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •