Update query to assign sequence numbers

[jaaret]

I want to assign a sequence number to each field in a dynaset after a user selects which records to include on a report and then runs the query. The sequence numbers will be used to mark each record on a map.

Sample query:

SELECT tblAddresses.Sequence, tblAddresses.Address, tblAddresses.DateClose, tblAddresses.Include
FROM tblAddresses
WHERE (((tblAddresses.Include)=-1))
ORDER BY tblAddresses.DateClose;

If five records are selected, I want the Sequence field to populate with 1 though 5 sorted by DateClose for the resulting dynaset.

Thanks in advance,
Jaaret

 

[dhookom]

If you really have a good reason to store a calculated value, you can try:

UPDATE tblAddresses
SET Sequence = DCount("*","tblAddresses","DateClose<=#" & DateClose & "# AND Include =-1)
WHERE Include=-1;

Duane
Hook'D on Access
MS Access MVP

 

[jaaret]

How would I implement this? When I paste this into the SQL window of a query it results in a syntax error with this part highlighted:

"# AND Include =-1)
WHERE Include=-1;

 

[dhookom]

You missed my error, there should have been a quote following the first -1:

UPDATE tblAddresses
SET Sequence = DCount("*","tblAddresses","DateClose<=#" & DateClose & "# AND Include =-1[red]"[/red])
WHERE Include=-1;

This is an update query which is what I thought you were asking for.

Duane
Hook'D on Access
MS Access MVP