Query Problem!!

[giacomo1]

Can Someone take a look at this query and check the syntax for me. The query is not pulling info and there is info in the tables that meets the criteria..

SQL:


SELECT General.Customer_Cell_Number, General.Date_of_Transaction, General.Year, General.Month, General.Customer_Name, General.Customer_Last_Name, General.[Rate Plan Code], General.[Rate Plan Access Fee], General.Term, General.Notes, General.Employee_ID, General.Dealer_Code, General.Location
FROM [General]
WHERE (((General.Year)=[Please Enter Commission Year]) AND ((General.Month)=[Please Enter Commission Month]) AND ((General.[Rate Plan Access Fee])="37.99") AND ((General.Term)="2") AND ((General.Employee_ID)=[Please enter Sales Code]) AND ((General.Upgrade)=No)) OR (((General.Year)=[Please Enter Commission Year]) AND ((General.Month)=[Please Enter Commission Month]) AND ((General.[Rate Plan Access Fee])="39.99") AND ((General.Term)="2") AND ((General.Employee_ID)=[Please enter Sales Code]) AND ((General.Upgrade)=No)) OR (((General.Year)=[Please Enter Commission Year]) AND ((General.Month)=[Please Enter Commission Month]) AND ((General.[Rate Plan Access Fee])="47.99") AND ((General.Term)="2") AND ((General.Employee_ID)=[Please enter Sales Code]) AND ((General.Upgrade)=No)) OR (((General.Year)=[Please Enter Commission Year]) AND ((General.Month)=[Please Enter Commission Month]) AND ((General.[Rate Plan Access Fee])="49.99") AND ((General.Term)="2") AND ((General.Employee_ID)=[Please enter Sales Code]) AND ((General.Upgrade)=No));

 

[glynch]

Should I dare ask why you would want to run a query that prompts you 12 times to enter a value?

Just for debugging purposes I would enter all of the constants as criteria in one query, and then use that query within a second query and add the variable criteria one by one until it fails. That should lead to the solution.

 

[glynch]

Another thought, are the Access Fee and Term fields of a text datatype or numeric?

 

[giacomo1]

They are both text datatypes....and the answer to your previous question is.....it is just a temp solution until I find a better way Any suggestions?

 

[glynch]

Would the querying of the date info generally be for date ranges?

There are calendar controls that can be used to select dates, use two date fields and query on dates between the two selected dates.

For other values you could use a multiselect listbox to pick available values if the list isn't too long.

For your existing query I would break it down like I suggested and pinpoint where it starts to fail. When I have a query that doesn't run, I eliminate one field at a time until it runs, it usually helps identify the problem.

Good luck.

 

[RV]

Optimized statement

Here's a liitle redesign of your query:

SELECT General.Customer_Cell_Number, General.Date_of_Transaction, General.Year, General.Month, General.Customer_Name, General.Customer_Last_Name, General.[Rate Plan Code], General.[Rate Plan Access Fee], General.Term, General.Notes, General.Employee_ID, General.Dealer_Code, General.Location
FROM [General]
WHERE General.Year = [Please Enter Commission Year]
AND General.Month = [Please Enter Commission Month]
AND General.Term="2"
AND General.Employee_ID=[Please enter Sales Code]
AND General.Upgrade=No
AND General.[Rate Plan Access Fee] IN ("37.99","39.99","47.99",""49.99");

1) Don't use spaces in your column names.
2) Don't use Yae and Month for column names.

As Glynch suggested, if your query doesn't work for you, break it up.
Drop one or more conditions until the query retrieves the rows you expect it to.
Then adjust the dropped condition(s) and add them to your statement.


RV

 

[giacomo1]

AWESOME!!!! Thanks So Much....