Error Message

  • Thread starter Thread starter BigK9
  • Start date Start date
B

BigK9

I keep getting the following message when I try to run a sales report

This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables.

I have provided my SQL statement as follows.

SELECT DISTINCTROW [LastName] & ", " & [FirstName] AS [Employee Name],
Sum(CLng((nz([Labor Totals by Workorder].[Labor Total])+nz([Parts Totals by
Workorder].[Parts Total]))*[SalesTaxRate]*100)/100) AS [Sales Tax],
Sum([Labor Totals by Workorder].[Labor Total]) AS [Total Labor], Sum([Parts
Totals by Workorder].[Parts Total]) AS [Total Parts]
FROM ((Employees RIGHT JOIN Workorders ON Employees.EmployeeID =
Workorders.EmployeeID) LEFT JOIN [Labor Totals by Workorder] ON
Workorders.WorkorderID = [Labor Totals by Workorder].WorkorderID) LEFT JOIN
[Parts Totals by Workorder] ON Workorders.WorkorderID = [Parts Totals by
Workorder].WorkorderID
WHERE (((Workorders.DateFinished)>=[Forms]![Report Date Range]![Beginning
Date] And (Workorders.DateFinished)<=[Forms]![Report Date Range]![Ending
Date]))
GROUP BY [LastName] & ", " & [FirstName];


Please look this over and help me to fix it.
 
I would start by finishing your Nz() expressions which should provide a
default value
nz([Labor Totals by Workorder].[Labor Total], 0 )
It may also help to enter the data types of your date controls in the
Query->Parameters.
 
The error message indicates that Access is not able to make sense of the
query. This can happen for many reasons. One is that the date types don't
match. You solve that one by declaring your parameters.

In query design view, choose Parameters on the Query menu.
Access opens a dialog. Enter 2 rows, like this:
[Forms]![Report Date Range]![Beginning Date] DateTime
[Forms]![Report Date Range]![Ending Date] DateTime

It is possible that the calculated field could be causing difficulties, so
I've simplified that too. Not sure what the CLng() part was for; you may
need to add that back in.

but see if something like this works:

PARAMETERS [Forms]![Report Date Range]![Beginning Date] DateTime,
[Forms]![Report Date Range]![Ending Date] DateTime;
SELECT [LastName] & ", " & [FirstName] AS [Employee Name],
(Nz(Sum([Labor Totals by Workorder].[Labor Total]),0)
+ Nz(Sum([Parts Totals by Workorder].[Parts Total]),0))
* [SalesTaxRate] AS [Sales Tax],
Sum([Labor Totals by Workorder].[Labor Total]) AS [Total Labor],
Sum([Parts Totals by Workorder].[Parts Total]) AS [Total Parts]
FROM ((Employees RIGHT JOIN Workorders
ON Employees.EmployeeID = Workorders.EmployeeID)
LEFT JOIN [Labor Totals by Workorder]
ON Workorders.WorkorderID = [Labor Totals by Workorder].WorkorderID)
LEFT JOIN [Parts Totals by Workorder]
ON Workorders.WorkorderID = [Parts Totals by Workorder].WorkorderID
WHERE (Workorders.DateFinished Between
[Forms]![Report Date Range]![Beginning Date]
And [Forms]![Report Date Range]![Ending Date])
GROUP BY [LastName] & ", " & [FirstName];

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

BigK9 said:
I keep getting the following message when I try to run a sales report

This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables.

I have provided my SQL statement as follows.

SELECT DISTINCTROW [LastName] & ", " & [FirstName] AS [Employee Name],
Sum(CLng((nz([Labor Totals by Workorder].[Labor Total])+nz([Parts Totals
by
Workorder].[Parts Total]))*[SalesTaxRate]*100)/100) AS [Sales Tax],
Sum([Labor Totals by Workorder].[Labor Total]) AS [Total Labor],
Sum([Parts
Totals by Workorder].[Parts Total]) AS [Total Parts]
FROM ((Employees RIGHT JOIN Workorders ON Employees.EmployeeID =
Workorders.EmployeeID) LEFT JOIN [Labor Totals by Workorder] ON
Workorders.WorkorderID = [Labor Totals by Workorder].WorkorderID) LEFT
JOIN
[Parts Totals by Workorder] ON Workorders.WorkorderID = [Parts Totals by
Workorder].WorkorderID
WHERE (((Workorders.DateFinished)>=[Forms]![Report Date Range]![Beginning
Date] And (Workorders.DateFinished)<=[Forms]![Report Date Range]![Ending
Date]))
GROUP BY [LastName] & ", " & [FirstName];


Please look this over and help me to fix it.
Click to expand...
 
I changed the Nz() expressions and did not get any better results. I do not
understand about the entering data types in the date controls. Could you
explain please.
--
BigK9


Duane Hookom said:
I would start by finishing your Nz() expressions which should provide a
default value
nz([Labor Totals by Workorder].[Labor Total], 0 )
It may also help to enter the data types of your date controls in the
Query->Parameters.

--
Duane Hookom
Microsoft Access MVP


BigK9 said:
I keep getting the following message when I try to run a sales report

This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables.

I have provided my SQL statement as follows.

