problem with criteria in crosstable qry (1 Viewer)

[Real Wally]

Hi All,

I'm working on a crosstable qry that should compare records of various companies in a variable (but defined) period

The form has 2 text fields to define the period (txtdate1 and textdate2). That works fine.
I can also combine the period criteria with a company criteria by manually ading the company name but what doesn't work is when I select the company using a combobox.

To select for the company in the qry criteria I'm using the same criteria as I do for a selection qry:
([forms]![frmName]![cboboxname]

When I copy the expression above into the criteria of a crosstable qry I get an error. (not a valid fieldname or expression)


Anybody any suggestion??

Here's the whole code:

PARAMETERS [forms]![frmFormsAndReports_Period]![txtdate1] Short, [forms]![frmFormsAndReports_Period]![txtdate2] Short;
TRANSFORM Count(tblPatents.PriorityDate) AS CountOfPriorityDate
SELECT [Company_Short] & "-" & [area] AS lbl
FROM tblArea INNER JOIN (tblCompanies INNER JOIN tblPatents ON tblCompanies.CompanyID = tblPatents.CompanyID) ON tblArea.AreaID = tblPatents.AreaID
WHERE (((tblCompanies.Company)=[forms]![frmFormsAndReports_Period]![ChooseCompany]) AND ((Year([PriorityDate])) Between [forms]![frmFormsAndReports_Period]![txtdate1] And [forms]![frmFormsAndReports_Period]![txtdate2]))
GROUP BY [Company_Short] & "-" & [area], tblCompanies.Company_Short
PIVOT Year([PriorityDate]);

 

[Badswell]

I am currently having this same issue. In a multiple crosstable query that combines the results of a series of 9 individual crosstabs. The individual queries all recognize the combo box criteria and run perfectly fine. Yet when the “master” query is run, it refuses to recognize the same combo box that has worked all along. Any ideas?

 

[dcx693]

Make sure that you define all the parameters for your crosstab query in design view (Query, Parameters) - not just the Dates. I don't see this one defined: [forms]![frmFormsAndReports_Period]![ChooseCompany]

 

[Real Wally]

Make sure that you define all the parameters for your crosstab query in design view (Query, Parameters) - not just the Dates. I don't see this one defined: [forms]![frmFormsAndReports_Period]![ChooseCompany]

I wish I understood what you meant.
Apparently I've defined the other one (how though?)
i haven't got a clue how I can fix this. Could you be a bit more specific please?

Thanks.

 

[dcx693]

OK, look at your SQL code. Notice how at the beginning, you have this:
PARAMETERS [forms]![frmFormsAndReports_Period]![txtdate1] Short, [forms]![frmFormsAndReports_Period]![txtdate2] Short;

Now notice that you don't have this parameter mentioned:
[forms]![frmFormsAndReports_Period]![ChooseCompany]

You need to do one of two things (identical effects, different methods):
1- in the query design view, go to the Query menu, choose Parameters enter that undefined parameter, and choose a data type for it
2 - change the Parameters section of your SQL statement to read:
PARAMETERS [forms]![frmFormsAndReports_Period]![txtdate1] Short, [forms]![frmFormsAndReports_Period]![txtdate2] Short,[forms]![frmFormsAndReports_Period]![ChooseCompany] Long;
or whatever the proper data type is based on what the control "ChooseCompany" holds. I would do (1), since I forget often the SQL data type descriptions for the Access data types.

 

[Real Wally]

Thanks for yor reply.
I now understand what the parameter bit is (I think). That's the good news.
The bad news is that I'm still not getting a result.

Here's what I've got after adding the parameter for ChooseCompany:

PARAMETERS [forms]![frmFormsAndReports_Period]![txtdate1] Short, [forms]![frmFormsAndReports_Period]![txtdate2] Short, [forms]![frmFormsAndReports_Period]![ChooseCompany] Text ( 255 );
TRANSFORM Count(tblPatents.PriorityDate) AS CountOfPriorityDate
SELECT [Company_Short] & "-" & [area] AS lbl
FROM tblArea INNER JOIN (tblCompanies INNER JOIN tblPatents ON tblCompanies.CompanyID = tblPatents.CompanyID) ON tblArea.AreaID = tblPatents.AreaID
WHERE (((tblCompanies.Company_Short)="[forms]![frmFormsAndReports_Period]![ChooseCompany]") AND ((tblArea.Area) Like "*") AND ((Year([PriorityDate])) Between [forms]![frmFormsAndReports_Period]![txtdate1] And [forms]![frmFormsAndReports_Period]![txtdate2]))
GROUP BY [Company_Short] & "-" & [area], tblCompanies.Company_Short, tblArea.Area
PIVOT Year([PriorityDate]);

The 'Company field' on the form is a combobox. In the table tblCompanies it's a text field of 255 characters.

Since I still get a zero return of records something is still not correct.

Am i still overlooking something?
Thankx for your patience,

Walter

 

[Rich]

WHERE (((tblCompanies.Company_Short)="[forms]![frmFormsAndReports_Period]![ChooseCompany]")

Why the quotes?

 

[Real Wally]

Why the quotes?

No reason other than that in my desparation I've tried all sort of minor changes since nothing seems to work. Also, since I have a significant gap in my limited knowledge in this area nothing stops me from making the simplest mistakes.

The SQL without the quotes don't work either si there must be another problem (or problems?)

 

[dcx693]

Good catch Rich!

There's a lot going on in this query. I'd simplify it by perhaps recreating it from the ground up and see where it goes wrong. You can also just copy your existing query and start removing pieces of it to simplify it. The idea is just to see what works, and what doesn't. Sometimes, I write critieria that doesn't make sense and results in no records being found.

 

[Real Wally]

There's a lot going on in this query. I'd simplify it.

OK, did that. I'm sorry for the length of this posting but it's not as bad as it looks as there are only minor changes in the code that I've explained.

Simplified qry. Just the period as an expression without amy criteria for Company or Area.

PARAMETERS [forms]![frmFormsAndReports_Period]![txtdate1] Short, [forms]![frmFormsAndReports_Period]![txtdate2] Short;
TRANSFORM Count(tblPatents.PriorityDate) AS CountOfPriorityDate
SELECT [Company_Short] & "-" & [area] AS lbl
FROM tblArea INNER JOIN (tblCompanies INNER JOIN tblPatents ON tblCompanies.CompanyID = tblPatents.CompanyID) ON tblArea.AreaID = tblPatents.AreaID
WHERE (((Year([PriorityDate])) Between [forms]![frmFormsAndReports_Period]![txtdate1] And [forms]![frmFormsAndReports_Period]![txtdate2]))
GROUP BY [Company_Short] & "-" & [area]
PIVOT Year([PriorityDate]);

The qry like this works fine but gives far too much information. I need to narrow it down to a particular area of company of choice:



If I manually enter a company name (in this case Nutricia) I get the following SQL and a perfect result:

PARAMETERS [forms]![frmFormsAndReports_Period]![txtdate1] Short, [forms]![frmFormsAndReports_Period]![txtdate2] Short, [forms]![frmFormsAndReports_Period]![ChooseCompany] Text ( 255 );
TRANSFORM Count(tblPatents.PriorityDate) AS CountOfPriorityDate
SELECT [Company_Short] & "-" & [area] AS lbl
FROM tblArea INNER JOIN (tblCompanies INNER JOIN tblPatents ON tblCompanies.CompanyID = tblPatents.CompanyID) ON tblArea.AreaID = tblPatents.AreaID
WHERE (((Year([PriorityDate])) Between [forms]![frmFormsAndReports_Period]![txtdate1] And [forms]![frmFormsAndReports_Period]![txtdate2]) AND ((tblCompanies.Company_Short)="nutricia"))
GROUP BY [Company_Short] & "-" & [area], tblCompanies.Company_Short
PIVOT Year([PriorityDate]);

Qry also works fine if I add a second criteria for Area. So far so good.


Next, I replace Nutricia with [forms]![frmFormsAndReports_Period]![ChooseCompany] and with the text parameter defined as text I get this and this is were trouble starts as I get no result anymore. The qry runs, but brings up zero records.

PARAMETERS [forms]![frmFormsAndReports_Period]![txtdate1] Short, [forms]![frmFormsAndReports_Period]![txtdate2] Short, [forms]![frmFormsAndReports_Period]![ChooseCompany] Text ( 255 );
TRANSFORM Count(tblPatents.PriorityDate) AS CountOfPriorityDate
SELECT [Company_Short] & "-" & [area] AS lbl
FROM tblArea INNER JOIN (tblCompanies INNER JOIN tblPatents ON tblCompanies.CompanyID = tblPatents.CompanyID) ON tblArea.AreaID = tblPatents.AreaID
WHERE (((Year([PriorityDate])) Between [forms]![frmFormsAndReports_Period]![txtdate1] And [forms]![frmFormsAndReports_Period]![txtdate2]) AND ((tblCompanies.Company_Short)=[forms]![frmFormsAndReports_Period]![ChooseCompany]))
GROUP BY [Company_Short] & "-" & [area]
PIVOT Year([PriorityDate]);

Sorry for imposing but I simply cannot solve this riddle by myself. I've spent far too much time on it already but get absolutely nowhere.

 

[dcx693]

OK, so the culprit is probably this:
[forms]![frmFormsAndReports_Period]![ChooseCompany]

You've already mentioned that the company name is a text field of 255 length. Regarding the particular combo box called "ChooseCompany" (you did say it was a combo box, right?), is there more than 1 column in it? If so, what is the "bound" column? Are you sure it's the company name, and not something like the company number?

 

[Real Wally]

it is a combobox and there are 2 clomns, 1 = CompanyID (autonumber) and 2 is Company (text)

I've tried changing the bound column for 0 to 1 to 2
Same thing, no results

If I remove the CompanyID from the row source I get an empty combobox so that cannot be right either (I've changed the bound column accordingly)

I've rebuild the form, rebuild the qry butI just don't get it.

Access help just confuses me more and I totally depend on the forum to get this solved.

 

[Rich]

It would be much easier to add the CompanyID to the query and use the criteria for that from the combo, other than than that you can wrap the combo criteria with the Eval function or add an unboundx textbox to the form set its control source to = [ChooseCompany].[Column](1), change the criteria to reference that textbox instead of the combo

 

[Real Wally]

Rich and dcx693,

I've finally got the qry going AND finding the correct records!!
Thank you both so much for helping me see the light. I might even be able to create one all by myself now

After this rush I feel ready to tackle the next problem(s)

Thanks again

Walter