Report criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all

I have a crosstab query that I am trying to get to work from selection
criteria entered in combo boxes on a reports menu. I have a similar query
already working that is just a select query. For some reason, using similar
logic, the crosstab isn't working properly (please see SQL below). Basically
if they enter just an activity or an activity and a specific person this
works ok, but if they don't enter an activity on the menu it doesn't produce
any results.

PARAMETERS [forms]![frm report menu]![DateFilter] Text ( 255 ), [forms]![frm
report menu]![txtActivity] Text ( 255 ), [forms]![frm report
menu]![txtSpecialist] Short;
TRANSFORM Count(Itinerary.ReviewDate) AS CountOfReviewDate
SELECT Itinerary.CountryCode, [Distributor Codes (ECS created)].Distributor,
Specialists.Specialist, Itinerary.Activity, tblActivity.Productive
FROM ([Distributor Codes (ECS created)] INNER JOIN Specialists ON
[Distributor Codes (ECS created)].CountryCode = Specialists.Country) INNER
JOIN (Itinerary INNER JOIN tblActivity ON Itinerary.Activity =
tblActivity.Activity) ON Specialists.SpecialistID = Itinerary.Specialist
WHERE (((Right([reviewdate],2))=Right([forms]![frm report
menu]![DateFilter],2)) AND ((Itinerary.Activity)=[forms]![frm report
menu]![txtActivity]) AND ((Specialists.SpecialistID)=[forms]![frm report
menu]![txtSpecialist])) OR (((Right([reviewdate],2))=Right([forms]![frm
report menu]![DateFilter],2)) AND ((Itinerary.Activity)=[forms]![frm report
menu]![txtActivity]) AND (([forms]![frm report menu]![txtSpecialist]) Is
Null)) OR (((Right([reviewdate],2))=Right([forms]![frm report
menu]![DateFilter],2)) AND ((Specialists.SpecialistID)=[forms]![frm report
menu]![txtSpecialist]) AND (([forms]![frm report menu]![txtActivity]) Is
Null)) OR (((Right([reviewdate],2))=Right([forms]![frm report
menu]![DateFilter],2)) AND (([forms]![frm report menu]![txtSpecialist]) Is
Null) AND (([forms]![frm report menu]![txtActivity]) Is Null))
GROUP BY Itinerary.CountryCode, [Distributor Codes (ECS
created)].Distributor, Specialists.Specialist, Itinerary.Activity,
tblActivity.Productive
ORDER BY Itinerary.CountryCode, tblActivity.Productive,
DatePart("m",[reviewdate])
PIVOT DatePart("m",[reviewdate]);

Can anyone help with this or do I need to look at creating separate queries
depending on if fields are left blank? I can't see why this works in the
select query but no a crosstab...

Thanks
Sue
 
The only possibility I see is the your [forms]![frm report
menu]![txtActivity] may be defaulting to something and therefore is not null.
 
It is an unbound control and therefore the form opens with both fields null.
Plus the other query runs ok from the same form fields.

KARL DEWEY said:
The only possibility I see is the your [forms]![frm report
menu]![txtActivity] may be defaulting to something and therefore is not null.

hughess7 said:
Hi all

I have a crosstab query that I am trying to get to work from selection
criteria entered in combo boxes on a reports menu. I have a similar query
already working that is just a select query. For some reason, using similar
logic, the crosstab isn't working properly (please see SQL below). Basically
if they enter just an activity or an activity and a specific person this
works ok, but if they don't enter an activity on the menu it doesn't produce
any results.

PARAMETERS [forms]![frm report menu]![DateFilter] Text ( 255 ), [forms]![frm
report menu]![txtActivity] Text ( 255 ), [forms]![frm report
menu]![txtSpecialist] Short;
TRANSFORM Count(Itinerary.ReviewDate) AS CountOfReviewDate
SELECT Itinerary.CountryCode, [Distributor Codes (ECS created)].Distributor,
Specialists.Specialist, Itinerary.Activity, tblActivity.Productive
FROM ([Distributor Codes (ECS created)] INNER JOIN Specialists ON
[Distributor Codes (ECS created)].CountryCode = Specialists.Country) INNER
JOIN (Itinerary INNER JOIN tblActivity ON Itinerary.Activity =
tblActivity.Activity) ON Specialists.SpecialistID = Itinerary.Specialist
WHERE (((Right([reviewdate],2))=Right([forms]![frm report
menu]![DateFilter],2)) AND ((Itinerary.Activity)=[forms]![frm report
menu]![txtActivity]) AND ((Specialists.SpecialistID)=[forms]![frm report
menu]![txtSpecialist])) OR (((Right([reviewdate],2))=Right([forms]![frm
report menu]![DateFilter],2)) AND ((Itinerary.Activity)=[forms]![frm report
menu]![txtActivity]) AND (([forms]![frm report menu]![txtSpecialist]) Is
Null)) OR (((Right([reviewdate],2))=Right([forms]![frm report
menu]![DateFilter],2)) AND ((Specialists.SpecialistID)=[forms]![frm report
menu]![txtSpecialist]) AND (([forms]![frm report menu]![txtActivity]) Is
Null)) OR (((Right([reviewdate],2))=Right([forms]![frm report
menu]![DateFilter],2)) AND (([forms]![frm report menu]![txtSpecialist]) Is
Null) AND (([forms]![frm report menu]![txtActivity]) Is Null))
GROUP BY Itinerary.CountryCode, [Distributor Codes (ECS
created)].Distributor, Specialists.Specialist, Itinerary.Activity,
tblActivity.Productive
ORDER BY Itinerary.CountryCode, tblActivity.Productive,
DatePart("m",[reviewdate])
PIVOT DatePart("m",[reviewdate]);