SELECT DISTINCTROW [LastName] & ", " & [FirstName] AS [Employee Name],
Sum(CLng((nz([Labor Totals by Workorder].[Labor Total])+nz([Parts Totals by
Workorder].[Parts Total]))*[SalesTaxRate]*100)/100) AS [Sales Tax],
Sum([Labor Totals by Workorder].[Labor Total]) AS [Total Labor], Sum([Parts
Totals by Workorder].[Parts Total]) AS [Total Parts]
FROM ((Employees RIGHT JOIN Workorders ON Employees.EmployeeID =
Workorders.EmployeeID) LEFT JOIN [Labor Totals by Workorder] ON
Workorders.WorkorderID = [Labor Totals by Workorder].WorkorderID) LEFT JOIN
[Parts Totals by Workorder] ON Workorders.WorkorderID = [Parts Totals by
Workorder].WorkorderID
WHERE (((Workorders.DateFinished)>=[Forms]![Report Date Range]![Beginning
Date] And (Workorders.DateFinished)<=[Forms]![Report Date Range]![Ending
Date]))
GROUP BY [LastName] & ", " & [FirstName];


Please look this over and help me to fix it.
Click to expand...
Click to expand...
 
Check Allen's reply.

--
Duane Hookom
Microsoft Access MVP


BigK9 said:
I changed the Nz() expressions and did not get any better results. I do not
understand about the entering data types in the date controls. Could you
explain please.
--
BigK9


Duane Hookom said:
I would start by finishing your Nz() expressions which should provide a
default value
nz([Labor Totals by Workorder].[Labor Total], 0 )
It may also help to enter the data types of your date controls in the
Query->Parameters.

--
Duane Hookom
Microsoft Access MVP


BigK9 said:
I keep getting the following message when I try to run a sales report

This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables.

I have provided my SQL statement as follows.

SELECT DISTINCTROW [LastName] & ", " & [FirstName] AS [Employee Name],
Sum(CLng((nz([Labor Totals by Workorder].[Labor Total])+nz([Parts Totals by
Workorder].[Parts Total]))*[SalesTaxRate]*100)/100) AS [Sales Tax],
Sum([Labor Totals by Workorder].[Labor Total]) AS [Total Labor], Sum([Parts
Totals by Workorder].[Parts Total]) AS [Total Parts]
FROM ((Employees RIGHT JOIN Workorders ON Employees.EmployeeID =
Workorders.EmployeeID) LEFT JOIN [Labor Totals by Workorder] ON
Workorders.WorkorderID = [Labor Totals by Workorder].WorkorderID) LEFT JOIN
[Parts Totals by Workorder] ON Workorders.WorkorderID = [Parts Totals by
Workorder].WorkorderID
WHERE (((Workorders.DateFinished)>=[Forms]![Report Date Range]![Beginning
Date] And (Workorders.DateFinished)<=[Forms]![Report Date Range]![Ending
Date]))
GROUP BY [LastName] & ", " & [FirstName];


Please look this over and help me to fix it.
Click to expand...
Click to expand...
Click to expand...
 
change:
Sum([Labor Totals by Workorder].[Labor Total])

to Sum(nz([Labor Totals by Workorder].[Labor Total]))

and:
Sum([PartsTotals by Workorder].[Parts Total]) AS [Total Parts]
to:
Sum(nz([PartsTotals by Workorder].[Parts Total])) AS [Total Parts]




Check Allen's reply.

--
Duane Hookom
Microsoft Access MVP

BigK9 said:
I changed the Nz() expressions and did not get any better results. I do not
understand about the entering data types in the date controls. Could you
explain please.
Click to expand...
I would start by finishing your Nz() expressions which should provide a
default value
nz([Labor Totals by Workorder].[Labor Total], 0 )
It may also help to enter the data types of your date controls in the
Query->Parameters.
--
Duane Hookom
Microsoft Access MVP
:
I keep getting the following message when I try to run a sales report
This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables.
I have provided my SQL statement as follows.
SELECT DISTINCTROW [LastName] & ", " & [FirstName] AS [Employee Name],
Sum(CLng((nz([Labor Totals by Workorder].[Labor Total])+nz([Parts Totals by
Workorder].[Parts Total]))*[SalesTaxRate]*100)/100) AS [Sales Tax],
Sum([Labor Totals by Workorder].[Labor Total]) AS [Total Labor], Sum([Parts
Totals by Workorder].[Parts Total]) AS [Total Parts]
FROM ((Employees RIGHT JOIN Workorders ON Employees.EmployeeID =
Workorders.EmployeeID) LEFT JOIN [Labor Totals by Workorder] ON
Workorders.WorkorderID = [Labor Totals by Workorder].WorkorderID) LEFT JOIN
[Parts Totals by Workorder] ON Workorders.WorkorderID = [Parts Totals by
Workorder].WorkorderID
WHERE (((Workorders.DateFinished)>=[Forms]![Report Date Range]![Beginning
Date] And (Workorders.DateFinished)<=[Forms]![Report Date Range]![Ending
Date]))
GROUP BY [LastName] & ", " & [FirstName];
Please look this over and help me to fix it.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top