Hello All,

Well, I have one of those challenges that definitely can be done, but I can't seem to find the answer.

I am working on a Check Register Report which lists our Checks in Check Number Order. I would like to identify if any Check Numbers are MISSING. (This is pretty similar to a standard Bank Statement that we all receive)

Example:

Check#... Amt

1300 ... 125.00
1301 ... 234.00
1302 ... 127.00
1304 ... 537.00

In the above sequence, Check Number 1303 is MISSING.

Is there a way to highlight/identify the gap where 1303 SHOULD be - OR - Highlight 1304 to identify that a preceeding number was missed?

Is there a way to calculate a field in a report and compare it to the prior row? Any other ideas?

Thanks for any possible insights,

David

You can base your Report on a Query with SQL something like (***untest***):


I guess it can be done purely in the Report also but this will need some VBA code and probably slightly more complex. Hence my preferred method is to do the processing in the RecordSource Query.

Thanks. I'll give it a try.

I appreciate the help.

David

You're welcome... Hope it work the way you want...

Thanks again. I finally was able to try, and it did work.

I find I tend to focus first on VBA and I seem to forget the power of SQL.

Thanks for the reminder.

David

You're welcome... Glad you worked it out...

Van

I used this code as well. Thanks! However, I am having a problem with leading zeros. This code is giving me the message "Gap" when there is no gap. I believe it's due to the leading zero. Can you help?

Bobbi

AFAIK, the format shouldn't matter provided that your Field values are actually numeric, i.e. your Field data type is AutoNumber, Long, Integer, etc...

Check the data type in the design view of the Table ... If it is Text, the SQL may not work ...

It is a text field. My document number also has alph characters. I used a formula to remove the alpha before using the SQL. I also tried val to convert to a number and that didn't seem to work either. When there is an actual gap #Error is returned.

Please post a copy of your database with your attempted Query / SQL...

Please compact and zip the database before posting...