Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Question Unanswered: Find missing number in a sequence

    Hi again everyone,

    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.

    Bud

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    Table2
    CheckNum
    2101
    2102
    2103
    2105
    2106
    2107
    2109
    2110


    Query
    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;


    Results
    CheckNum | Marker
    2101 |
    2102 |
    2103 |
    2105 | #
    2106 |
    2107 |
    2109 | #
    2110 |

    This query will only work if the checknum is a NUMERIC Field

    S-

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Sequential check numbers

    Wow, thanks for the fast reply,

    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?

    Bud
    (how does this PrivateMessage thing work.....)

  4. #4
    Join Date
    Nov 2003
    Posts
    267
    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.

    I will get back to you

    S-

  5. #5
    Join Date
    Nov 2003
    Posts
    267
    Got it

    Table
    2101
    2102
    2103
    2105
    2106
    2106V
    2107
    2109
    2110



    Query
    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;

    Results
    CheckNum | Marker
    2101 |
    2102 |
    2103 |
    2105 | #
    2106 |
    2106V |
    2107 |
    2109 | #
    2110 |

  6. #6
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Thanks sbaxter,

    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??

    Bud

  7. #7
    Join Date
    Nov 2003
    Posts
    267
    The second query will do it off a text field,


    The marker will show up in the query only (as a field in the query), where you can access it from the report to display it on the report

    S-

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •