Hi Jerry, thanks for the help.
Here's what I do, queries, field, and create this expression;
[98]+[99]+[2000]+[2001]+[2002]+[2003]+[2004]+[2005]+[2006]+[2007]+[2008]+[2012]+[2013]+[2047]+[2047
2nd]+[2046]+[2046 2nd]+[2045]+[2045 2nd]+[2044]+[2044 2nd]+[2043]+[2043
2nd]+[2042]+[2042 2nd]+[2041]+[2040]-[1st Pay 98]-[2nd Pay 98]-[1st Pay
99]-[2nd Pay 99]-[1st Pay 2000]-[2nd Pay 2000]-[3rd Pay 2000]-[1st Pay
2001]-[2nd Pay 2001]-[3rd Pay 2001]-[1st Pay 2002]-[Pay 2nd 2002]-[Pay 3rd
2002]-[1st Pay 2003]-[Pay 2nd 2003]-[Pay 3rd 2003]+[Option
$]+[Interest]+[Special Assmnt]+[Option $03]-[1st Pay BNS]+[Reinstatement
Fee]+([BNS
Pnts]*[Fee])-[Amount1]-[Amount2]-[Amount3]-[Amount4]-[Amount5]-[Amount6]+[Sub
Total1]*[Service Charge1]+[Sub Total2]*[Service Charge2]+[Sub
Total3]*[Service Charge3]+[Sub Total4]*[Service Charge4]+[Sub
Total5]*[Service Charge5]+[Sub Total6]*[Service Charge6]+[05' INT]+[05'
LC]+[06' INT]+[06' LC]+[NSF Fee1]+[NSF Fee2]+[NSF Fee3]+[NSF Fee4]+[NSF
Fee5]+[NSF Fee6]+[Points w/out Option Fees]+[tax total 05']+[tax total
06']+[tax total 07']+[05' RC Fee]+[IVA98-01]+[TOF], it works in a form, and
use to work in the query and now as I mentioned it appears to limit the 3 of
fields in a query.
I read up on the SQL statement and tried it, like this
SELECT
[98]+[99]+[2000]+[2001]+[2002]+[2003]+[2004]+[2005]+[2006]+[2007]+[2008]+[2012]+[2013]+[2047]+[2047
2nd]+[2046]+[2046 2nd]+[2045]+[2045 2nd]+[2044]+[2044 2nd]+[2043]+[2043
2nd]+[2042]+[2042 2nd]+[2041]+[2040]-[1st Pay 98]-[2nd Pay 98]-[1st Pay
99]-[2nd Pay 99]-[1st Pay 2000]-[2nd Pay 2000]-[3rd Pay 2000]-[1st Pay
2001]-[2nd Pay 2001]-[3rd Pay 2001]-[1st Pay 2002]-[Pay 2nd 2002]-[Pay 3rd
2002]-[1st Pay 2003]-[Pay 2nd 2003]-[Pay 3rd 2003]+[Option
$]+[Interest]+[Special Assmnt]+[Option $03]-[1st Pay BNS]+[Reinstatement
Fee]+([BNS
Pnts]*[Fee])-[Amount1]-[Amount2]-[Amount3]-[Amount4]-[Amount5]-[Amount6]+[Sub
Total1]*[Service Charge1]+[Sub Total2]*[Service Charge2]+[Sub
Total3]*[Service Charge3]+[Sub Total4]*[Service Charge4]+[Sub
Total5]*[Service Charge5]+[Sub Total6]*[Service Charge6]+[05' INT]+[05'
LC]+[06' INT]+[06' LC]+[NSF Fee1]+[NSF Fee2]+[NSF Fee3]+[NSF Fee4]+[NSF
Fee5]+[NSF Fee6]+[Points w/out Option Fees]+[tax total 05']+[tax total
06']+[tax total 07']+[05' RC Fee]+[IVA98-01]+[TOF] AS Balance
FROM [Assessment Payment]; but when I go to run it, it still cuts off 1/2
the fields. Now I have no experience is SQL so maybe I did it wrong. Sure
appreciate your help!
Jerry Whittle said:
Please post the entire SQL statement.
However if you have a column for every year in a table, as you example
suggests, your database has a serious normalization issues and weird problems
will happen.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
Hi,
In the past I've had no problems with creating a huge expression to sum up
several fields. All of a sudden, when I open up the query it has cut off
several of my fields in the expression. I get of course, the syntax error
that it is missing a bracket, etc. Makes sense because it appears it has
limited room now for the number of characters in an expression.
i.e. =[98]+[99]+[2000]+[2001] and then when I run it comes up with the
error, looking like this =[98]+[99]+[2000]+[20
I've had probably up to 40 some fields in one expression and it now deletes
about 1/2 of those fields.
I'm a newbie and would sure appreciate someone's help! Thanks in advance.
