Huge problem with multiple selection on Crosstab query

[Poweruser99]

I have a crosstab query which is two joined tables giving a sum of hours worked.

Now on my form I want to be able to select certain fields for a criteria.

I want to search by

* Sold to Customer Name
* ContractStartDate (Greater than or equal to)
* ContractEndDate (Less than or equal to)
* ContractNumber

I want to search for all of these fields or even a single one. I am baving logic problems with this. I have made a form which has the following

* Combo Box for searching Customer Name
* Text Box for searching the start date
* Text Box for searching the End date
* Combo Box for searching Contract Number

What I want is for the user to select either all of these values or any one of these to get a result. If for example a customer name is selected, and a non matching contract number, then I dont want any results to be displayed, likewise for the date. With my current code it is giving all the records if there is just one match.

Here is my code I hope someone can guide me better.

PARAMETERS [Forms]![Transaction Details]![cbo_client] Text ( 255 ), [Forms]![Transaction Details]![cbo_cont_num] Text ( 255 ), [Forms]![Transaction Details]![txt_start_date] DateTime, [Forms]![Transaction Details]![TXT_end_date] DateTime;
TRANSFORM Sum([Hours Consumed].[Adjusted Hours]) AS [SumOfAdjusted Hours]
SELECT [Hours Consumed].ContractNumber, [Hours Consumed].ContractStartDate, [Hours Consumed].ContractEndDate, [Hours Consumed].[Service Order Number], [Hours Consumed].[Sold to Customer Name], Format([ActivityStartDate],"dddd"", ""mmm d yyyy") AS ActivityStart, [Hours Remaining].[Hours Used], [Hours Remaining].HoursRemaining, [Hours Remaining].[Target Hours], Sum([Hours Consumed].[Adjusted Hours]) AS [Adjusted Hours]
FROM [Hours Consumed] INNER JOIN [Hours Remaining] ON ([Hours Consumed].[Sold to Customer Name] = [Hours Remaining].[Sold to Customer Name]) AND ([Hours Consumed].ContractNumber = [Hours Remaining].ContractNumber)
WHERE ((([Hours Consumed].[Sold to Customer Name])=[Forms]![Transaction Details]![cbo_client])) OR (((IsNull([Forms]![Transaction Details]![cbo_client]))<>False) AND (([Hours Consumed].ContractStartDate)>=[Forms]![Transaction Details]![txt_start_date])) OR (((IsNull([Forms]![Transaction Details]![txt_start_date]))<>False) AND (([Hours Consumed].ContractEndDate)<=[Forms]![Transaction Details]![txt_End_date])) OR (((IsNull([Forms]![Transaction Details]![txt_End_date]))<>False) AND (([Hours Consumed].ContractNumber)=[Forms]![Transaction Details]![cbo_cont_num])) OR (((IsNull([Forms]![Transaction Details]![cbo_cont_num]))<>False))
GROUP BY [Hours Consumed].ContractNumber, [Hours Consumed].ContractStartDate, [Hours Consumed].ContractEndDate, [Hours Consumed].[Service Order Number], [Hours Consumed].[Sold to Customer Name], Format([ActivityStartDate],"dddd"", ""mmm d yyyy"), [Hours Remaining].[Hours Used], [Hours Remaining].HoursRemaining, [Hours Remaining].[Target Hours], [Hours Consumed].ContractStartDate, [Hours Consumed].ContractEndDate
ORDER BY [Hours Consumed].ContractNumber, [Hours Consumed].ContractStartDate, [Hours Consumed].[Service Order Number]
PIVOT [Hours Consumed].ActivityType;

 

[Poweruser99]

Ok I got this to work sort of. Here is my revised code.

PARAMETERS [Forms]![Transaction Details]![cbo_client] Text ( 255 ), [Forms]![Transaction Details]![cbo_cont_num] Text ( 255 ), [Forms]![Transaction Details]![txt_start_date] DateTime, [Forms]![Transaction Details]![TXT_end_date] DateTime;
TRANSFORM Sum([Hours Consumed].[Adjusted Hours]) AS [SumOfAdjusted Hours]
SELECT [Hours Consumed].ContractNumber, [Hours Consumed].ContractStartDate, [Hours Consumed].ContractEndDate, [Hours Consumed].[Service Order Number], [Hours Consumed].[Sold to Customer Name], Format([ActivityStartDate],"dddd"", ""mmm d yyyy") AS ActivityStart, [Hours Remaining].[Hours Used], [Hours Remaining].HoursRemaining, [Hours Remaining].[Target Hours], Sum([Hours Consumed].[Adjusted Hours]) AS [Adjusted Hours]
FROM [Hours Consumed] INNER JOIN [Hours Remaining] ON ([Hours Consumed].[Sold to Customer Name]=[Hours Remaining].[Sold to Customer Name]) AND ([Hours Consumed].ContractNumber=[Hours Remaining].ContractNumber)
WHERE ((([Hours Consumed].[Sold to Customer Name])=Forms![Transaction Details]!cbo_client Or Forms![Transaction Details]!cbo_client Is Null) And (([Hours Consumed].ContractStartDate)>=Forms![Transaction Details]!txt_start_date Or ([Hours Consumed].ContractStartDate)>=Forms![Transaction Details]!txt_start_date Is Null) And (([Hours Consumed].ContractEndDate)<=Forms![Transaction Details]!txt_End_date Or ([Hours Consumed].ContractEndDate)<=Forms![Transaction Details]!txt_End_date Is Null) And (([Hours Consumed].ContractNumber)=Forms![Transaction Details]!cbo_cont_num Or Forms![Transaction Details]!cbo_cont_num Is Null))
GROUP BY [Hours Consumed].ContractNumber, [Hours Consumed].ContractStartDate, [Hours Consumed].ContractEndDate, [Hours Consumed].[Service Order Number], [Hours Consumed].[Sold to Customer Name], Format([ActivityStartDate],"dddd"", ""mmm d yyyy"), [Hours Remaining].[Hours Used], [Hours Remaining].HoursRemaining, [Hours Remaining].[Target Hours], [Hours Consumed].ContractStartDate, [Hours Consumed].ContractEndDate
ORDER BY [Hours Consumed].ContractNumber, [Hours Consumed].ContractStartDate, [Hours Consumed].[Service Order Number]
PIVOT [Hours Consumed].ActivityType;


Now when I run this via opening the query directly and test with different parameters, things are perfect. BUT when I run this via a form, then nothing is passed. It all comes out blank. Help!!!