Display previous and next

[Mark S]

Is there a way to have a query that meets certain criteria also display the
previous and next record that don't meet the criteria?

Let me give a hypothetical example to explain this. Let's say I have a
table of client names, address, etc. that is in no specific order. I use a
query to sort the data by State, City, Address. I then set a criteria to
only show the records that don't have a zip code.

Now if I could see the record previous to and just after the record that
doesn't have a zip code (let's assume these other records do have a zip
code), I might be able to see what the zip code should be because the
addresses are on the same street. I might also see that the record with the
missing zip code is totally unrelated to the previous and next record and I
will have to go elsewhere to look it up.

Again, this is hypothetical so please don't tell me I should always look up
a zip code. Also, I will need to be able to type in the new code.

 

[KARL DEWEY]

Try this ---
SELECT MarkS.Addr, MarkS.City, MarkS.State, MarkS.Zip, MarkS_1.Addr
FROM MarkS INNER JOIN MarkS AS MarkS_1 ON (MarkS.State = MarkS_1.State) AND
(MarkS.City = MarkS_1.City)
WHERE (((MarkS_1.Zip) Is Null));