Expression builder telling me I made something too complex.

[mikefortune]

The following error is one I got when making what I would think is a simple expression:
Expr1: InStr([STARTPOINT],[DESC1],"-&quot
I am pretty sure it is not typed incorrectly, just that it may contain incorrect statements.

"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 am simply trying to run an Instr() using a predefined variable from another query as the start point.

Expr1: InStr([STARTPOINT],[DESC1],"-&quot

The startpoint is actually the Instr() of the same field on
another query. There are multiple "-" in the Field and I need to know the location of both. Working on this for a month now. Any ideas would be great!!!

 

[smedvid]

Sounds like you may be trying run a filter against a set of tables/views that are vast. I recall having a query that used 8-10 tables/views and a simple filter, so I thought returned a similar message. I reworked the query and found another was to get the final result. May not be much help, but remember there is always more than one way to get the answer. Steve Medvid
Atlas Commerce ("ebusiness evolved&quot

http://www.atlascommerce.com

 

[mikefortune]

I actually am only running this against one query which was run against 2 tables.
It isn't that big of a dataset.
Thanks though, I can't seem to think of another way to get the information I need from the field.

THANKS!

 

[albywalt]

What is the structure of the field and what part are you trying to extract? You could probably write a function to get what you want in one query.

Jeff

 

[mikefortune]

For instance, the field contains alphanumeric data. In that data should reside a color surrounded by dashes. For instance...
10% Flannel 30% Jersey 60% Cotton - White - Spun poly

I am looking to extract the color "White" into it's own field for a report.

 

[PaulF]

try creating a function and using that in the query to test for the data

Public Function getDashes(strData As String)
Dim i As Integer, i2 As Integer
If InStr(strData, "-&quot > 0 Then
i = InStr(strData, "-&quot
If InStr(i + 1, strData, "-&quot > 0 Then
i2 = InStr(i + 1, strData, "-&quot
getDashes = Trim(Mid(strData, i + 1, i2 - (i + 1)))
Else
getDashes = Trim(Mid(strData, i + 1))
End If
Else
getDashes = vbNullString
End If
End Function

Then in the query use an expression field to get the data

Color: GetDashes([TableName].[FieldName])

PaulF

 

[mikefortune]

Man that looks great, but when i went to run it, it gave me the error:

Undefined Function 'Getdashes' in expression.

I will be the first to admit, I am not familiar with modules and the VB side of access, but what I did was created a new module and copied what you typed into it.

(I did replace where you put Table:field name.

Did I do it wrong?

 

[PaulF]

you need to put it in a new code module, not one behind a form, and ensure it is declared as a Public Function. It worked for me when I tested it. Also ensure that you have the correct table listed in your query. Other than that I don't know what else to tell you.

PaulF

 

[PaulF]

Additional info.... I made a table named tblDashes, and it contained only one field (Field1), the Function was the same as posted, and this is the SQL behind the query

SELECT tblDashes.Field1, getDashes([field1]) AS Color
FROM tblDashes;

This is an example of the data I used and the value returned
Field1
Color
10% Flannel 30% Jersey 60% Cotton - White - Spun poly White
40% Jersey 60% Cotton - Off White - Spun poly
Off White
10% Flannel 30% Jersey 60% Cotton - Blue - Spun poly
Blue
10% Flannel 90% Jersey - Green - Spun poly
Green
100% Flannel - Green- Spun poly
Green

PaulF

 

[mikefortune]

Paul you rock.
But this just doesn't seem to work. You may need to hold my hand on this one.
I went to the modules menu, selected NEW, then I pasted what you typed into that. I already have one module in there for converting dates...it works. This one still gives me the undefined thing.

I saved it. It's there. I must have done something very stupid, can you think stupid for me and figure out what I must have done?

Thanks for all this help!

 

[mikefortune]

Oh wait, I got it to work, but now I got the same dang message about expression being to complex....

Paul can I send this database to you for you to look at and maybe see what I've done?

 

[PaulF]

post the SQL statement behind the query you are using

PaulF

 

[mikefortune]

not sure what you mean by that, sorry to be difficult.

 

[PaulF]

I used a query to display the results, what are you using? If it is a query then you can see what the SQL Statement is for the query by selecting View on the Menu then SQL View.... Copy the code that appears there, and paste it here so we can look at it.

PaulF

 

[mikefortune]

SELECT orders.QhdrProspectName AS Customer, orders.QlinItemID, dashes([QlinQuoteLines]![QlinLineDescription]) AS Expr1, orders.QhdrLocation, orders.QlinNatlNetPrice, Sum(orders.WEEK1) AS SumOfWEEK1, Sum(orders.WEEK2) AS SumOfWEEK2, Sum(orders.WEEK3) AS SumOfWEEK3, Sum(orders.WEEK4) AS SumOfWEEK4, Sum(orders.WEEK5) AS SumOfWEEK5, Sum(orders.WEEK6) AS SumOfWEEK6, Sum(orders.FUTURE) AS SumOfFUTURE, Sum(orders.QlinQuantity) AS SumOfQlinQuantity, [SumOfQlinQuantity]*[QlinNatlNetPrice] AS ExtPrice, orders.Weekend1 AS Expr6, orders.weekend2 AS Expr7, orders.weekend3 AS Expr8, orders.weekend4 AS Expr9, orders.weekend5 AS Expr10, orders.weekend6 AS Expr11
FROM orders
GROUP BY orders.QhdrProspectName, orders.QlinItemID, dashes([QlinQuoteLines]![QlinLineDescription]), orders.QhdrLocation, orders.QlinNatlNetPrice, orders.Weekend1, orders.weekend2, orders.weekend3, orders.weekend4, orders.weekend5, orders.weekend6;

 

[PaulF]

change dashes to getdashes in the following areas

dashes([QlinQuoteLines]![QlinLineDescription]) AS Expr1
to
getdashes([QlinQuoteLines]![QlinLineDescription]) AS Expr1

and

dashes([QlinQuoteLines]![QlinLineDescription])
to
getdashes([QlinQuoteLines]![QlinLineDescription])

and see what happens... also grouping on this function might be problem in this one query... you may need to skip the grouping part of the query and create another query that uses this query as its source, then do the grouping.

PaulF

 

[mikefortune]

YOU ROCK BIG TIME man!
I just put that expression into the first query and it worked great! Now, I just need to place the field in the second query and I will be set!!! I Love helpful people! It worked!!

 

[mikefortune]

oH! GOD I am so screwed!
Now, when I add that field it gives that same message about being too complex.

I take grouping off but that defeats the purpose of the query.

 

[PaulF]

I'm getting ready to leave for the day, but before I do, why add the expression to a second query... can't you use the results of the first query as a field in the second query?

PaulF

 

[mikefortune]

I am getting ready to go too. Thanks for all your help.
Maybe if you get a chance some other time you can help some more.

I tried adding it as another field, but I need them grouped. There are records with identical data except the date which makes those records need to appear as one with separate dates. I.e.:

Customer - Item# - Color - Date1 - Date2 - Date3 - Date4
John 10020 Black X o o X

Not,
Customer - Item# - Color - Date1 - Date2 - Date3 - Date4
John 10020 Black X o o o
John 10020 Black o o o x

see what I mean? So I need the grouping.