Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

If - Then problem - Field titles 1

Status
Not open for further replies.

Cort

MIS
Apr 16, 2002
154
US
OK here's how the table I want to get information out of is designed.

year - pay 1 - pay 2 - pay 3 - pay 4 - pay 5 etc all the way to 12
02 0 0 100 0 0
01 0 100 0 0 0

It's an accounting table used to see when things were paid. ex. 02 and field 3 having 100 in it means 100 dollars was paid in March 2002 on this contract.

Here is what I'm trying to do. I want to create a parameter that queries the user to enter a year and month. Then I need an If - Then statement that references the table and pulls out any payments for that given month. Something to the extent of:

if [year] + (Right[title of Pay 1],1) = {parameter} then [Pay 1] else 0 or
if [year] + (Right[title of Pay 2],1) = {parameter} then [Pay 2] else 0, etc all the way up to Pay 12.


Is anything like this possible? Is it possible to somehow reference the title of a field in a formula?

Thanks for any help you may have.

 
Isn't this:

"if [year] + (Right[title of Pay 1],1) = {parameter} then [Pay 1] else 0 or
if [year] + (Right[title of Pay 2],1) = {parameter} then [Pay 2] else 0

the same as:

if [year] + 1 = {parameter} then [Pay 1] else 0 or
if [year] + 2 = {parameter} then [Pay 2] else 0

Anyway, I think you want (note I assume that year is a string type (use totext({year},0,"") if it isn't):

if {Table.MyYear} + "1" = {parameter} then {Table.Pay 1} else
if {Table.MyYear} + "2" = {parameter} then {Table.Pay 2}
else
etc...
else
0

-k
kai@informeddatadecisions.com
 
Isn't this:

"if [year] + (Right[title of Pay 1],1) = {parameter} then [Pay 1] else 0 or
if [year] + (Right[title of Pay 2],1) = {parameter} then [Pay 2] else 0

the same as:

if [year] + 1 = {parameter} then [Pay 1] else 0 or
if [year] + 2 = {parameter} then [Pay 2] else 0

Anyway, I think you want (note I assume that year is a string type (use totext({year},0,"") if it isn't):

if {Table.MyYear} + "1" = {parameter} then {Table.Pay 1} else
if {Table.MyYear} + "2" = {parameter} then {Table.Pay 2}
else
etc...
else
0

-k kai@informeddatadecisions.com
 
Thanks a bunch. As usual I make a problem harder than it really is and your solution works great.

2 stars if I could
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top