2048 character maximum exceeded in Control Source

[Dimeola]

I entered the following Control Source code with no problem:

=Trim(Mid$(Nz(IIf([A]=Yes,", Concrete Ramp",""))+Nz(IIf([D]=Yes,", Boarding Floats"))+Nz(IIf([E]=Yes,", Transient Floats"))+Nz(IIf([F]=Yes,", Gangway"))+Nz(IIf([G]=Yes,", Access Road"))+Nz(IIf([H]=Yes,", Paved Parking"))+Nz(IIf(=Yes,", Gravel Parking"))+Nz(IIf([J]=Yes,", Ski FLoat"))+Nz(IIf([K]=Yes,", Breakwater"))+Nz(IIf([L]=Yes,", Piling"))+Nz(IIf([M]=Yes,", Vault RR"))+Nz(IIf([N]=Yes,", Flush RR"))+Nz(IIf([P]=Yes,", Utilities"))+Nz(IIf([Q]=Yes,", Debris Boom"))+Nz(IIf([R]=Yes,", Dredging"))+Nz(IIf([W]=Yes,", Composting RR"))+Nz(IIf([X]=Yes,", Fishing Pier/Cleaning Station"))+Nz(IIf([Z]=Yes,", Fuel Station"))+Nz(IIf([AA]=Yes,", Cantilever Ramp"))+Nz(IIf([AB]=Yes,", Pole Slide"))+Nz(IIf([AC]=Yes,", Carry Down Trail"))+Nz(IIf([AD]=Yes,", Land Acquistion"))+Nz(IIf([AE]=Yes,", Gravel Ramp"))+Nz(IIf([AF]=Yes,", Showers"))+Nz(IIf([AG]=Yes,", Portable RR"))+Nz(IIf([AH]=Yes,", Riprap"))+Nz(IIf([AI]=Yes,", Overnight Moorage"))+Nz(IIf([AK]=Yes,", Boat Hoist")),2,255))

The problem that I am having is that I need to add more code, which means I will exceed the maximum 2048 characters that are allowed. Any advice? I would like to use VBA but have limited VBA syntax skills.

Thanks
Chris

 

[Fizzio]

Phew!
I'm exhausted just reading it, no wonder Access is complaining

Personally, sniffing at that controlsource, it smells of limited database design (but feel free to correct me) as you seem to have a lot of fields in one table, indicating options chosen for maybe a little cruise on the Norfolk Broads

I would be interested to see your Db Structure as I'm sure there will be a more efficient way of controling and showing your data.

 

[sportsguy]

Try this

Dim anyA, anyC, anyD, anyE, anyF, anyG, anyH, anyI, anyJ, anyK, anyL, anyM, anyN, anyP as string
Dim anyB, anyQ, anyR, anyW, anyX, anyZ, anyAA, anyAB, anyAC, anyAC, anyAD as string
Dim anyAE, anyAF, anyAG, anyAH, anyAI, anyAK, anyAE as String
Dim tmpString as String


Function Dimeola(anyA, anyB, anyC, . . . . anyAE) as String

If anyA = Yes then
tmpString = “ Concrete Ramp”
End If

If anyC = Yes then
tmpString = tmpString & “, Boarding Floats”
end If

If anyC = Yes Then
tempString = tmpString & “, Transient Floats”
End If

. . . . . . add If conditions until finished

Dimeola = Trim(tmpString)

End Function

 

[Fizzio]

SportsGuy,

Although your code will work well, I still think that the issue of hard coding the variables into code is not the best solution as you can use a lookup table with more efficiency as this allows for more versatility.

Also guess how many of the variables in this statement are actually defined as strings?

Dim anyA, anyC, anyD, anyE, anyF, anyG, anyH, anyI, anyJ, anyK, anyL, anyM, anyN, anyP as string
Dim anyB, anyQ, anyR, anyW, anyX, anyZ, anyAA, anyAB, anyAC, anyAC, anyAD as string
Dim anyAE, anyAF, anyAG, anyAH, anyAI, anyAK, anyAE as String
Dim tmpString as String

The surprising answer is 4 - all the others are given the default of Variant - each variable has to be declared explicitly.

 

[sportsguy]

Cool! thanks Fizzio

I learn something everyday!

Hey, i am in finance, and just sort of picked up some VB along the way. Never have taken a programming class, and i am old, last computer course was when punch cards and paper tapes were used. . .

i just sort of figured that i would get him started with a possible Function
that will work.

sg

 

[Fizzio]

Hey no problem. I just dabble myself with computers as a rest from people

 

[Dimeola]

Solve my 2048 max character dilemma

I ended up using two text boxes and summing the results in a third. This doubled my maximum allowable characters allowed for code. I had to come up with a quick and dirty way to generate a one time deal report. I am very happy with the results; however, I do appreciate the VBA code that was contributed and will certainly go that route in the future.

I do have another question regarding database efficiency. What is the typical maximum amount of fields recommended for a table?

Chris

 

[RV]

What is the typical maximum amount of fields recommended for a table?

See Access Help, search on specifications.

RV

 

[Pat Hartman]

What is the typical maximum amount of fields recommended for a table

Jet allows 255. Other db's allow more. I've been designing relational databases for over 30 years and except in a data warehousing application, I doubt that I've used more than 50 columns a handful of times. Most of my tables have fewer than 30 columns. I've worked with db's that had more columns. But they were always designed by someone else and most contained repeating groups and so were not properly normalized.

I find that most people who have trouble with column limits think they are making tables when really they are making spreadsheets. Yours is no exception.