Mark gap in sequence 2

[tempclerk]

As they do in bank statements when there is a missing check or two, I would like to mark records where there is a gap in the sequence. I would also like to mark where there is a duplication (because it is probably an error that needs to be looked into.)

This is not a numeric field, but text codes that should end in sequential numbers.

I guess mostly I need to know how to refer to the previous record in a sort, if possible. Other ideas welcome.

 

[MichaelRed]

The dups should be no problem, as Ms. Access has ye olde "find duplicates" query wizzard. finding the "Gaps" will probably require some calculated fields (to seperate the "Sequence" into a seperate field and a self join to relate to the previous 'sequence' and a conditional. Before I would tackel it, I would want to see some more details and sample records.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[tempclerk]

This is the kind of data I have. Within the group the codes should be sequential.

code group date etc
12-345 AB 8/1/2001 ...
12-1578 CD 8/1/2001 ...
12-1576 CD 8/2/2001 ...
12-344 AB 8/2/2001 ...
12-345 AB 8/4/2001 ...
12-341 AB 8/4/2001 ...

Needs to come out:
(group header)
12-341 AB 8/4/2001 ...
* 12-344 AB 8/2/2001 ...
12-345 AB 8/1/2001 ...
+ 12-345 AB 8/4/2001 ...
(group header)
12-1576 CD 8/2/2001 ...
* 12-1578 CD 8/1/2001 ...

* = gap in sequence
+ = duplicate code

Thanks for your help.

 

[tempclerk]

Working on the duplicates part, I found a way to get the report to put #Error where the + signs should be. Close enough that I should be able to get it working, but the method just seems too messy.

Is there NO way to refer to the previous record?

I would appreciate ideas on how to go about this.

 

[MichaelRed]

"Previous" record needs a definition. By the RULES of relational db, Records are NOT ordered. Not even by the entry process, much less by some part of an arbitrary field.

You can set the DISPLAY (and processing) ORDER via the Order By, Group By and ASC / DESC functions. So to do your more-or-less "previous" record, you need the [Group] and (partial)

 fields. set upto determine the Previous/Current records.  This would place the Group records (using the GROUP By) together, while using a calculated field (everthing to the right of the hyphen in hte [Code] field with the ASC predicate would then put htem in order of that partial field.  Joining two queries set up in this manner gets you CLOSE, but you still need to deal with the [Group] breaks (Where [Group] <> [Group_1]).  For [b]ME[/b] the various checks are MORE than I would like to deal with in a query/SQL environment, so I would just fling together a procedure and loop through the set.  Again, for [b]ME[/b] I would add a field to the table for the calculated status (your &quot;+&quot; and &quot;*&quot;) and just add the field to the report.

Then, again your example leaves out some parts.  YOu only show the &quot;+&quot; & &quot;*&quot; on the &quot;Current&quot; record, however the definition would suggest they need to be on both the PREVIOUS and CURRENT records - for instance what happens when the User gets &quot;hung up&quot; on a particularly interestiing Number - and there are [4 | 5 | 25] of the same?  What EXACTLY is flaged as a DUP?  Another issue appears to be the possible (PROBABLE) occurance where BOTH Flags need to be applied? Or -according to the sample- this is NOT POSSIBLE?  Because You ONLY mark the &quot;Current&quot; record?

 MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[tempclerk]

Sorry, by previous record, I mean the record that is printed on the line above in a Report. I thought I had said that, but I see I was mistaken. And since this will most likely be different every time the report is printed, it doesn't seem to me that making it a field in the table is appropriate.

How DO they do it on those bank statements?

I'm wondering if I need to somehow artificially number the lines. If I could just somehow refer to that "previous" record I could do something like:

IIf([codepart]=[previous codepart],"+", IIf([codepart]-1>[previous codepart],"-","&quot)

And, No, since the goal is only to have the second (or subsequent) occurence of a code or the code after a gap marked, there would never be a need for both marks.

 

[MichaelRed]

The field in the TABLE is (IMHO) the better way. You just need to run the Module (or do WHATEVER) as part of the process which generated the report AND have the Grouping and Sorting the same in the report as in the procedure which marks the records. This places the calculated) information where you can at least review it and check for problems. If you are REALLY concerned about concurrenct, you could make the report recordset be a "clone" table, where the first step in the procedure would be to delete all records and the procedure would populate the table via code as it walked through the original table, adding each record with it's "code".

The only oother approach I can see for this is to build a UDT with the fields necessary to do the calcs. Simply save the (current record) vars after the calculation is done. ON the Next record, they will represent the "Previous Record" info. Do the compare/calc from the UDT saved vars/field values and set the Code FLAG(s). This would 'contain' the calculation to the report execution, and certainly not be very difficult. YOu WOULD need a flag for the FIRST record, otherwise it would CERTAINLY end up being out of Sequence. You would also need to check for the change in Group - and suppress the calculation of the Code flag(s) -while 'rembering to collect the values for current record to be used as the Previous record values on the next pass.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Dim StatusFlag As String
    Dim blnNoCalc As Boolean

    StatusFlag = ""

    'Check for First Pass
    If (CodeStatus.Group = "" And CodeStatus.CodeRt = "") Then
        'Initalization
        With CodeStatus
        .Group = [Group]
        .CodeRt = [CodeRt]
        End With
        blnNoCalc = True
    End If

    'Not First Pass, Check for Group Change
    If (CodeStatus.Group <> [Group]) Then
        'OOps - Change of Group, So Dup and Seq == False
        With CodeStatus
        .Group = [Group]
        .CodeRt = [CodeRt]
        End With
        blnNoCalc = True
    End If
        
    If (Not blnNoCalc) Then
        'Check For DUPLICATE
        If (CodeStatus.CodeRt = [CodeRt]) Then
            StatusFlag = &quot;+&quot;
            blnNoCalc = True
        End If
    End If

    If (Not blnNoCalc) Then
        If (CLng(CodeStatus.CodeRt) + 1 <> [CodeRt]) Then
            StatusFlag = &quot;*&quot;
        End If
    End If

    Me.txtStatus = StatusFlag

    With CodeStatus
    .Group = [Group]
    .CodeRt = [CodeRt]
    End With

End Sub

This is the TABLE
Code Group Date
12-345 AB 8/1/01
12-1578 CD 8/1/01
12-1576 CD 8/2/01
12-344 AB 8/2/01
12-345 AB 8/4/01
12-341 AB 8/4/01

The QUERRY. this is the RECORDSOURCE for the REPORT. I did NOT include the elipiss fields (after all, I do not know what they are?), so you DEFFINITLY need to add them
SELECT tblCodeTest.Group, Right(

,Len([Code])-InStr([Code],"-")) AS CodeRt, Left([Code],InStr([Code],"-")) AS CodeLft, tblCodeTest.Date
FROM tblCodeTest
ORDER BY tblCodeTest.Group, Right([Code],Len([Code])-InStr([Code],"-"));


[b]This is the Query recordset.  Note the division of the "[Code]" field into left and right parts.  I also ordered and SORTED the fields according to the same criteria which is used in the report.
Group	CodeRt	CodeLft	Date
AB	341	12-	8/4/01
AB	344	12-	8/2/01
AB	345	12-	8/4/01
AB	345	12-	8/1/01
CD	1576	12-	8/2/01
CD	1578	12-	8/1/01

There is no easy way to show the rest.  txtStatus is the text box where the ["+" | "*"] is shown on the report.  It is assigned in the procedure (SUB).  Much of the sub can be removed with appropiate structuring of the code, however I thought the additional detail might be helpful to 
[b][i]tempclerk[/i][/b] in understanding the approach and logic behind the calculation.

Shakesperian!

 MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[tempclerk]

I understand how this is supposed to work, I think, but I don't write code myself, so please bear with me.

1. What is CodeStatus in the code? Is it the query name, or is there one? Should I put the SQL in the report itself?

2. When I try to preview the report I get "run-time error'424'/Object Required" and the debugger highlights the line (Under 'Check for First Pass):
If (CodeStatus.Group = "" And CodeStatus.CodeRt = "&quot Then
I have no idea what this could mean. Help?

Thanks

 

[MichaelRed]

Well, I really shouldn't be doing this in the dark of the week-end.

Option Explicit    Private Type CodeStatusType
        Group As String
        CodeRt As String
    End Type
    Dim CodeStatus As CodeStatusType
[

So CodeStatus is the (Heretofor) unknown UDT. It is only the 'place-holder' for the "Previous Record" values (or Interest).

All other questions should be answered amd resolved with this small mystery.

Sorry for the sloppiness.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[tempclerk]

Okay, now you've lost me. I know what Option Explicit means, but I don't follow the rest. As I mentioned, I don't write code, and I'm guessing where all this goes.
Does the additional code all have to go outside the Detail Format sub? I found that Option Explicit and Private Type do, but the rest seems to work inside?
I inserted the code and I don't get any errors now, but I can't preview the report. The screen flickers, then returns to design view (or the database window if I try to open it from there). Any ideas?

 

[MichaelRed]

'The Rest' of the Type Stuff (all "Code" in the last post) goes in the Declarations section of the Report Module. The First 'batch' of code should ALSO be in the Report Moduls -but I suspect it must be- because you aren't getting any error(s). As to Why (else) it would not work, I'm fairly clueless. I do not think I have used anything unique to a specific release/version of MS. Access or VBA or the Jet db Engine. You DID rename "tblCodeTest" to reflect YOUR table name? didn't You? Post your version of the module and the definition of the UDT which YOU are using. Perhaps it will give me a clue?


MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[tempclerk]

Okay, now all the code from the last post is in the declarations, but I get
Run-time error '2465' Microsoft Access can't find the field 'CodeRt' referred to in your expression.

Debugger highlights
.CodeRt = [CodeRt]

That is the correct name of the field in the SQL data source of the report, but I don't see anything telling the sub to look there. Do I need to?

 

[tempclerk]

I put an invisible text box on the report with [CodeRt] as its control source, and now it works! Thanks so much for your help!

 

[tempclerk]

Oops. Except the top of a new column is always marked a duplicate. It must run the code twice when the section needs to be moved.

 

[MichaelRed]

Please explain/expound. What do you mean by "new Column"?

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[tempclerk]

My report (in page setup) is set up with two columns of detail data (down then across). When it gets to the bottom of the page and starts on the next column or page, the first record in the new column is always marked as a duplicate.

 

[MichaelRed]

O.K. I think I know where to go to "fix" it. I do not think I can actually do it w/o the whole report (and -of course- the supporting data table / query). It Occurs because ot the 'Retreat" event. If you had anyone who could "Do Code", you could "trap" the event and reset the calculation of the flag (re-get the last pass values?).

In a 'sescriptive" process, Ms. Access always "prints" until the current record exceeds the page height allowed (or it is complete). When the allowed pae height is exceeded, it "retreats" -or backs up- and reprints. Since the calculation is done "on the Fly", this makes the current and previous record the "same" - hence it is flagged as a "Dup". Doesn't help, only explains.

If it were done in the recordsource PRIOR to the report generation, it wouldn't happen. THAT would require some overhead and/or dicipline to assure that the calculation was done JUST prior to the report generation.

While I could (fairly easily) implement the soloution myself, I do not think I can 'coach' a non-programmer through it.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[tempclerk]

Well, for now it's just a three-page report, and white-out isn't such a hassle. I intend to buy a book and start learning VBA this week. If you have any other suggestions I'd be happy to have them. Wish me luck.

 

[MichaelRed]

Luck? DO NOT depend on 'luck'. It has NO place in the world of data-processing! My wishing you 'luck' would be the WORST I could do. I'm one of those in the 'boat' where if it wern't for bad luck we wouldn't have any luck at all!

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over