Solved Problem with Cross Tab Query (1 Viewer)

[tihmir]

Hi all.
I have the following problem: When I define the parameter in the Cross Tab Query "[Forms]![fm_Reports]![cboEmployee] and make column expression: [tbl_12Inspections.Employee]=[forms]![fm_Reports]![cboEmployee] Or [forms]![fm_Reports]![cboEmployee] Is Null, I can NOT see all records they gone until I choose Employee from cboEmployee. But I want to see all records IF my combobox is Null.

 

[theDBguy]

Please post the SQL statement for your crosstab query.

 

[arnelgp]

try this criteria:

[tbl_12Inspections.Employee]=IIF(IsNull([forms]![fm_Reports]![cboEmployee]), [tbl_12Inspections.Employee],[forms]![fm_Reports]![cboEmployee])

 

[tihmir]

Please post the SQL statement for your crosstab query.

PARAMETERS [Forms]![fm_12Reports]![txtFrom] DateTime, [Forms]![fm_12Reports]![txtTo] DateTime, [Forms]![fm_12Reports]![cboEmployee] Text ( 255 );
SELECT tbl_12ObjectType.ObjectID,
tbl_12ObjectType.ObjectType,
tbl_12Inspections.DateT,
tbl_12Inspections.Employee, tbl_12Objects.Municipality,
tbl_12Objects.City,
tbl_12Inspections.InspectionType,
Sum([qry_12-Samples-1].[KA-CTA]) AS KA,
Sum([qry_12-Samples-1].[KD-KD123]) AS KD,
Sum([qry_12-Samples-1].[KSP-KSP123]) AS KSP,
Sum([qry_12-Samples-1].[MCh-123]) AS MCh,
Sum([qry_12-Samples-1].[HK-HK123]) AS HK
FROM
(tbl_12ObjectType LEFT JOIN tbl_12Objects ON tbl_12ObjectType.ObjectType = tbl_12Objects.ObjectType)
LEFT JOIN
(tbl_12Inspections LEFT JOIN [qry_12-Samples-1] ON tbl_12Inspections.InspectionID = [qry_12-Samples-1].InspectionID)
ON
tbl_12Objects.ObjectID = tbl_12Inspections.ObjectID
WHERE (((IIf([Forms]![fm_12Reports]![txtFrom] Is Null And [forms]![fm_12Reports]![txtTo] Is Null,True,IIf([Forms]![fm_12Reports]![txtFrom] Is Not Null
And [forms]![fm_12Reports]![txtTo] Is Not Null,[DateT] Between [Forms]![fm_12Reports]![txtFrom] And [Forms]![fm_12Reports]![txtTo],[DateT]>=[forms]![fm_12Reports]![txtFrom]
Or [DateT]<=[forms]![fm_12Reports]![txtTo])))=True) AND (([tbl_12Inspections.Employee]=[forms]![fm_12Reports]![cboEmployee] Or [forms]![fm_12Reports]![cboEmployee] Is Null)=True))
GROUP BY tbl_12ObjectType.ObjectID, tbl_12ObjectType.ObjectType, tbl_12Inspections.DateT, tbl_12Inspections.Employee, tbl_12Objects.Municipality, tbl_12Objects.City, tbl_12Inspections.InspectionType;

 

[tihmir]

try this criteria:

[tbl_12Inspections.Employee]=IIF(IsNull([forms]![fm_Reports]![cboEmployee]), [tbl_12Inspections.Employee],[forms]![fm_Reports]![cboEmployee])

Same problem again!
When the cbo is empty and no records are displayed in the query. When I select an employee from the cbo , only records for the employee appear!

I wonder why when I use this criteria for range Date everything works:

IIf([Forms]![fm_12Reports]![txtFrom] Is Null And [forms]![fm_12Reports]![txtTo] Is Null;True;
IIf([Forms]![fm_12Reports]![txtFrom] Is Not Null And [forms]![fm_12Reports]![txtTo] Is Not Null;
[DateT] Between [Forms]![fm_12Reports]![txtFrom] And [Forms]![fm_12Reports]![txtTo];
[DateT]>=[forms]![fm_12Reports]![txtFrom] Or [DateT]<=[forms]![fm_12Reports]![txtTo]))

