storing formula to use

[canberry]

Hi All
I have an Issue where my query is too long as I am prompted with "The string returned by the builder was too long".
So is there a way i could store a formula that is 770 characters and refer to that formula in next query to cut down on characters in the main query.

 

[MSAccessRookie]

Hi All
I have an Issue where my query is too long as I am prompted with "The string returned by the builder was too long".
So is there a way i could store a formula that is 770 characters and refer to that formula in next query to cut down on characters in the main query.

Sure. Two quick possibilities come to mind:

1. The results of any query look just like a table, so if you break your Query up into Subqueries, you can link them together in a Master Query that gives you the results that you want.
   • Advantages: You will be able to do much more before you hit any formula limitations again.
   • DisAdvantages: You need to determine how to break up your Query. This May or may not be a lot of work.
2. If your Query uses TableName.ColumnName structures, then creating Aliases for the tablenames would reduce the number of characters.
   • Advantages: You would not need to change the basic Structure of your code.
   • DisAdvantages: The amount of characters saved depends on both the number of Table references, and the Length of the Table names as compared to the Aliases

If you get stuck and you can post the Query with the problem, perhaps we can assist you even more.

 

[canberry]

This is the code below. The 2 parts is what I want to store somewhere else to be used so this query wont be so long.So I would end up with something like (NewTableorQuery)*.42 + (NewTableorQuery2)*.1*.1*.1 when making the reference

(Iif(([Query2]![AvgOfDRE YTD AVG SALE Int]+ [Query2]![AvgOfWIGGS YTD AVG Sale Int]+ [Query2]![AvgOfJUBBA YTD AVG Sale]+ [Query2]![AvgOfMugz YTD AVG Sale])>0, (IIf(IsNull([Query2]![AvgOfDRE YTD AVG SALE Int]),0,[Query2]![AvgOfDRE YTD AVG SALE Int])+IIf(IsNull([Query2]![AvgOfWIGGS YTD AVG Sale Int]),0,[Query2]![AvgOfWIGGS YTD AVG Sale Int])+IIf(IsNull([Query2]![AvgOfJUBBA YTD AVG Sale]),0,[Query2]![AvgOfJUBBA YTD AVG Sale])+IIf(IsNull([Query2]![AvgOfMugz YTD AVG Sale]),0,[Query2]![AvgOfMugz YTD AVG Sale]))/(IIf([Query2]![AvgOfDRE YTD AVG SALE Int]>0,1,0)+IIf([Query2]![AvgOfWIGGS YTD AVG Sale Int]>0,1,0)+IIf([Query2]![AvgOfJUBBA YTD AVG Sale]>0,1,0)+IIf([Query2]![AvgOfMugz YTD AVG Sale]>0,1,0)),0)*.42)+
(Iif(([Query2]![AvgOfWIGGS YTD AVG Sale Local]+ [Query2]![AvgOfDRE YTD AVG SALE Local])>0,(IIf(IsNull([Query2]![AvgOfWIGGS YTD AVG Sale Local]),0,[Query2]![AvgOfWIGGS YTD AVG Sale Local])+IIf(IsNull([Query2]![AvgOfDRE YTD AVG SALE Local]),0,[Query2]![AvgOfDRE YTD AVG SALE Local]))/(IIf([Query2]![AvgOfWIGGS YTD AVG Sale Local]>0,1,0)+IIf([Query2]![AvgOfDRE YTD AVG SALE Local]>0,1,0)),0)*.1)*.1*.1

 

[MSAccessRookie]

This is the code below. The 2 parts is what I want to store somewhere else to be used so this query wont be so long.So I would end up with something like (NewTableorQuery)*.42 + (NewTableorQuery2)*.1*.1*.1 when making the reference

(Iif(([Query2]![AvgOfDRE YTD AVG SALE Int]+ [Query2]![AvgOfWIGGS YTD AVG Sale Int]+ [Query2]![AvgOfJUBBA YTD AVG Sale]+ [Query2]![AvgOfMugz YTD AVG Sale])>0, (IIf(IsNull([Query2]![AvgOfDRE YTD AVG SALE Int]),0,[Query2]![AvgOfDRE YTD AVG SALE Int])+IIf(IsNull([Query2]![AvgOfWIGGS YTD AVG Sale Int]),0,[Query2]![AvgOfWIGGS YTD AVG Sale Int])+IIf(IsNull([Query2]![AvgOfJUBBA YTD AVG Sale]),0,[Query2]![AvgOfJUBBA YTD AVG Sale])+IIf(IsNull([Query2]![AvgOfMugz YTD AVG Sale]),0,[Query2]![AvgOfMugz YTD AVG Sale]))/(IIf([Query2]![AvgOfDRE YTD AVG SALE Int]>0,1,0)+IIf([Query2]![AvgOfWIGGS YTD AVG Sale Int]>0,1,0)+IIf([Query2]![AvgOfJUBBA YTD AVG Sale]>0,1,0)+IIf([Query2]![AvgOfMugz YTD AVG Sale]>0,1,0)),0)*.42)+
(Iif(([Query2]![AvgOfWIGGS YTD AVG Sale Local]+ [Query2]![AvgOfDRE YTD AVG SALE Local])>0,(IIf(IsNull([Query2]![AvgOfWIGGS YTD AVG Sale Local]),0,[Query2]![AvgOfWIGGS YTD AVG Sale Local])+IIf(IsNull([Query2]![AvgOfDRE YTD AVG SALE Local]),0,[Query2]![AvgOfDRE YTD AVG SALE Local]))/(IIf([Query2]![AvgOfWIGGS YTD AVG Sale Local]>0,1,0)+IIf([Query2]![AvgOfDRE YTD AVG SALE Local]>0,1,0)),0)*.1)*.1*.1

This was not exactly what I was expecting. You appear to be providing code for Design Mode, not SQL Mode (I was expecting to see a Select Statement). I have queries that are significantly larger than this one. I always work on them in SQL Mode. Nevertheless, I still have some observations:

1. Creating Aliases will not provide you enough relief to make it worthwhile (unless you are looking for 20 or less characters).
2. A SubQuery, should be a viable option, and might not be too much work.
3. Some of your Column/Field Names are over 20 characters in Length. changing the names could provide a significant reduction in the number of characters.

 

[canberry]

I didnt want to resort to changing feild names cas that would be a huge job to rename feilds everytime data is loaded. Would it help if i posted the sql code ??

 

[MSAccessRookie]

I didnt want to resort to changing feild names cas that would be a huge job to rename feilds everytime data is loaded. Would it help if i posted the sql code ??

It would help to see the code, although since you feel that you are unable to change the Field/COlumn names, them you will probably have little choice than to split the code (assuming it can be split).

 

[boblarson]

If you have that many (and complex) IIF's you should just create a function or combination of functions and then call them as appropriate and passing them the appropriate values.

 

[canberry]

Its working like a charm now guys. I made sub-queries, and linked them back in the main query Thanks again

 

[MSAccessRookie]

If you have that many (and complex) IIF's you should just create a function or combination of functions and then call them as appropriate and passing them the appropriate values.

Bob has a good idea here, but I did not think that was the way you wanted to go. In the long run, it probably creates the greatest room for expansion in the query if it becomes necessary

Glad you got it to work by splitting it.

 

[Pat Hartman]

The underlying issue is an un-normalized schema.