Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'Where' criteria from form (2003 SP2)

    I'm trying to show a set of descending records, from a user specified value. The query has one field criteria from a form text box, but the one that requires a 'less than' (<) doesn't seem to work.

    Here's the SQL;
    SELECT TOP 13 Qry2Sum2.UpdtWeek AS Week, Min(Qry2Sum2.Days) AS [Minimum Days], Round(Avg([Qry2Sum2].[Days]),2) AS [Average days], Max(Qry2Sum2.Days) AS [Maximum days], Round(StDev([Qry2Sum2].[Days]),2) AS [Std Deviation, Days], Count(Qry2Sum2.ID) AS [Number]
    FROM Qry2Sum2
    WHERE (((Qry2Sum2.UpdtWeek)<[forms]![Frm2CompletionSel]![WeekTxt] & "*"))
    GROUP BY Qry2Sum2.UpdtWeek
    HAVING (((Min(Qry2Sum2.UpdtYear)) Like [forms]![Frm2CompletionSel]![YearTxt] & "*"));

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

    Re: 'Where' criteria from form (2003 SP2)

    You cannot include a wildcard * in a condition with <. Wildcards can only be used with Like.

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

    Re: 'Where' criteria from form (2003 SP2)

    Why do you have Like in the HAVING part? Do you really want the minimum value of UpdtYear to start with the text of YearTxt?

  4. #4
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Where' criteria from form (2003 SP2)

    OK got it sorted now. Thanks

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Where' criteria from form (2003 SP2)

    This was a bit weird; originally it was 'group' but just got error meassage about not including specified expression as part of the aggragate function. Using 'Min' worked though.

Posting Permissions

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