But When I add the second criteria, the records disappear:

[tbl_12Inspections.Employee]=IIF(IsNull([forms]![fm_Reports]![cboEmployee]), [tbl_12Inspections.Employee],[forms]![fm_Reports]![cboEmployee])

 

[theDBguy]

PARAMETERS [Forms]![fm_12Reports]![txtFrom] DateTime, [Forms]![fm_12Reports]![txtTo] DateTime, [Forms]![fm_12Reports]![cboEmployee] Text ( 255 );
SELECT tbl_12ObjectType.ObjectID,
tbl_12ObjectType.ObjectType,
tbl_12Inspections.DateT,
tbl_12Inspections.Employee, tbl_12Objects.Municipality,
tbl_12Objects.City,
tbl_12Inspections.InspectionType,
Sum([qry_12-Samples-1].[KA-CTA]) AS KA,
Sum([qry_12-Samples-1].[KD-KD123]) AS KD,
Sum([qry_12-Samples-1].[KSP-KSP123]) AS KSP,
Sum([qry_12-Samples-1].[MCh-123]) AS MCh,
Sum([qry_12-Samples-1].[HK-HK123]) AS HK
FROM
(tbl_12ObjectType LEFT JOIN tbl_12Objects ON tbl_12ObjectType.ObjectType = tbl_12Objects.ObjectType)
LEFT JOIN
(tbl_12Inspections LEFT JOIN [qry_12-Samples-1] ON tbl_12Inspections.InspectionID = [qry_12-Samples-1].InspectionID)
ON
tbl_12Objects.ObjectID = tbl_12Inspections.ObjectID
WHERE (((IIf([Forms]![fm_12Reports]![txtFrom] Is Null And [forms]![fm_12Reports]![txtTo] Is Null,True,IIf([Forms]![fm_12Reports]![txtFrom] Is Not Null
And [forms]![fm_12Reports]![txtTo] Is Not Null,[DateT] Between [Forms]![fm_12Reports]![txtFrom] And [Forms]![fm_12Reports]![txtTo],[DateT]>=[forms]![fm_12Reports]![txtFrom]
Or [DateT]<=[forms]![fm_12Reports]![txtTo])))=True) AND (([tbl_12Inspections.Employee]=[forms]![fm_12Reports]![cboEmployee] Or [forms]![fm_12Reports]![cboEmployee] Is Null)=True))
GROUP BY tbl_12ObjectType.ObjectID, tbl_12ObjectType.ObjectType, tbl_12Inspections.DateT, tbl_12Inspections.Employee, tbl_12Objects.Municipality, tbl_12Objects.City, tbl_12Inspections.InspectionType;

Thanks. That's a little too much to just read and decipher. Are you able to post a sample db with test data?

 

[tihmir]

Thanks. That's a little too much to just read and decipher. Are you able to post a sample db with test data?

Okay, I'm going to rewrite a bit of the database into English because it's in Cyrillic.
Thank you all for your help!

 

[tihmir]

I attach a a simple database.
What I am trying to do is to extract the required result through three queries. With query one I do the filtration and query 3 I use it for my report. I can't think of any other way to do this. I also attach a photo to show what I want the end result to be.

 

[arnelgp]

i am using Query when you press the button on your fmFilter form.
you need to make your Report's Label/textboxes dynamic because
some inspector has fewer/ or many columns to show.

 

[tihmir]

i am using Query when you press the button on your fmFilter form.
you need to make your Report's Label/textboxes dynamic because
some inspector has fewer/ or many columns to show.

Thank you again arnelgp! With your code and the improvements you made еverything works perfectly. I greatly appreciate your help, the time you take to help me and the other members of this forum. Your tips and tutorials are very valuable to me!

Regarding difficulties I had with the Report, I changed the strategy and look of one of the tables by merging two columns into one so I could bypass the cross table query so I can safely add the two criteria. What do I mean, I changed the columns in a table by adding the five varieties of tblSampling like each variety I created it as a separate column in which to directly record the numerical sampling.

Thank you all for your time and help again!