Control source in 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. 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

 

[dhookom]

Since there seem to be a list box and combo box on your form, you need to pay attention to the bound column and make sure it matches the field in the query. When your form is open, press Ctrl+G and enter:
?[Forms]![Find Skills]![cmboFindCat]

?[Forms]![Find Skills]![listFindSubCat]

Do you see the values you want to use as criteria?

 

[JimRich]

Yes those fields have the values I selected.


JimRich

 

[dhookom]

Do they match the fields in your query or are you comparing IDs/codes to titles?

 

[JimRich]

They match.

Thanks

JimRich

 

[dhookom]

Save the query as "qselFindSkills". Then open the form and enter values in the controls. Next open the query and play with it until you understand what values are being sent to the query. One at a time, remove a criteria to see which one if screwing up your results.

If the query works as expected then check the report's filter or other properties.

 

[JimRich]

Thanks I'll try that.



JimRich