Crosstab query-Null value entered via form

[Iceman95]

Hi,

I'm back again with a problem I've been struggling with for weeks, without being able to find any satisfying solution.

Here it is :

I designed a crosstab query based on a select query that requires parameters to be entered via a form.

All parameters have been declared in the crosstab query, and everything works fine, provided that the parameters are given a non-Null value.

But I wanted the parameters to be optional, i.e when some of the associated controls (mainly drop-down lists) are left blank (contain a Null value), the query must return all records related to those specific parameters.

So I set the following statement for each parameter to be entered : [Forms]![MyForm]![MyParameter] OR LIKE [Forms]![MyForm]![MyParameter] IS NULL.

Everything is OK when the crosstab query is executed in design view or without opening the form (i.e. parameters prompted via dialog boxes), but not when the parameters are entered through the form.

Furthermore, the problem seems to be specific to Xtab queries, since the select query works perfectly with the form.

To sum up the trouble : crosstab queries seem not to support parameters containing Null value entered in a form.

I'm desperate with this bug, since I don't want the end user of my application to be bothered with dozens of dialog boxes opening one after each other (my form is made of more than 20 controls), furthermore losing the drop-down lists convenience.

Has someone come across the same trouble ? Is there any way to solve it ?

Thanks in advance for any help.

Tom

 

[Liv Manto]

USe the NZ. (Null to Zero if Integer or Null To Empty String if string)


[Forms]![MyForm]![MyParameter] OR NZ( [Forms]![MyForm]![MyParameter], 0) = 0 or NZ( [Forms]![MyForm]![MyParameter], "") = ""

 

[Jon K]

I think it is a bug.


For a declared numeric parameter, Access can properly evaluate
OR [Forms]![MyForm]![MyParameter] IS NULL as True when the numeric parameter is left blank, and hence will return every record.

However, if the declared parameter is text, Access fails to evaluate the expression as True when the text parameter is left blank, and hence no records will be returned. I came to this conclusion by experimenting with this simply select query:-

PARAMETERS [forms]![MyForm]![cboYear] Text ( 4 );
SELECT *
FROM tblAnnualSales
WHERE (InYear=[forms]![MyForm]![cboYear] Or [forms]![MyForm]![cboYear] Is Null);


I think, in your case, you can change the select query to a make-table query and build the crosstab query based on the table created, thereby getting rid of the need to declare the parameters for the crosstab query.
.

 

[Iceman95]

Yes, I experimented the make-table solution, I think unfortunately I'll have to use it, but it's a pity, for definitely not time-saving and adding complexity where it should not have.

Thanks anyway, and I'll dig out the text parameter issue ; will try to convert every parameter to numeric or date, but I doubt to get any positive result.

 

[Jon K]

Upon further testing, I found that if a declared parameter is text, Access will evaluate
[Forms]![MyForm]![TextParameter]="" as True when the text parameter is left blank. Hence we can use it as a workaround.

I have attached the database that I used for testing.
It contains a select query and a crosstab query:-

PARAMETERS [Forms]![MyForm]![cboYear] Long, [Forms]![MyForm]![cboEmployeeName] Text ( 255 );
SELECT tblSales.*
FROM tblSales
WHERE ([Year]=[Forms]![MyForm]![cboYear] Or [forms]![MyForm]![cboYear] Is Null)=True AND
([EmployeeName]=[Forms]![MyForm]![cboEmployeeName] Or [Forms]![MyForm]![cboEmployeeName]="")=True;


TRANSFORM Sum([query1].[Amount]) AS SumOfAmount
SELECT [query1].[EmployeeName], Sum([query1].[Amount]) AS [Total Of Amount]
FROM query1
GROUP BY [query1].[EmployeeName]
PIVOT [query1].[Year];


They worked on my Access 2000 and 2003 systems. You can open the form and click on the command button to see if they work on yours.


Note
In the select query, I added =True after the criteria for each field so that Access will not re-arrange the Where Clause if the query is subsequently edited and saved in query Design View.
.

 

[Iceman95]

Thanks, man, it helped !

That was not the cause of my trouble, but it helped to find it out !

The problem was comming from an IIf... instruction used as criterion for one of my field. Now my Xtab query shows off when the parameters are entered with the form, even if they are left blank !

