Updating the first row which is null

[recif20002002]

Hello

I am trying to update table1 and set a new value with a condition
selecting only the results for the element with the specified ID and
the first one with the Date field null.
The query executes but it doesn t not update the informations. I ve
tried using TOP 1 and spent hours on this query but i can t get it to
work.

UPDATE table1 SET table1.Name = newName
WHERE table1.Date = (SELECT First(table1.Date) FROM table1 WHERE
table1.Date = Null And table1.ID = specifiedID);;

thank you for your help

Updating the first row which is null

[tina]

you can use the TOP predicate in a query (and presumably in a subquery,
though i've never tried it), if you also include an ORDER BY clause. the
records have to be in a specified order, so that Access can figure out which
record is the TOP 1.

hth
<re***********@yahoo.fr> wrote in message
news:11*********************@e56g2000cwe.googlegro ups.com...

Hello

I am trying to update table1 and set a new value with a condition
selecting only the results for the element with the specified ID and
the first one with the Date field null.
The query executes but it doesn t not update the informations. I ve
tried using TOP 1 and spent hours on this query but i can t get it to
work.

UPDATE table1 SET table1.Name = newName
WHERE table1.Date = (SELECT First(table1.Date) FROM table1 WHERE
table1.Date = Null And table1.ID = specifiedID);;

thank you for your help

Updating the first row which is null

[recif20002002]

I tried this but I get an error with this one

UPDATE table1 SET table1.Name = setName
WHERE table1.Date = (SELECT TOP 1 table1.Date FROM table1
WHERE isNull([table1.Date]) = true And table1.ID = specifiedID
ORDER BY DateValue(table1.Date) ASC);

I also tried this but it doesn t update any row

UPDATE table1 SET table1.Name = setName
WHERE table1.ID = specifiedID And table1.Date = (SELECT TOP 1
table1.Date FROM table1
WHERE isNull([table1.Date]) = true);

Any ones has got an idea?

[Bob Quintal]

"re***********@yahoo.fr" <re***********@yahoo.fr> wrote in
news:11*********************@e56g2000cwe.googlegro ups.com:

Hello

I am trying to update table1 and set a new value with a
condition selecting only the results for the element with the
specified ID and the first one with the Date field null.
The query executes but it doesn t not update the informations.
I ve tried using TOP 1 and spent hours on this query but i can
t get it to work.

UPDATE table1 SET table1.Name = newName
WHERE table1.Date = (SELECT First(table1.Date) FROM table1
WHERE table1.Date = Null And table1.ID = specifiedID);;

thank you for your help

Updating the first row which is null

If table1.date is null, then WHERE table1.Date = (SELECT ....
will never be true, and will not return any recoords.

try
SELECT TOP 1 Date FROM Table1
WHERE table1.Date = Null
AND table1.ID = specifiedID
;
as a query, and use that query in the update

UPDATE table1
SET table1.Name = newName
FROM query1
;

When the two queries work correctly, you can optionally merge
them to get something like

UPDATE table1
SET table1.Name = newName
FROM (SELECT TOP 1 Date FROM Table1
WHERE table1.Date = Null
AND table1.ID = specifiedID
)
;

--
Bob Quintal

PA is y I've altered my email address.

[Bob Quintal]

Bob Quintal <rq******@sympatico.ca> wrote in
news:Xn**********************@207.35.177.135:
See my fix, I had an = instead of an IS, which will always be
false.

"re***********@yahoo.fr" <re***********@yahoo.fr> wrote in
news:11*********************@e56g2000cwe.googlegro ups.com:

Hello

I am trying to update table1 and set a new value with a
condition selecting only the results for the element with the
specified ID and the first one with the Date field null.
The query executes but it doesn t not update the
informations. I ve tried using TOP 1 and spent hours on this
query but i can t get it to work.

UPDATE table1 SET table1.Name = newName
WHERE table1.Date = (SELECT First(table1.Date) FROM table1
WHERE table1.Date = Null And table1.ID = specifiedID);;

thank you for your help

Updating the first row which is null

If table1.date is null, then WHERE table1.Date = (SELECT ....
will never be true, and will not return any recoords.

try

SELECT TOP 1 Date FROM Table1
WHERE table1.Date IS Null
AND table1.ID = specifiedID
;

as a query, and use that query in the update

UPDATE table1
SET table1.Name = newName
FROM query1
;

When the two queries work correctly, you can optionally merge
them to get something like

UPDATE table1
SET table1.Name = newName
FROM (SELECT TOP 1 Date FROM Table1
WHERE table1.Date IS Null
AND table1.ID = specifiedID
)
;


--
Bob Quintal

PA is y I've altered my email address.

[John Welch]

In your (Select top 1) query, your WHERE clause is limiting your results to
only those records where there is no date.
In other words, you're looking through dateless records to find the most
recent one. It's impossible.

When you say "the first one with the Date field null", you're going to have
to find some other way to determine the "first one" since you obviously
can't use the date field to do that.

<re***********@yahoo.fr> wrote in message
news:11*********************@e56g2000cwe.googlegro ups.com...

Hello

I am trying to update table1 and set a new value with a condition
selecting only the results for the element with the specified ID and
the first one with the Date field null.
The query executes but it doesn t not update the informations. I ve
tried using TOP 1 and spent hours on this query but i can t get it to
work.

UPDATE table1 SET table1.Name = newName
WHERE table1.Date = (SELECT First(table1.Date) FROM table1 WHERE
table1.Date = Null And table1.ID = specifiedID);;

thank you for your help

Updating the first row which is null

[recif20002002]

Hey guys thx a lot for yur help
I manage to get it working starting from the subquery which was
incorrect

UPDATE table1 SET table1.Name = setName
WHERE table1.Code = (SELECT TOP 1 table1.Barcode AS Code FROM table1
WHERE table1.ID=specifiedID And table1.Date IS NULL ORDER BY
table1.Code;);