Previous and Next from Criteria

[Mark S]

I am not sure what to even look for here so I wasn't able to do any real
searches to see if this has been answered.

Ok, this isn't exactly what I am doing but is the best way I can think of to
explain it.

Start with a table of address (Name, Street, City, State, etc) and use a
query to sort it to State, City, Street. This should group address on the
same street together. Then add a criteria for only addresses that are
missing the Zip Code.

I would like to be able to see not only the entries that are missing the Zip
Code, but also the one immediately before and after that entry based on the
sort criteria stated above. From this I could make a best guess at what the
Zip Code should be.

Like I said, this isn't exactly what I am trying to do. I am actually
working with an Event Code and Event Description. From previous entries I
know what the description should be for a new code. Doing this at the time
of entry is not possible. Currently I have to look for missing entries in
the entire table.

Any suggestions?

 

[Jeanette Cunningham]

Make 2 queries.
A query showing the zip code/s for each city will serve as your reference.

In the original query use a criteria of Is Null to get the State and City
for address without zip code.

Now do a DLookup to the city with zip code query to get the zip code for the
city that matches the city of the record with missing zip code.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Mark S]

The use of Zip Code in the explanation was to try to make it a little more
understandable. I am actually using an Event Code that is extracted monthly
from an Excel table from another party. When a new Event Code is used, I
have to take it and manually add 3 other pieces of information. If I can see
a previous code (ie. AB3) and the other fields, I can usually interpret what
a new code means (ie. ABZ). In this case, the ABx would represent something
significant and I can enter the data from that. But it is not something that
can be done with lookups. I normally have to look at a previous entry or if
there is none I go to another source to decode it.

While I might be able to build some lookups that are partially effective, I
could not trust it to be 100% accurate. The example I gave above is again an
oversimplification. There are essentially several different formats that the
Event Code field could be in, so a lookup based on the first two character
would not work.

Thanks for you input though.

 

[Jeanette Cunningham]

The difficulty is in knowing which record is a previous code.
How do you manually find the previous code in the table?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia