Trim Query and Report to Show Missing numbers

[Gale Coleman]

Hello all,

Using Access 2000.

I received help from you wonderful folks to trim a field in a table like so:

SELECT CLIENTSW.CASENUM, IIf(Mid([CaseNum],4),Left([CaseNum],0) &
Right([CaseNum],7),[CaseNum]) AS TempCaseNum
FROM CLIENTSW
WHERE (((CLIENTSW.CASENUM) Like "04*"));

This works exactly as I want it to.

I then went on Google and found how to create a report that will show the
numbers that are out of Sequence:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210234

I put the following code in a report based on the above query:

Dim GlobalFlag as Variant

Function SetFlag(x as Variant)
If GlobalFlag <> x - 1 then
SetFlag = "*"
Else
SetFlag = ""
End If

GlobalFlag = x
End Function

I added a textbox with the name of Out of Sequence and a control source =
SetFlag([TempCaseNum])

I run the report and get no * where there are missing numbers. I put in a
number out of sequence to test it and it is not working. What am I doing
wrong?

Gale

 

[Ken Snell [MVP]]

"Not working" meaning what? What are you getting as a result? In which
section of the report did you put the textbox? Is TempCaseNum a field that
is in the report's recordsource? Is there a control on the report (same
section as the textbox) that is bound to that field?

For reports, if you plan to use a field as a data source in a control
source, the field must be on the report or the report cannot find it.

 

[Ken Snell [MVP]]

OK, I think I see what is happening. Your query appears to be returning a
string value for the TempCaseNum data. So let's try this change to your
Function and see if this helps (I'm inserting usage of CInt function to cast
the x value as a long integer):


Dim GlobalFlag as Variant

Function SetFlag(x as Variant)
If GlobalFlag <> CInt(x) - 1 then
SetFlag = "*"
Else
SetFlag = ""
End If

GlobalFlag = CInt(x)
End Function

--

Ken Snell
<MS ACCESS MVP>

"Not working" meaning what? What are you getting as a result? I get an

asterisk beside each Out of Sequence Number instead of by the numbers that
are out of sequence only.

In which section of the report did you put the textbox? The Out of Sequence
text box is in the Detail section. The TempCaseNum box is in the Detail
section.

Is TempCaseNum a field that is in the report's recordsource? Yes, it is
based on the query mentioned in my post.

Is there a control on the report (same section as the textbox) that is bound
to that field? Yes, it is =SetFlag([TempCaseNum])

For reports, if you plan to use a field as a data source in a control
source, the field must be on the report or the report cannot find it. - It
is on the report


Report:

I have a report header with the name of the report on it.

I have a page header with a label "Casenumber" and a label "Temporary Case
Number"

I have a detail section: with a text box named "Casenum" with the control
source of "Casenum" from the query mentioned. I also have a text box named
"TempCaseNum" with the control source of "TempCaseNum" from the query
mentioned. I have a label with "Out of Sequence" and a text box named "Out
of Sequence" with a control source of =SetFlag([TempCaseNum])

I have the following code on the report:

Option Compare Database

Dim GlobalFlag As Variant

Function SetFlag(x As Variant)
If GlobalFlag <> x - 1 Then
SetFlag = "*"
Else
SetFlag = ""
End If

GlobalFlag = x
End Function

I have sorting and grouping as TempCaseNum, Ascending. I have included a
gif of what the report looks like.

 

[Gale]

Hello again Ken,

I think I figured part of it out. I changed :

Dim GlobalFlag as Variant

Function SetFlag(x as Variant)
If GlobalFlag <> CInt(x) - 1 then
SetFlag = "*"
Else
SetFlag = ""
End If

GlobalFlag = CInt(x)
End Function

to:

Dim GlobalFlag As Variant

Function SetFlag(x As Variant)
If GlobalFlag <> Int(x) - 1 Then
SetFlag = "*"
Else
SetFlag = ""
End If

GlobalFlag = Int(x)
End Function

The only problem I have now is that it gives me an asterisk on the first
number of every page. It appears that it is only looking by each page. Is
there a way to tell it to continue, even if there is a page break?

Thanks,

Gale

 

[Ken Snell [MVP]]

Changing from Int to CInt shouldn't make the function work differently, at
least not based on what I know.

But, try using a Static declaration for the GlobalFlag inside the function
and delete the Public declaration outside the function:

Function SetFlag(x As Variant)
Static GlobalFlag As Variant
If GlobalFlag <> Int(x) - 1 Then
SetFlag = "*"
Else
SetFlag = ""
End If

GlobalFlag = Int(x)
End Function


If you know that the number will be an integer number, then why declare
everything as variant?

Function SetFlag(x As Integer)
Static GlobalFlag As Integer
If GlobalFlag <> x - 1 Then
SetFlag = "*"
Else
SetFlag = ""
End If

GlobalFlag = x
End Function

 

[Gale]

Hi Ken,

This is what I found, I left the function like this: (Although it also works
to use the static declaration for the Globalflag)

Dim GlobalFlag As Variant

Function SetFlag(x As Variant)
If GlobalFlag <> Int(x) - 1 Then
SetFlag = "*"
Else
SetFlag = ""
End If

GlobalFlag = Int(x)
End Function

When I put the "C" in front of Int, I get #num! beside each number. I don't
know why as I am nothing close to a coder, but when I take the "C" out. It
works as expected.

As far as the asterisk on the first number of each page: If I page through
the pages in sequence (page 1, then page 2, then page 3, etc.), I don't get
the asterisk on the first page of the report, I only get them on the numbers
that are missing or out of sequence. If I go to the last page first and go
backward, thats when I get the asterisk on the first number of each page.

Thanks,

Gale

 

[Ken Snell [MVP]]

Yes, the asterisk will appear for the first number if you go backwards
through the pages while in report preview mode.

This is because you're now comparing the first number on that page to the
last number of the page after it, and of course they are not in sequence any
more. This is because the last number of the other page is the last one that
was stored in GlobalFlag.

You'll need to remember this for preview mode; however, if you print the
report, it should not be a problem.

 

[Gale]

Thanks Ken,

I am always learning something new and appreciate your help immensely in
figuring out my problem!!!

Gale

 

[Ken Snell [MVP]]

You're welcome.