Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Issue with colours (2000)

    Hi,
    I have this query:

    SELECT TblMain.URN, TblMain.DefSurname, TblMain.DefForename, TblHearing.CourtDate, TblDirections.CourtDirection, TblDirections.ConfirmedCourtCPO, TblDirections.DueDate, TblArea.AreaName, TblColours.Colour, 1-(Date()-[DueDate]>4)-(Date()-[DueDate]>9)-(Date()-[DueDate]>19) AS Colr, TblCPO.[CPO Surname], TblCPO.[CPO Forename]
    FROM TblColours, (TblLawyer INNER JOIN (TblCPO INNER JOIN (TblArea INNER JOIN TblMain ON TblArea.AreaID = TblMain.AreaID) ON TblCPO.CPOID = TblMain.CPO) ON TblLawyer.LawyerId = TblMain.LawyerID) INNER JOIN (TblHearing INNER JOIN TblDirections ON TblHearing.HearingID = TblDirections.HearingID) ON TblMain.URNID = TblHearing.URNID
    WHERE (((TblDirections.ConfirmedCourtCPO) Is Null) AND ((1-(Date()-[DueDate]>4)-(Date()-[DueDate]>9)-(Date()-[DueDate]>19))=[value]))
    ORDER BY 1-(Date()-[DueDate]>4)-(Date()-[DueDate]>9)-(Date()-[DueDate]>19);

    The issue I'm having is that the text in bold returns a value of between 1 and 4 dependant upon how close DueDate is to today.

    However as you can see from the screen shot, it only seems to be returning values of 1, regardless of how close or how far DueDate is to Date().

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Issue with colours (2000)

    What is "value" in ((1-(Date()-[DueDate]>4)-(Date()-[DueDate]>9)-(Date()-[DueDate]>19))=[value]))? A parameter?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Issue with colours (2000)

    This returns to a previous post concerning a query where:
    Colr: 1-(Date()-[DueDate]>4)-(Date()-[DueDate]>9)-(Date()-[DueDate]>19)
    And the criteria is:
    [Value]
    Can't work our why its solely returning values of 1!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Issue with colours (2000)

    When you open the query, it should prompt you to enter Value.
    Do you get a prompt? What do you enter?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Issue with colours (2000)

    I have two almost identical queries, one has further information from another table, but they both have this field/criteria in relation to Colr.
    This query doesn't prompt, the other query does. The other one I enter 1 and this brings up all the records as shown on the screenshot.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Issue with colours (2000)

    If you enter 1 in the parameter prompt, you'll only see records for which Colr is 1 - that is what the parameter is for. If you enter 3, you'll see records for with Colr is 3, etc. If you want to see all records, you should remove the parameter prompt.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Issue with colours (2000)

    That makes sense, however I can't work out why the value of one is being returned as in the previous screenshot. There is a record that has a date in July which is clearly over 19 days, which should in theory return a value of 4 in regards to the Colr. However 1 is returned.....

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Issue with colours (2000)

    As it stands now, the Colr expression tells you how much a record is overdue. Dates in the future are clearly not overdue.
    Try switching Date() and DateDue in the expression:

    1-([DueDate]-Date()>4)-([DueDate]-Date()>9)-([DueDate]-Date()>19);

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Issue with colours (2000)

    Of course..............I previously had a reminder field in this formula as well, hence the error........works a treat.
    Would you mind helping with another query I have?
    The Main form is filtered on a OpenArg where the user selects an AreaID from a combobox in a previous form. This is then carried across to a field [Area] in the Main Table so that a record of what has been selected is recorded for all subsequent new entries and existing ones. The form then only displays the records relating to that area.
    This field [Area] is also in the query that I've been quoting in this post. However I'd also like to filter [Area] in the query based on the OpenArgs.
    I can't work out how to do it..........

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Query Issue with colours (2000)

    One way to do this is to have a hidden textbox on the form. When you open the form, set its value to the Openargs value, then use the textbox as a query parameter.
    Regards
    John



  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Issue with colours (2000)

    I assume that you have a text box (or combo box) bound to the Area field on your form. You can set the criteria for the Area field in the query to

    [Forms]![NameOfTheForm]![NameOfTheTextBox]

    with the appropriate names substituted. Of course, this will only work if the form is open.

  12. #12
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Issue with colours (2000)

    Thanks again Hans, appreciated!

Posting Permissions

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