Error when running report/query

[brosenfelt]

I have a query that uses the input from a form as criteria, which is then used in a report. The form input is a drop down based on another table. This is a sales pipeline report, and the list is a list of sales people. The report works perfect for all sales people except one. When I run it for the one, I get the following error:

"This expression is 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 DO NOT get the same error when running the query by itself - so assuming there is something in the report causing this. I do have some sum formulas in the report.

Again, no other salespersons selected cause this error -- so I am assuming there is something in the dataset for this person that is causing the error.

Thanks for the help!

-Brian

 

[brosenfelt]

This is the query I'm using, if this is helpful:

SELECT ParseText([pipeline]![Short Description],0) AS ARL, pipeline.Status, pipeline.[Estimated Close Date], pipeline.[Probability of Close], ParseText([pipeline]![Short Description],1) AS [BRANCH RM], ParseText([pipeline]![Short Description],2) AS DESCRIPTION, ParseText([pipeline]![Short Description],3) AS [UPDATE], ParseText([pipeline]![Short Description],4) AS UPDATE_DATE, IIf([pipeline]![Business] Is Null,[pipeline]![Contact First Name] & " " & [pipeline]![Contact Last Name],[pipeline]![Business]) AS BUSINESSNAME, BRC_RM.RM_FULL_NAME, [DESCRIPTION] & "/" & [UPDATE] AS DESCRIPTION2, pipeline.Product, pipeline.[Incr New Commitment]
FROM pipeline INNER JOIN BRC_RM ON pipeline.[Primary Officer Last Name] = BRC_RM.RM_LAST_NAME
WHERE (((BRC_RM.RM_FULL_NAME)=[Forms]![frm_rmpick]![Combo41]));

 

[brosenfelt]

This is the formula that is being used in the header of the report:

=Count(*) & " opportunities totalling " & Format(Sum([Incr New Commitment]),"$#,000")

 

[plog]

Like most things, its probably the Irish's fault.

You said it was one person causing the error, but you didn't name names. My guess is its an O'Shaughnessy, O'Malley or O'SomethingOrOther. My guess is you have a single quote in the name.

If that's the case, you probably need to escape that single quote by finding where it is in the string and putting a '\' before it.

 

[brosenfelt]

Good thought - I too would typically blame the Irish - but alas....not this time. I actually have another salesperson named "O'Malia" and his is running fine!

 

[plog]

So what's the record? Are there any weird characters in that record at all? A dash, ampersand, slash or any quote marks?

 

[brosenfelt]

There are approximately 50 records returned; lots of / characters in one of he fields--but all of the other salespeople also have / in their records

 

[brosenfelt]

/ are used as delimeters in one of the fields (we use the SPLiT function to then parse then into separate fields)

 

[brosenfelt]

Found it! One of the records had a blank field, and the parsing function was returning the value #error in the fields. Deleted that record, and the report ran properly. Just need to add some error correcting logic to my original parse formula. Whew.