Can anyone help with this or do I need to look at creating separate queries
depending on if fields are left blank? I can't see why this works in the
select query but no a crosstab...

Thanks
Sue
Click to expand...
Click to expand...
 
Some further info... the query that does work is a select query without any
parameters specified. The crosstab query would not run without me creating
the parameters - it errors with 'The MS Jet DB engine does not recognize
'[forms]![frm report menu]![txtSpecialist]' as a valid field name or
expression.' If I put these parameters in the select query this also stops
working so it has to be something to do with this.

Anyone any ideas?

Thanks
Sue


hughess7 said:
It is an unbound control and therefore the form opens with both fields null.
Plus the other query runs ok from the same form fields.

KARL DEWEY said:
The only possibility I see is the your [forms]![frm report
menu]![txtActivity] may be defaulting to something and therefore is not null.

hughess7 said:
Hi all

I have a crosstab query that I am trying to get to work from selection
criteria entered in combo boxes on a reports menu. I have a similar query
already working that is just a select query. For some reason, using similar
logic, the crosstab isn't working properly (please see SQL below). Basically
if they enter just an activity or an activity and a specific person this
works ok, but if they don't enter an activity on the menu it doesn't produce
any results.

PARAMETERS [forms]![frm report menu]![DateFilter] Text ( 255 ), [forms]![frm
report menu]![txtActivity] Text ( 255 ), [forms]![frm report
menu]![txtSpecialist] Short;
TRANSFORM Count(Itinerary.ReviewDate) AS CountOfReviewDate
SELECT Itinerary.CountryCode, [Distributor Codes (ECS created)].Distributor,
Specialists.Specialist, Itinerary.Activity, tblActivity.Productive
FROM ([Distributor Codes (ECS created)] INNER JOIN Specialists ON
[Distributor Codes (ECS created)].CountryCode = Specialists.Country) INNER
JOIN (Itinerary INNER JOIN tblActivity ON Itinerary.Activity =
tblActivity.Activity) ON Specialists.SpecialistID = Itinerary.Specialist
WHERE (((Right([reviewdate],2))=Right([forms]![frm report
menu]![DateFilter],2)) AND ((Itinerary.Activity)=[forms]![frm report
menu]![txtActivity]) AND ((Specialists.SpecialistID)=[forms]![frm report
menu]![txtSpecialist])) OR (((Right([reviewdate],2))=Right([forms]![frm
report menu]![DateFilter],2)) AND ((Itinerary.Activity)=[forms]![frm report
menu]![txtActivity]) AND (([forms]![frm report menu]![txtSpecialist]) Is
Null)) OR (((Right([reviewdate],2))=Right([forms]![frm report
menu]![DateFilter],2)) AND ((Specialists.SpecialistID)=[forms]![frm report
menu]![txtSpecialist]) AND (([forms]![frm report menu]![txtActivity]) Is
Null)) OR (((Right([reviewdate],2))=Right([forms]![frm report
menu]![DateFilter],2)) AND (([forms]![frm report menu]![txtSpecialist]) Is
Null) AND (([forms]![frm report menu]![txtActivity]) Is Null))
GROUP BY Itinerary.CountryCode, [Distributor Codes (ECS
created)].Distributor, Specialists.Specialist, Itinerary.Activity,
tblActivity.Productive
ORDER BY Itinerary.CountryCode, tblActivity.Productive,
DatePart("m",[reviewdate])
PIVOT DatePart("m",[reviewdate]);

Can anyone help with this or do I need to look at creating separate queries
depending on if fields are left blank? I can't see why this works in the
select query but no a crosstab...

Thanks
Sue
Click to expand...
Click to expand...
Click to expand...
 
Back
Top