I am hoping that someone can give me a helping hand on this. For my work I created a program to keep track of all the checks written each day. They input the CheckNumbers in 2 columns, (per their request) one in [ChecksFrom] the other [ChecksTo]. From and To being the Check numbers. All is well but now just been asked to duplicate what banking systems do on your statements. What is needed is when the report is run showing all checks for a certain period, they need to be flagged when a number skips in the sequence. Let's say you have check numbers from 2003 to 2103, however, check# 2045 is missing. Is there a way to alert that a number(s) are missing and possibly show which one(s) it is? If you can help please put in lamens terms as I am still learing VBA.
SELECT T2.CheckNum, IIF((Select Count(*) From Table2 Where Table2.CheckNum<T2.CheckNum)>1,
Iif((Select Top 1 Table2.CheckNum From Table2 Where Table2.CheckNum<T2.CheckNum Order By Table2.CheckNum Desc) = (T2.CheckNum-1),"","#") ,"") AS Marker
FROM Table2 as T2
Order By T2.CheckNum;
Only sad to say it is a Text field as they wanted to be able to input a Duplicate check# with a "V" to show it was voided. I wanted to go with creating a CheckBox or something denoting "Void", but hey, I'm just paid to do things their way as best I can. Now, can your solution be altered to allow for a Text field?
(how does this PrivateMessage thing work.....)
If you didn't have the V possibly (not sure). I will think about it.
You also have an opportunity to go back to you Customer and use this as a teaching example. You can show them that this simple think may not be possible, because they wouldn't listen to you in the first place, and allow you to set up a check box.
They need the developer to help them make a good idea even better (better by meaning, more flexible in the long run). Like you know, you could have easily display the checknumber with a V based on a check box usage.
SELECT T3.CheckNum, IIf((Select Count(*) From Table3 Where Table3.CheckNum<T3.CheckNum)>1,
IIF(Val(T3.CheckNum)>0, IIf((Select Top 1 Val(Table3.CheckNum) From Table3 Where VAl(Table3.CheckNum)<Val(T3.CheckNum) Order By Table3.CheckNum Desc)=(Val(T3.CheckNum)-1),"","#") ,"") ,"") AS Marker
FROM Table3 AS T3
ORDER BY T3.CheckNum;
Incidentally, my customer is also my Employer...hehehe. He knows this but to no good yet. I will create a duplicate database and set the CheckNumbers to Number fields, apply a CheckBox to check if Voided and have it to make that entry gray or something to be visible. Using the query you posted here, where will it show that a check number is missing? Do I have to include a certain field in the Table or Form or Report? And is there a way to guide me to the place where that entry is missing??