Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query question (A2002)

    I have a field in a table that will either be null or have a single value. I would like to update the nulls to the single value. I tried the update query below. Note: the table is the result of a make table query so the name of the table is "qryHours1"

    UPDATE qryHours1 SET qryHours1.ClientOrgID = [qryHours1]![ClientOrgID]>0
    WHERE (((qryHours1.ClientOrgID) Is Null));

    How do I make this work?

    Elizabeth

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

    Re: query question (A2002)

    Do this in two steps:

    1. Create a query based on the table that returns the single value. This must be an updateable query, otherwise the update query won't work. For example:

    SELECT TOP 1 ClientOrgID
    FROM qryHours1
    WHERE ClientOrgID Is Not Null

    Save this query as qrySingleValue.

    2. Create a query based on the table and on qrySingleValue. Make it into an update query, add the ClientOrgID field from the table, set the criteria to Is Null, and the update value to qrySingle.ClientOrgID:

    UPDATE qryHours1, qrySingleValue SET qryHours1.ClientOrgID = [qrySingleValue].[ClientOrgID ]
    WHERE qryHours1.Hop Is Null

Posting Permissions

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