QueryDef for Select on CrossTab giving to many Records

[Noel2]

Okay Here is my situation:

I have a report that I am trying to get working and I am having huge troubles with it. The main problem that I am having is that I am gaining an extra record or two when I create a temporary QueryDef. The recordsource is a Select Query that queries a Crosstab Query. I run the query outside of the VBA and it works fine, so I am pretty sure the query is not the problem. For my report, I am assigning the values to each one of the boxes in the detail section. The only problem is that there is always an extra record.

I have tried to hide this extra record, but have been unsuccessful. The closest I can get is to hide all except the total field and then turn the total field black through the BackColor. I can't change the ForeColor property or the BorderColor property for just the one field, it always changes it for every field in the column. Same thing happens when I try to set its visible property to false. I really am upset about this. I am including some of the code for some help with explanation.

On the open property of the form I have:

Dim intX As Integer
Dim qdf As QueryDef
Dim astro As QueryDef

Set dbsReport = CurrentDb
Set astro = dbsReport.CreateQueryDef("tmp_qry", "SELECT qrytmsht_rpt.* FROM qrytmsht_rpt WHERE (((qrytmsht_rpt.[employee no]) Is Not Null));&quot
Set qdf = dbsReport.QueryDefs("tmp_qry&quot

Set rstReport = qdf.OpenRecordset()

intColumnCount = rstReport.Fields.Count
intRstCount = DCount("*", "tmp_qry&quot
dbsReport.QueryDefs.Delete ("tmp_qry&quot

To Initiate the variables I have the Report Header Call this:

Dim intX As Integer

lngReportTotal = 0
intDetailCount = 0

For intX = 1 To conTotalColumns
lngRgColumnTotal(intX) = 0
Next intX

On Format I have:

Dim intX As Integer

If Not intDetailCount >= intRstCount Then
If Me.FormatCount = 1 Then
For intX = 3 To intColumnCount
' Convert Null values to 0
Me("hr" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
If Me("hr" + Format(intX)).Value = "0" Then
Me("hr" + Format(intX)).Visible = False
Else
Me("hr" + Format(intX)).Visible = True
End If
Next intX

For intX = intColumnCount + 2 To conTotalColumns
Me("hr" + Format(intX)).Visible = False
Next intX
End If
Else
For intX = 3 To intColumnCount
Me("hr" + Format(intX)).Value = "0"
If Me("hr" + Format(intX)).Value = "0" Then
Me("hr" + Format(intX)).Visible = False
Else
Me("hr" + Format(intX)).Visible = True
End If
Next intX
End If
intDetailCount = intDetailCount + 1
If Not intDetailCount >= intRstCount Then
rstReport.MoveNext
End If

To eliminate Nulls the function xtabCnulls below:

If IsNull(varX) Then
xtabCnulls = "-"
Else
xtabCnulls = varX
End If

The Column Headings are put in through other code which I thought was not pertinent to my question. For the totals and other things to come together and probably where I will get my solution is in the On Print for the detail section below:

Dim intX As Integer
Dim lngRowTotal As Double
Dim lngerFxer As Variant

If Me.PrintCount = 1 Then
lngRowTotal = 0

For intX = 4 To intColumnCount
' Starting at column 4 (first text box with crosstab value),
' compute total for current row in detail section.
If Me("hr" + Format(intX)) = "-" Then
lngerFxer = 0
Else
lngerFxer = Me("hr" + Format(intX))
End If

lngRowTotal = lngRowTotal + lngerFxer
lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + lngerFxer
Next intX

Me("hr" + Format(intColumnCount + 1)) = lngRowTotal
End If

If intDetailCount > intRstCount Then
Me("hr" + Format(intColumnCount + 1)).BackColor = 0
Else
Me("hr" + Format(intColumnCount + 1)).BackColor = 12632256
End If

Please tell me if you have any ideas.

Thanks,
Noel

 

[Noel2]

Is this post too long for people? Is the question too hard? Is what I am trying to do impossible? I really would like to know so that I know where I stand with the post that I made. Please someone help me with some type of response.

Thank you,
Noel