Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Location
    Guelph, Canada
    Posts
    39

    Unanswered: Query or VB code?

    I have the following Update query that updates field 'Other' in
    Detentions table to true if it meets the criteria set out below.

    UPDATE StudentCheckQuery2 INNER JOIN Detentions ON
    StudentCheckQuery2.StudentID = Detentions.StudentID SET
    Detentions.Other = True
    WHERE (((StudentCheckQuery2.Keep)>"0") AND
    ((Detentions.Detentions)<>[Served]) AND
    ((Detentions.Suspended)<>True));

    I need to make one key change now to the query. I need to update all
    of the 'Other' fields to true except the last number of 'Keep' records.
    So for example, if the value of Keep is 3, then update all of the
    'Other' fields in the selected records except for the last three.

    I hope this can be done with this same Update query.
    I'd really appreciate some help in getting this to work

    TIA Eric

  2. #2
    Join Date
    Apr 2003
    Location
    Australia
    Posts
    20
    Try this:

    UPDATE StudentCheckQuery2, Detentions SET [Detentions].[Other] = True
    WHERE (((IIf([StudentCheckQuery2].[Keep]>0 And [StudentCheckQuery2].[Keep]<Max([StudentCheckQuery2].[Keep]),True,False))=True) AND (([Detentions].[Detentions])<>[Served]) AND (([Detentions].[Suspended])<>True));

    Dobs

Posting Permissions

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