Anyway, thanks a lot for the (...)=true tip when specifying the criteria : never thought of it, it saves MUCH time when you're editing your query !

Nevertheless, I am stuck now with another problem... Feel like I will blow my head !!!

Here is the thing : I use an adaptation of the Solutions.mdb sample database code to build a dynamic report for my Xtab query ; until then, it worked fine.

Now, the report doesn't build correctly, because the underlying recordset contains no values (whereas the Xtab query does contain values !!!) - gives a "no current record" error when trying to go to first record.

I think the code cannot handle the fact that the parameters contains "" or Null values. Is there anything to do when including the parameters in the code ?

Here is what I've written (quite long !), where "Analyse croisée Nombre Tous" is my Xtab query and "Dialogue2" my criteria form :

Option Compare Database
Option Explicit

' Constant for maximum number of columns EmployeeSales query would
' create plus 1 for a Totals column. Here, you have 9 employees.
Const conTotalColumns = 16

' Variables for Database object and Recordset.
Dim dbsReport As DAO.Database
Dim rstReport As DAO.Recordset

' Variables for number of columns and row and report totals.
Dim intColumnCount As Integer
Dim lngRgColumnTotal(1 To conTotalColumns) As Long
Dim lngReportTotal As Long

Private Sub InitVars()

Dim intX As Integer

' Initialize lngReportTotal variable.
lngReportTotal = 0

' Initialize array that stores column totals.
For intX = 1 To conTotalColumns
lngRgColumnTotal(intX) = 0
Next intX

End Sub


Private Function xtabCnulls(varX As Variant)

' Test if a value is null.
If IsNull(varX) Then
' If varX is null, set varX to 0.
xtabCnulls = 0
Else
' Otherwise, return varX.
xtabCnulls = varX
End If

End Function


Private Sub Détail_Format(Cancel As Integer, FormatCount As Integer)
' Put values in text boxes and hide unused text boxes.

Dim intX As Integer
' Verify that you are not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, put values from recordset into text boxes
' in "Detail" section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
Next intX

' Hide unused text boxes in the "Detail" section.
For intX = intColumnCount + 2 To conTotalColumns
Me("Col" + Format(intX)).Visible = False
Next intX

' Move to next record in recordset.
rstReport.MoveNext
End If
End If

End Sub

Private Sub Détail_Print(Cancel As Integer, PrintCount As Integer)

Dim intX As Integer
Dim lngRowTotal As Long

' If PrintCount is 1, initialize rowTotal variable.
' Add to column totals.
If Me.PrintCount = 1 Then
lngRowTotal = 0

For intX = 2 To intColumnCount
' Starting at column 2 (first text box with crosstab value),
' compute total for current row in the "Detail" section.
lngRowTotal = lngRowTotal + Me("Col" + Format(intX))

' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("Col" + Format(intX))
Next intX

' Put row total in text box in the "Detail" section.
Me("Col" + Format(intColumnCount + 1)) = lngRowTotal
' Add row total for current row to grand total.
lngReportTotal = lngReportTotal + lngRowTotal
End If
End Sub


Private Sub Détail_Retreat()

' Always back up to previous record when "Detail" section retreats.
rstReport.MovePrevious

End Sub


Private Sub EntêtePage_Format(Cancel As Integer, FormatCount As Integer)

Dim intX As Integer

' Put column headings into text boxes in page header.
For intX = 1 To intColumnCount
Me("Head" + Format(intX)) = rstReport(intX - 1).Name
Next intX

' Make next available text box Totals heading.
Me("Head" + Format(intColumnCount + 1)) = "TOTAL"

' Hide unused text boxes in page header.
For intX = (intColumnCount + 2) To conTotalColumns
Me("Head" + Format(intX)).Visible = False
Next intX

End Sub

Private Sub Report_Close()

On Error Resume Next
Forms!Dialogue.Visible = True
' Close recordset.
rstReport.Close

End Sub


Private Sub Report_NoData(Cancel As Integer)

MsgBox "Il n'y a pas d'enregistrement(s) correspondant à vos critères.", vbExclamation, "Pas d'enregistrement(s)"
rstReport.Close
Cancel = True

End Sub


Private Sub Report_Open(Cancel As Integer)

