Is there a limit to the amount of characters in a forumla?

E

essseeproductions

Hi, basically im trying to enter the following into a Query title. It
seems to paste correctly but then part at the end gets deleted when i
click away. I assume this is because there is a limit?
If so can anybody suggest away around my complex formula because it is
very large. (Maybe another method/forumla/re-arrange etc) See below:

Current Grade: Switch([City]=London,IIf([Pop] Between 1 And 3,"Small",
IIf([Pop] Between 3 And 5,"Med", IIf([Pop] Between 5 And 7,"Large"))),
[City]=Rome,IIf([Pop] Between 1 And 3,"Small",IIf([Pop] Between 3 And
5,"Med",IIf([Pop] Between 5 And 7,"Large"))))

Note: The actual formula contains about 5 more cases of cities and is
therefore much longer, hence why i think there is a limit. I have cut
it down to make it easier to read and understand.

Many thanks in advance to all help recieved it is greatly appreciated.
 
S

strive4peace

rather than writing such a long equation in the field cell, why not
create a user-defined function (UDF) in a general module?

'~~~~~~~~~~~`
Function GetGrade( _
pCity as string _
, pPop as long _
) as string

select case pCity
case "London", "Rome"
select case pPop
case > 5
GetGrade = "Large"
case > 3
GetGrade = "Med"
case else
GetGrade = "Small"
end select
case "SomeOtherCity"
select case pPop
case > 4
GetGrade = "Large"
case > 2
GetGrade = "Med"
case else
GetGrade = "Small"
end select
case else
GetGrade = "undefined"
end select
end function
'~~~~~~~~~~~~

in a query, you would call the function as follows:

field --> Current Grade: GetGrade(City, Pop)

where City and Pop are both fieldnames
I have assumed Long Integer data type for Pop -- you can adjust that
accordingly
~~~


How to Create a General Module

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in

once the code is in the module sheet, do
Debug,Compile from the menu

if there are no syntax/reference errors, nothing will appear to happen
-- this is good ;)

Make sure to give the module a good name when you save it. You can have
several procedures in a module, so I like to group them.


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
E

essseeproductions

Many thanks Crystal, thankyou for your solution and help in educating
me on VB and UDF's. Everything has gone well in compiling the Module
in VB. I have saved it, however on entering Current Grade:
GetGrade(City, Pop) into the field box on my query and try to run the
query an error message comes up stating "undefined function 'getgrade'
in expression. Do you know why this may be, again many thanks it is
really appreciated.
Hope you have a nice day.
 
E

essseeproductions

Sorry please ignore above post i have solved problem just renamed it
to something else instead of GetGrade.
Again many thanks for your generous help!!!!!
 
S

strive4peace

you're welcome ;) UDFs are a great tool for reducing hairball equations
-- you can do the same in Excel too ;)

I send out the first 3 chapters of a book I am writing on programming
with VB to all who request it -- email me if you are interested and I
will send it

~~~

You could be having a name conflict with GetGrade ... do you have any
other objects named that? Field, Form, Table, etc ?


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
S

strive4peace

you're welcome ;) happy to help


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top