Report criteria

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
 
G

Guest

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

Guest

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

Guest

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

Query criteria 2
Fill in some or all criteria 4
One-to-Many Query 3
Selecting Criteria from Combo Box 1
Sending Multiple Criteria to Report 1
Row and Column Averages in Dynamic Crosstab Report? 3
QUERY TO SORT BY MORE THAN ONE COLUMN 1
Show All Dates 1

Top