Too Complex to be evaluated

[vbbbmom]

I have 4 queries that run and prepare a temporary table for which I then have a sales report generated . THis report has been active for over a year exactly as is but now the past week I am getting an error message that says "The expression is typed incorrectly or is too complex to be evaluated".

I used the query designer in access to create them, here they are in SQL VIEW


My Make Table Query:

SELECT [Job Table].[Job Number], [Job Table].[Date Sold], Commission.originalCRate, Customers.[Last Name], [Job Table].Salesperson, [Job Table].Split, [Job Table].[Job Type], Commission.SaleAmount, Commission.JobCost, Commission.SPR INTO [New Sales Report Temp Table]
FROM (Customers LEFT JOIN [Job Table] ON Customers.[client id] = [Job Table].[Client id]) LEFT JOIN Commission ON [Job Table].[Job Number] = Commission.[Job Number]
WHERE ((([Job Table].[Date Sold])>#12/31/2012#) AND (([Job Table].Split)=No));

Query 2:

INSERT INTO [New Sales Report Temp Table] ( [Job Number], [Date Sold], [Last Name], Split, SaleAmount, Salesperson, [Job Type], OriginalCRate, SPR, JobCost )
SELECT [Job Table].[Job Number], [Job Table].[Date Sold], Customers.[Last Name], [Job Table].Split, [saleAmount]*[SPr] AS TotalSale4, [Job Table].Salesperson, [Job Table].[Job Type], Commission.OriginalCRate, Commission.SPR, [jobCost]*[SPR] AS JCost
FROM (Customers LEFT JOIN [Job Table] ON Customers.[client id] = [Job Table].[Client id]) LEFT JOIN Commission ON [Job Table].[Job Number] = Commission.[Job Number]
WHERE ((([Job Table].[Date Sold])>#12/31/2012#) AND (([Job Table].Split)=Yes));

Query 3:

INSERT INTO [New Sales Report Temp Table] ( [Job Number], [Date Sold], [Last Name], Split, SaleAmount, Salesperson, [Job Type], OriginalCRate, SPR, JobCost )
SELECT [Job Table].[Job Number], [Job Table].[Date Sold], Customers.[Last Name], [Job Table].Split, [saleAmount]*[SPS2R] AS TotalSale4, [Job Table].SP2, [Job Table].[Job Type], Commission.SP2CR, Commission.SPS2R, [JobCost]*[SPS2R] AS JCost
FROM (Customers LEFT JOIN [Job Table] ON Customers.[client id] = [Job Table].[Client id]) LEFT JOIN Commission ON [Job Table].[Job Number] = Commission.[Job Number]
WHERE ((([Job Table].[Date Sold])>#12/31/2012#) AND (([Job Table].Split)=Yes) AND (([Job Table].SP2) Is Not Null));

Query 4:

INSERT INTO [New Sales Report Temp Table] ( [Job Number], [Date Sold], [Last Name], Split, SaleAmount, Salesperson, [Job Type], OriginalCRate, SPR, JobCost )
SELECT [Job Table].[Job Number], [Job Table].[Date Sold], Customers.[Last Name], [Job Table].Split, [saleAmount]*[SPS3R] AS TotalSale4, [Job Table].SP3, [Job Table].[Job Type], Commission.SP3CR, Commission.SPS3R, [JobCost]*[SPS3R] AS JCost
FROM (Customers LEFT JOIN [Job Table] ON Customers.[client id] = [Job Table].[Client id]) LEFT JOIN Commission ON [Job Table].[Job Number] = Commission.[Job Number]
WHERE ((([Job Table].[Date Sold])>#12/31/2012#) AND (([Job Table].Split)=Yes) AND (([Job Table].SP3) Is Not Null));


When I run the queries as a query and view data in datasheet they all work, however when I run the report I get the error message.

Any help would be greatly appreciated. My Access DB is a front end connected to an SQL server backend. I have never created queries in SQL just in Access.

Thank you in advance!

Shellyn

 

[CJ_London]

You haven't said which query is causing the problem

if they has been working but have now stopped this indicates you have issues with the data which the query cannot evaluate. Often this is a null value.

When you view the data as a query, it is only running the select part. So perhaps you are trying to insert a null or a number greater than that allowed - you are not making any specification in your make table so perhaps it is creating the wrong datatype for the subsequent appends.

After you have made your table, check the datatypes are correct before running the append queries