' Create underlying recordset for report using criteria entered in
' EmployeeSalesDialogBox form.

Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form

' Set database variable to current database.
Set dbsReport = CurrentDb
Set frm = Forms!Dialogue2
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("Analyse croisée Nombre Tous")
' Set parameters for query based on values entered
' in EmployeeSalesDialogBox form.
qdf.Parameters("Forms!Dialogue2!PDateDébut") _
= frm!PDateDébut
qdf.Parameters("Forms!Dialogue2!PDateFin") _
= frm!PDateFin
qdf.Parameters("Forms!Dialogue2!PNom du liquidateur") _
= frm![PNom du liquidateur]
qdf.Parameters("Forms!Dialogue2!PNom du contrôleur") _
= frm![PNom du contrôleur]
qdf.Parameters("Forms!Dialogue2!PType de contrôle") _
= frm![PType de contrôle]
qdf.Parameters("Forms!Dialogue2!PRubrique") _
= frm![PRubrique]
qdf.Parameters("Forms!Dialogue2!PZone utilisateur 1") _
= frm![PZone utilisateur 1]
qdf.Parameters("Forms!Dialogue2!PZone utilisateur 2") _
= frm![PZone utilisateur 2]
qdf.Parameters("Forms!Dialogue2!PNIR") _
= frm![PNIR]
qdf.Parameters("Forms!Dialogue2!PNom adhérent") _
= frm![PNom adhérent]
qdf.Parameters("Forms!Dialogue2!PPrénom adhérent") _
= frm![PPrénom adhérent]
qdf.Parameters("Forms!Dialogue2!PType de prestation") _
= frm![PType de prestation]
qdf.Parameters("Forms!Dialogue2!PDate de prise d'effet") _
= frm![PDate de prise d'effet]
qdf.Parameters("Forms!Dialogue2!PAnomalie 1") _
= frm![PAnomalie 1]
qdf.Parameters("Forms!Dialogue2!PAnomalie 2") _
= frm![PAnomalie 2]
qdf.Parameters("Forms!Dialogue2!PAnomalie 3") _
= frm![PAnomalie 3]
qdf.Parameters("Forms!Dialogue2!PAnomalie 4") _
= frm![PAnomalie 4]
qdf.Parameters("Forms!Dialogue2!PIncidence financière") _
= frm![PIncidence financière]
qdf.Parameters("Forms!Dialogue2!PIntervalle 1") _
= frm![PIntervalle 1]
qdf.Parameters("Forms!Dialogue2!PIntervalle 2") _
= frm![PIntervalle 2]
qdf.Parameters("Forms!Dialogue2!PDate d'ordonnancement") _
= frm![PDate d'ordonnancement]
qdf.Parameters("Forms!Dialogue2!PDate de transmission pour contrôle") _
= frm![PDate de transmission pour contrôle]
qdf.Parameters("Forms!Dialogue2!PDate de retour pour correction") _
= frm![PDate de retour pour correction]
qdf.Parameters("Forms!Dialogue2!PDate de retour après correction") _
= frm![PDate de retour après correction]
qdf.Parameters("Forms!Dialogue2!PDate de validation comptable") _
= frm![PDate de validation comptable]



' Open Recordset object.
Set rstReport = qdf.OpenRecordset()

' Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count

End Sub


Private Sub PiedÉtat_Print(Cancel As Integer, PrintCount As Integer)

Dim intX As Integer

' Put column totals in text boxes in report footer.
' Start at column 2 (first text box with crosstab value).
For intX = 2 To intColumnCount
Me("Tot" + Format(intX)) = lngRgColumnTotal(intX)
Next intX

' Put grand total in text box in report footer.
Me("Tot" + Format(intColumnCount + 1)) = lngReportTotal

' Hide unused text boxes in report footer.
For intX = intColumnCount + 2 To conTotalColumns
Me("Tot" + Format(intX)).Visible = False
Next intX

End Sub


Private Sub EntêteÉtat_Format(Cancel As Integer, FormatCount As Integer)

' Move to first record in recordset at the beginning of the report
' or when the report is restarted. (A report is restarted when
' you print a report from Print Preview window, or when you return
' to a previous page while previewing.)
rstReport.MoveFirst

'Initialize variables.
InitVars

End Sub

Any idea on this ?