Crosstab query does not display all results

[mo]

I have a form & subform which has a crosstab query as its rescordsource. The
data on the subform would display data in the following fashion:

Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
2003 124 163 251 219 177 143 137 123 141 75 166 128 1847
2004 etc etc...

The user has reported that the form sometimes does not always display all
the results, but only for certain months. I have never seen this problem
myself as the form has always worked perfectly on my machine.

The target pc is using Win2K, Office2K. I'm not sure what service pack, but
at least SR1a.

Has anyone seen a similar problem and if so is there a possible resolution?

TIA,

Mo

------------------------------------------------------------------------------

The code behind the form is the following:

Dim db As Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strWhere As String

Set db = CurrentDb()

strSQL = "TRANSFORM Count(AutoNum) AS CountOfAutoNum "
strSQL = strSQL & "SELECT Format([entrydate],'yyyy') AS Recruit_Year,
HospCode, Count(AutoNum) AS AnnualTotal,
Abs(Sum(DateDiff('d',Date(),[eddbylmp])<=0)) AS NoOfDelivs "
strSQL = strSQL & "FROM TBL_REGISTRATION0003 "
strSQL = strSQL & "WHERE TrialGrpBak <> -1 "
strSQL = strSQL & "GROUP BY Format([entrydate],'yyyy'), HospCode "
strSQL = strSQL & "ORDER BY Format([entrydate],'yyyy') "
strSQL = strSQL & "PIVOT Format([entrydate],'mmm') In
('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')"

Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.RecordCount <> 0 Then
Me.frm_recruit_sub.Form.RecordSource = strSQL
Forms![frm_recruit]![frm_recruit_sub].Requery
Me.Refresh
Else
Me.frm_recruit_sub.Form.RecordSource = ""
MsgBox ("No matches found!"), vbOKOnly, "No matches"

rst.Close
Set rst = Nothing
Set db = Nothing

End If

 

[Michel Walsh]

Hi,


Use the Columns Headings property of the crosstab to enumerate all the
required fields, or, manually, add a IN in the PIVOT clause:

....
PIVOT Format(fieldName, "mmm") IN( "Jan", "Feb", ..., "Dec")



That assumes the field name would be in English, so that relay on the
regional setting of the PC (won't work fine on a PC where French or Spanish
is the default language).



Hoping it may help,
Vanderghast, Access MVP