Reports :: Sum Different Fields With Same Format - Too Complex To Be Evaluated
Aug 6, 2013
I am trying to sum 3 different time fields together in a report that all are the same format but it wont allow me to.
The expression i have built is =Sum([ACDTime]+[AgentRingTime]+[OtherTime])
but I get back a error message saying...
"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."
View Replies
ADVERTISEMENT
May 23, 2007
The control source for the Activity Summary report in my DB is the primary data table. The report contains about 12 fields, each with a statement like this one as its control source: =Sum(Abs(Year([RecDate])=Year(Date()) And ([NotifType]=9)))
Up until yesterday afternoon the report was working just fine. But suddenly when I run the report I'm getting this error:
"The 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 no idea why this problem suddenly popped up when the report had been running just fine for weeks.
Here's what I've done so far to try and fix it:
1. Copied the report and gave it a new name, thinking if the report were somehow corrupt creating a new one might fix it. No such luck.
2. Tried running the report after removing each control source statement one-by-one, but the error didn't disappear until all the statements had been cleared.
Anyone have any ideas? I'm willing to post the DB if that will help.
View 2 Replies
View Related
Dec 2, 2014
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));
[code]...
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.My Access DB is a front end connected to an SQL server backend. I have never created queries in SQL just in Access.
View 1 Replies
View Related
Aug 16, 2006
Hey guys,
I am getting this error "This expression is typed incorrectly, or is too complex to be evaluated"
For this query:
SELECT first([TblProp].[Name]) AS [SName], First([TblProp].[CommentDate]) AS DateCommented, First([TblProp].[No]) AS BNum, First([TblProp].[Indication]) AS Ind, First([TblProp].[PropSubmitted]) AS DateSub, First([TblProp].[Contact]) AS PrimCon, First([TblProp].[Prepared]) AS PrepName, First([TblProp].[Comment]) AS Comment,First([TblProp].[Value]) AS ValueNew, First([TblProp].[Rating]) AS Prob
FROM TblProp
GROUP BY [TblProp].[RFPNo];
This query had been working fine for a good long time, but suddenly it starts throwing up this message. I haven't changed anything at all with this query. Its very puzzling. I went through each of the fields and its the comment field that is causing the problem.
Would anyone have any ideas on why this might be happening?
Thanks for reading this!
Polo
View 6 Replies
View Related
Nov 21, 2006
I am getting the following error on my query:
"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."
Below is my SQL for this query:
SELECT DISTINCTROW [OI Category Aging Query].[Age List], [OI Category Aging Query].[Reporting Entity], Sum([OI Category Aging Query].[Accrual $]) AS [Sum Of Accrual $], Sum([OI Category Aging Query].Unearned) AS [Sum Of Unearned]
FROM [OI Category Aging Query]
GROUP BY [OI Category Aging Query].[Age List], [OI Category Aging Query].[Reporting Entity];
It's just a simple query to sum up two columns from another query. This query is for an OI Category Aging report, and I have another MI Category Aging report that is working perfectly fine. They almost mirror each other, except for a few calculation differences. They both start from the same table. And my OI Category Aging Query seems to be working just fine. I cannot find any errors or N/A's in either the Accrual $ column or the Unearned column. Any suggestions on what to do or where to go from here? A simple sum on only the Accrual $ column or the Unearned column doesn't work either.
The OI Category Aging Query is 198,711 rows, so I can't drop it into excel.
Any help would be greatly appreciated!
Thanks,
Jason
View 1 Replies
View Related
Feb 8, 2006
I have a simple query to calcualte a profit margin on daily sales lines and I use a quick and dirty expression to calculate the margin in the query so I never need to drill it down further than that level (I don't want to go as far as putting the output into a report as it is only for use when double checking lines for errors which get fixed there and then in the database).
So far so good, however the margin output is a bit awkward to read as I can't seem to format it as a simple percentage. The field properties page doesn't like doing anything with the expression and even typing in a format manually has no effect, so I end up with figures like
36.7768595041322
38.6666666666667
15.6448202959831
etc
the expression i use is:
Margin: IIf([dbo_tbl_sales_invoice_lines.price]=0,"",([dbo_tbl_sales_invoice_lines.price]-[net_cost])/[dbo_tbl_sales_invoice_lines.price]*100)
Is there any way to format this output to show only 1-2 decimal places and be in a proper number format so I can sort them in ascending order properly?
View 7 Replies
View Related
Jun 8, 2015
I have an Access application that print a report with 7 sub-reports in it. When I preview the report, it looks fine. When I print it directly to the printer, it looks fine. The problem is when I print from the preview, Access error with a "...too complex..." error and crashes the application.
View 8 Replies
View Related
Aug 13, 2007
Hi at the moment I am using a bit of VBA code like below:
Public Const currentYear As String = "0708"
Static Function GetCurrentYear()
GetCurrentYear = currentYear
End Function
I then call this from my ms access sql statement with GetCurrentYear() am I correct in thinking this will only need to be evaluated once (I am just thinking in terms of performance) as it is a static function and a constant or is there a quicker way to do this. I couldn't see a way to easily get the value from a constant without a function. I may be missing something though thanks for any advice.
View 4 Replies
View Related
May 7, 2014
I've got a word mail merge document that is formatted exactly how I want it. I'm using an Access 2003 database to populate the word 2003 mail merge document.What I would like is to use a access report rather than a mail merge but correctly formatting everything is going to take me forever.
Is there any way I can use the word document I already have to enforce the formatting or must I spend hours positioning each line of text by eye and guessing where on my report it must go to match the word doc?
View 3 Replies
View Related
Aug 26, 2014
I have a Query which can pull data to a form, then the form will evaluate the ID, after saving/recording, I need to delete the evaluated data on the query.
My problem is how can i delete it? There is Table1 for outputing the evaluation, Query1 where the evaluation can get the Data.
Process Flow:
> Form pulls data from Query1
> Evaluation was saved on Table1
> Evaluated ID should also be removed on Query1
View 1 Replies
View Related
Jul 2, 2013
Is it perhaps possible to "copy" the format of an entire report and "dump" it on another? I have ample of forms and reports and I know how to use the Format Painter - but I need to alter the format of all the other physical reports.
View 5 Replies
View Related
Aug 9, 2006
hi guys. i just wanna know if the reports that will be generated by ms access can be in ms excel format.
btw, i am using ms access 2003.
thanks in advance. =)
View 2 Replies
View Related
Feb 25, 2014
I have a report bound to a query that includes zip codes as a field. Zip codes are formatted in the table as 99999-9999 and display in this format when the query is run. However, when I place that field in the report text box as
=[ContactCity] & ", " & [StateAbbreviation] & " " & [ContactZip]
or even as [ContactZip]
The zip is not formatted.
View 1 Replies
View Related
Dec 2, 2013
I have a report with a list of employee names (and other information). A handful of them need to be a different color (other than default). There will always be more than 3, so I can't use the conditional formatting tool.
View 14 Replies
View Related
Feb 18, 2014
In my tables i have used calculated fields. one of the fields is to "total expenses." In a report, i need to show the sum of all the "total expenses", the filed populates in the report but the cents are missing. for example if the amount is 6080.40 it shows as 6080. how can i get around this? I have tried changing the decimal point value to 2 at which point the value turns to 6080.00 when it should be 6080.40 (i am a beginner at this i am assuming the answer will probably involve c++ or visual basic's, two concepts i am not familiar with.)
View 2 Replies
View Related
May 14, 2004
I need to e-mail weekly reports to several parties, but both Word and Excel are not working for me because I am losing the original formatting. For the sake of consistency and readability I need to preserve the original report format, but I am totally stumped as to how to do this. Please, someone, help!
View 3 Replies
View Related
Mar 21, 2015
A report field data is sourced from a query which displays its values as a string eg "George Renee", however in the reports it displays data as a number eg 42. It is true that the query field obtains its data from a table which gets its data through lookup Wizard. I really want the report to display string type.
View 1 Replies
View Related
Apr 20, 2013
I have browsed a few forums and am still having trouble creating a pie chart that has the proper layout that I would prefer.
I have created a query that will list the data I would like to organize into a pie chart. The query, when run, will prompt the user for a start date and an end date and then display the data in this form:
EmployeeX | SumofProject hours | SumofDemo Hours | SumofAdministration hours | SumofEtc hours (5 other categories of hours)
EmployeeY | SumofProject hours | SumofDemo Hours | SumofAdministration hours | SumofEtc hours (5 other categories of hours)
OK, now that I have the data in this format I would like to create a report that includes tabs for each employee, each tab will have a pie chart that has one employee and a pie chart showing how their hours are allocated to each task. e.g. tab 1 would have Employee X and the pie chart would show what percentage of this employee's hours was dedicated to project/demo/admin/etc...
View 1 Replies
View Related
Apr 30, 2014
How do I format:
First 25% of records - Gold
Second 25% of records - Green
Third 25% of records - Yellow
Fourth 25% of records - Red
View 4 Replies
View Related
Oct 21, 2013
The first part of the request is to capitalise all the below fields, when they appear on my report in addition to removing any <null> values. I can't seem to get them both in the format area
my fields are
Title
Fname
Sname
Address1
Address2
Address3
Town
Postal
View 5 Replies
View Related
Oct 20, 2005
What code should I use to set a specific field format using the on load event.
For example my database is used by different countries. in their setup the can choose to use Euro or GBP as Currency.
When they log on I set this as a global constant (GbCurrency)
Depending on their setting when they load certain reports or forms I want certain fields to be formatted either as "currency" or as "Euro" in the format settings with the decimal place as 2.
i have enterd the following code but it doesn't work.
Any suggestions?
On load event
If gbCurrency = "Euro" Then
Me.labour_rate.Format = Euro
Me.labour_rate.DecimalPlaces = 2
end if
View 1 Replies
View Related
Feb 25, 2005
Can anyone help?!
We are trying to export some reports into a Rich Text format using Access on Office 2003 on a Win XP op system. When doing this on Win 98 the Rich text document was slightly out of line but not too bad. Now we are doing it on XP it is completely out of line and unusable. Does anyone have any suggestions please?
Regards
Richard
View 3 Replies
View Related
Mar 27, 2014
When you create a column chart and then change one of the series to a line, how can you then apply formatting to that line? I've attached a stripped down database showing what I'm trying to do. Series 1 shows individual monthly values (percentages) and series 2 shows the target which is 80% for each month.
The problem is that although I can apply formatting to the column series, I can't see any way of selecting the line series so I can set the formatting I want. I'm using Access 2003, on Win XP Professional, in case that's important.
View 3 Replies
View Related
Mar 7, 2013
In my report, I have combo boxes that display numbers (for example min_revenue and max_revenue).
In case of a "0" or "999.999", I would like to display "n/a" within the combo-boxes.
I would like to avoid doing this within the table, because I would like to keep the data type in number format.
Remark: I confused "combo-box" with "text box". I'm using text boxes, which are supplied with values from a table
View 6 Replies
View Related
Aug 1, 2013
I have a field in my report [Idle]
This has a time in a HH:MM:SS format (so 01:38:23)
What I need to be able to do is sum this greater than 24 hours and keep the format of HH:MM:SS - ideally I need to do this in the form field that I'll be using to sum it.
View 1 Replies
View Related
Sep 19, 2013
I have created a report from a query. The report contains some numeric fields.
I have set the format of the numeric fields to #,###;#,###;0;0;
Despite this setting, my report is showing numbers as #.###
View 1 Replies
View Related
