Control source is select statement does not work.

[JimRich]

I have a report built on a control source with 3 criteria based on a form. The report will run correctly if the form is not open and each of the criteria is entered when requested. If the form is open I get an empty record. The form name is "Find Skills", all the criteria is present just before the report is called; all control names are correct. The SQL statement is as follows:

SELECT [LName] & ", " & [fname] AS Name, ClientSkills.Category, ClientSkills.SubCategory, ClientSkills.id, ClientSkills.Skill, Client.DateOfApp, Client.Phone, Client.Transportaion, Client.Active, System.Company, System.Slogan, System.Logo, System.Address1, [system.city] & ", " & [system.state] & " " & [zip] AS cocity, System.Phone, [category] & "-- " & [subcategory] AS cat
FROM System, Client INNER JOIN ClientSkills ON Client.id = ClientSkills.id
WHERE (((ClientSkills.Category)=[Forms]![Find Skills]![cmboFindCat]) AND ((ClientSkills.SubCategory)=[Forms]![Find Skills]![listFindSubCat]) AND ((ClientSkills.Skill)=[Forms]![Find Skills]![listFindSkills]) AND ((Client.Active)=Yes))
ORDER BY [LName] & ", " & [fname];

Can anyone show me what I should do differently or a better way?




JimRich

 

[RickSpr]

It might be that for one or more of those control references, the comparison is being made to the control object instead of to its default Value property. Try changing, for example, [Forms]![Find Skills]![cmboFindCat] to [Forms]![Find Skills]![cmboFindCat].Value

Access usually determines whether a reference is to the control or to the control's value by looking at the operations and the data types of the other elements in the expression. Since you put (ClientSkills.Category), for example, in parentheses, you may have obscured what the expected data type is, leaving Access to default to treating the reference as an object reference. So another think you might try is removing the parentheses around field names in the WHERE clause expressions.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein