String Too Long in Query

  • Thread starter Thread starter Meg
  • Start date Start date
M

Meg

I have a complicated calculated field in a query, and the builder keeps
truncating my expression. Is there any way around this limitation? If
necessary, I can post the calculation - but the basics are a switch by the
value of class, then a switch by value of position to get the correct payout
for a car race. (so each class has the switch for position)

Any help would be GREATLY appreciated!

Meg
 
I have a complicated calculated field in a query, and the builder keeps
truncating my expression. Is there any way around this limitation? If
necessary, I can post the calculation - but the basics are a switch by the
value of class, then a switch by value of position to get the correct payout
for a car race. (so each class has the switch for position)

Any help would be GREATLY appreciated!

Meg
Click to expand...

When a calculation is too complex, either for Access or my brain, it
is helpful to put it into a VBA function in a module instead. This
way, you can structure the code (with indentation, intermediate
variables, etc.)

Then call the function from your query with the appropriate fields as
parameters.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Armen Stein said:
When a calculation is too complex, either for Access or my brain, it
is helpful to put it into a VBA function in a module instead. This
way, you can structure the code (with indentation, intermediate
variables, etc.)

Then call the function from your query with the appropriate fields as
parameters.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
Click to expand...

Not a bad idea, but I have no clue how to call a vba function for each row
for one variable (that doesn't exist in the database). Since it is a
calculation, I didn't want to have to store it. But figured I could just
calculate it in the query at run-time.

Is there somewhere you can point me to to learn how to do that
programmatically with VBA?

It would also be much appreciated.

Meg
 
I am not sure it is SQL, but here is what is in the column in the query:

payout:Val(Switch([class]="ITR",IIf(([position]=[qryracesummary]![itrcount]),0,Switch([position]=1,(.22*[qryracesummary]![itrpurse]),[position]=2,(.16*[qryracesummary]![itrpurse]),[position]=3,(.13*[qryracesummary]![itrpurse]),[position]=4,(.10*[qryracesummary]![itrpurse]),[position]=5,(.09*[qryracesummary]![itrpurse]),[position]=6,(.08*[qryracesummary]![itrpurse]),[position]=7,(.07*[qryracesummary]![itrpurse]),[position]=8,(.06*[qryracesummary]![itrpurse]),[position]=9,(.05*[qryracesummary]![itrpurse]),[position]=10,(.04*[qryracesummary]![itrpurse])),[class]="ITS",IIf(([position]=[qryracesummary]![itscount]),0,Switch([position]=1,(.22*[qryracesummary]![itspurse]),[position]=2,(.16*[qryracesummary]![itspurse]),[position]=3,(.13*[qryracesummary]![itspurse]),[position]=4,(.10*[qryracesummary]![itspurse]),[position]=5,(.09*[qryracesummary]![itspurse]),[position]=6,(.08*[qryracesummary]![itspurse]),[position]=7,(.07*[qryracesummary]![itspurse]),[position]=8,(.06*[qryracesummary]![itspurse]),[position]=9,(.05*[qryracesummary]![itspurse]),[position]=10,(.04*[qryracesummary]![itspurse])),[class]="ITA",IIf(([position]=[qryracesummary]![itacount]),0,Switch([position]=1,(.22*[qryracesummary]![itapurse]),[position]=2,(.16*[qryracesummary]![itapurse]),[position]=3,(.13*[qryracesummary]![itapurse]),[position]=4,(.10*[qryracesummary]![itapurse]),[position]=5,(.09*[qryracesummary]![itapurse]),[position]=6,(.08*[qryracesummary]![itapurse]),[position]=7,(.07*[qryracesummary]![itapurse]),[position]=8,(.06*[qryracesummary]![itapurse]),[position]=9,(.05*[qryracesummary]![itapurse]),[position]=10,(.04*[qryracesummary]![itapurse])),[class]="ITB",IIf(([position]=[qryracesummary]![itbcount]),0,Switch([position]=1,(.22*[qryracesummary]![itbpurse]),[position]=2,(.16*[qryracesummary]![itbpurse]),[position]=3,(.13*[qryracesummary]![itbpurse]),[position]=4,(.10*[qryracesummary]![itbpurse]),[position]=5,(.09*[qryracesummary]![itbpurse]),[position]=6,(.08*[qryracesummary]![itbpurse]),[position]=7,(.07*[qryracesummary]![itbpurse]),[position]=8,(.06*[qryracesummary]![itbpurse]),[position]=9,(.05*[qryracesummary]![itbpurse]),[position]=10,(.04*[qryracesummary]![itbpurse])),[class]="ITC",IIf(([position]=[qryracesummary]![itccount]),0,Switch([position]=1,(.22*[qryracesummary]![itcpurse]),[position]=2,(.16*[qryracesummary]![itcpurse]),[position]=3,(.13*[qryracesummary]![itcpurse]),[position]=4,(.10*[qryracesummary]![itcpurse]),[position]=5,(.09*[qryracesummary]![itcpurse]),[position]=6,(.08*[qryracesummary]![itcpurse]),[position]=7,(.07*[qryracesummary]![itcpurse]),[position]=8,(.06*[qryracesummary]![itcpurse]),[position]=9,(.05*[qryracesummary]![itcpurse]),[position]=10,(.04*[qryracesummary]![itcpurse])),[class]="SM",IIf(([position]=[qryracesummary]![smcount]),0,Switch([position]=1,(.22*[qryracesummary]![smpurse]),[position]=2,(.16*[qryracesummary]![smpurse]),[position]=3,(.13*[qryracesummary]![smpurse]),[position]=4,(.10*[qryracesummary]![smpurse]),[position]=5,(.09*[qryracesummary]![smpurse]),[position]=6,(.08*[qryracesummary]![smpurse]),[position]=7,(.07*[qryracesummary]![smpurse]),[position]=8,(.06*[qryracesummary]![smpurse]),[position]=9,(.05*[qryracesummary]![smpurse]),[position]=10,(.04*[qryracesummary]![smpurse]))))

I definitely had it htmlkit, with returns and indenting to figure it out,
but bunched all back together thinking maybe the returns caused the error.

Meg
 
Meg said:
Not a bad idea, but I have no clue how to call a vba function for each row
for one variable (that doesn't exist in the database). Since it is a
calculation, I didn't want to have to store it. But figured I could just
calculate it in the query at run-time.

Is there somewhere you can point me to to learn how to do that
programmatically with VBA?

It would also be much appreciated.

Meg
Click to expand...

I did not see any way to edit, so another part of the question would be will
it have access to the fields that are available to the query? Is the naming
the same? (for example races!itrpurse or results!position?

Thanks for all the help!
 
Well that is just about impossible to follow. If you were going to put it
into a function you could use something like the following
UNTESTED function. If this is written correctly you should be able to make
modifications as needed fairly easily. AND if you needed to perform the
calculation for more than one place you wouldn't have to worry about
modifying it everywhere you were using it.

Public Function FGetValue (sClass, iPosition, _
itrCount, itrPurse, _
itsCount, itsPurse, _
itaCount, itaPurse, _
itbCount, itbPurse, _
itcCount, itcPurse, _
smCount, smPurse)

Dim LCount as Long
Dim dPurse as Double

Select Case sClass
Case "ITR"
Lcount = ItrCount
dPurse = ltrPurse
Case "ITS"
LCount = itsCount
dPurse = itsPurse
Case "ITA"
Lcount = itaCount
DPurse = itaPurs
...
End Select

IF lPosition = LCount then
fGetValue = 0
Else
Select Case LPosition
Case 1
fGetValue = .22 * DPurse
Case 2
fGetValue = .16* DPurse
Case 3
...
End Select
End IF

End Function

In the query you could call that with

FGetValue([Class],[Position],[itrCount],[itrPurse], itsCount, itsPurse,
itaCount, itaPurse, itbCount, itbPurse, itcCount,itcPurse, smCount, smPurse)
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I am not sure it is SQL, but here is what is in the column in the query:

payout:Val(Switch([class]="ITR",IIf(([position]=[qryracesummary]![itrcount]),0,Switch([position]=1,(.22*[qryracesummary]![itrpurse]),[position]=2,(.16*[qryracesummary]![itrpurse]),[position]=3,(.13*[qryracesummary]![itrpurse]),[position]=4,(.10*[qryracesummary]![itrpurse]),[position]=5,(.09*[qryracesummary]![itrpurse]),[position]=6,(.08*[qryracesummary]![itrpurse]),[position]=7,(.07*[qryracesummary]![itrpurse]),[position]=8,(.06*[qryracesummary]![itrpurse]),[position]=9,(.05*[qryracesummary]![itrpurse]),[position]=10,(.04*[qryracesummary]![itrpurse])),[class]="ITS",IIf(([position]=[qryracesummary]![itscount]),0,Switch([position]=1,(.22*[qryracesummary]![itspurse]),[position]=2,(.16*[qryracesummary]![itspurse]),[position]=3,(.13*[qryracesummary]![itspurse]),[position]=4,(.10*[qryracesummary]![itspurse]),[position]=5,(.09*[qryracesummary]![itspurse]),[position]=6,(.08*[qryracesummary]![itspurse]),[position]=7,(.07*[qryracesummary]![itspurse]),[position]=8,(.06*[qryracesummary]![itspurse]),[position]=9,(.05*[qryracesummary]![itspurse]),[position]=10,(.04*[qryracesummary]![itspurse])),[class]="ITA",IIf(([position]=[qryracesummary]![itacount]),0,Switch([position]=1,(.22*[qryracesummary]![itapurse]),[position]=2,(.16*[qryracesummary]![itapurse]),[position]=3,(.13*[qryracesummary]![itapurse]),[position]=4,(.10*[qryracesummary]![itapurse]),[position]=5,(.09*[qryracesummary]![itapurse]),[position]=6,(.08*[qryracesummary]![itapurse]),[position]=7,(.07*[qryracesummary]![itapurse]),[position]=8,(.06*[qryracesummary]![itapurse]),[position]=9,(.05*[qryracesummary]![itapurse]),[position]=10,(.04*[qryracesummary]![itapurse])),[class]="ITB",IIf(([position]=[qryracesummary]![itbcount]),0,Switch([position]=1,(.22*[qryracesummary]![itbpurse]),[position]=2,(.16*[qryracesummary]![itbpurse]),[position]=3,(.13*[qryracesummary]![itbpurse]),[position]=4,(.10*[qryracesummary]![itbpurse]),[position]=5,(.09*[qryracesummary]![itbpurse]),[position]=6,(.08*[qryracesummary]![itbpurse]),[position]=7,(.07*[qryracesummary]![itbpurse]),[position]=8,(.06*[qryracesummary]![itbpurse]),[position]=9,(.05*[qryracesummary]![itbpurse]),[position]=10,(.04*[qryracesummary]![itbpurse])),[class]="ITC",IIf(([position]=[qryracesummary]![itccount]),0,Switch([position]=1,(.22*[qryracesummary]![itcpurse]),[position]=2,(.16*[qryracesummary]![itcpurse]),[position]=3,(.13*[qryracesummary]![itcpurse]),[position]=4,(.10*[qryracesummary]![itcpurse]),[position]=5,(.09*[qryracesummary]![itcpurse]),[position]=6,(.08*[qryracesummary]![itcpurse]),[position]=7,(.07*[qryracesummary]![itcpurse]),[position]=8,(.06*[qryracesummary]![itcpurse]),[position]=9,(.05*[qryracesummary]![itcpurse]),[position]=10,(.04*[qryracesummary]![itcpurse])),[class]="SM",IIf(([position]=[qryracesummary]![smcount]),0,Switch([position]=1,(.22*[qryracesummary]![smpurse]),[position]=2,(.16*[qryracesummary]![smpurse]),[position]=3,(.13*[qryracesummary]![smpurse]),[position]=4,(.10*[qryracesummary]![smpurse]),[position]=5,(.09*[qryracesummary]![smpurse]),[position]=6,(.08*[qryracesummary]![smpurse]),[position]=7,(.07*[qryracesummary]![smpurse]),[position]=8,(.06*[qryracesummary]![smpurse]),[position]=9,(.05*[qryracesummary]![smpurse]),[position]=10,(.04*[qryracesummary]![smpurse]))))

I definitely had it htmlkit, with returns and indenting to figure it out,
but bunched all back together thinking maybe the returns caused the error.

Meg

mscertified said:
post the SQL

-Dorian
Click to expand...
Click to expand...
 
Back
Top