Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Oklahoma City, Oklahoma, USA
    Thanked 0 Times in 0 Posts

    Query Problem (A2K)

    I have a linked table with many tables linked to it on the ID field.

    I use the query builder to select the records needed, then when I get the records that I want selected, I convert the query to an Update where one of the selection criteria fields is also the update field.

    When I run the query, I want it to update the dates the physical was done to Date() if the patient (inmate in a count jail) has been in the facility for more than 9 days. I use a DOA (date of arrest) field to select them.

    UPDATE Inmates LEFT JOIN tblPE ON Inmates.ID = tblPE.ID SET tblPE.PE = Date()
    WHERE (((tblPE.PE) Is Null) AND ((Inmates.Cell) Like "0*" Or (Inmates.Cell) Like "1*") AND ((Inmates.DOA)<Date()-9));

    When everything looks good I run the query and it only updates those who have been in the facility for 10 days. I also use the location (Cell) to make sure I do not include those who are housed outside this building.

    I cannot see what I am doing wrong.

    Please help!

    Jail Administrator Medical

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Evergreen, CO, USA
    Thanked 65 Times in 64 Posts

    Re: Query Problem (A2K)

    I don't understand what is going wrong - is it that you are only updating records where inmates have been there for exactly 10 days? Your SQL string appears to be checking for 10 days or more. Is it possible that your cases were restricted to only 10 days for some other reason (such as Inmates.Cell as not equal to either 0 or 1)?

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Melbourne, Australia
    Thanked 32 Times in 32 Posts

    Re: Query Problem (A2K)

    If you run that query each day, all you will get is the inmates who have been there only 10 days.

    The very first time you run the query you will get all inmates who have been there for more than 9 days.
    Then each day you run it, it will only find inmates for the 10th day ago.

    Otherwise I'm not getting what your problem is.

Posting Permissions

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