Report criteria

H

hughess7

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
 
K

KARL DEWEY

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

hughess7

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...
 
H

hughess7

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code for setting Forms to open with custom tool bar, menubar and control custom short 1
Min and Max for Date Range in Report 4
Jet does not recognize " as valid field 0
Row and Column Averages in Dynamic Crosstab Report? 3
Crosstab Query Questions 5
Calculate working days in month on report 0
Report based on criteria form 5
Hide detail section of dynamic crosstab like report 0

Top