4 table linked to one form

[amperzzz]

I created a form with three linked tables. One is Condensed Analysis
the second is Fee Schedule, the 3rd is Rates, and the final one i
Totals. These tables are all linked by Calendar Year only. I'm tryin
to run an IIF code in a free standing text box on the form but canno
get it to function or give me my desired results. If I enter th
following code I get an accurate result

=sum( [Fee Schedule]![Additional 1099 (up to 770,000)]
[Rates]![Rate1] )

but if I enter any IIF or IF formula I get #NAME?

=IIf([Fee Schedule]![Additional 1099 (up to 770,000])>770000,[Fe
Schedule]![Additional 1099 (up to 770,000)]*[Rates]![Rate1],IIf([Fe
Schedule]![Additional 1099 (up to 770,000)]<770000,0))

Can anyone help

I've tried the above and the below

=IIf([Fee Schedule_Additional 1099 (up to 770,000])>770000,[Fe
Schedule_Additional 1099 (up to 770,000)]*[Rates_Rate1],IIf([Fe
Schedule_Additional 1099 (up to 770,000)]<770000,0))

I've also tried =if(([Fee Schedule_Additional 1099 (up t
770,000)>770000, "True", "False")

This also doesn't return any results for me.

If anyone can help please let me know.

Thank you,

Ada

 

[Douglas J. Steele]

You seem to have an unnecessary IIf statement inside there. Try

=IIf([Fee Schedule]![Additional 1099 (up to 770,000])>770000,[Fee
Schedule]![Additional 1099 (up to 770,000)]*[Rates]![Rate1],0)

The IIf statement has three parts: a boolean expression, what to return if
the boolean expression is True, and what to return if the boolean expression
is False.

 

[amperzzz]

This still brings back a #Name? result.
Each field in each table is labeled as a "Number" field. Should th
fields be "Text"?

You seem to have an unnecessary IIf statement inside there. Try

=IIf([Fee Schedule]![Additional 1099 (up to 770,000])>770000,[Fee
Schedule]![Additional 1099 (up to 770,000)]*[Rates]![Rate1],0)

The IIf statement has three parts: a boolean expression, what to retur
if
the boolean expression is True, and what to return if the boolea
expression
is False.

--
Doug Steele, Microsoft Access MVP
'Doug Steele's Beer and Programming Emporium' ()
(no e-mails, please!)

I created a form with three linked tables. One is Condense Analysis,
the second is Fee Schedule, the 3rd is Rates, and the final one is
Totals. These tables are all linked by Calendar Year only. I' trying
to run an IIF code in a free standing text box on the form bu cannot
get it to function or give me my desired results. If I enter the
following code I get an accurate result

=sum( [Fee Schedule]![Additional 1099 (up to 770,000)] *
[Rates]![Rate1] )

but if I enter any IIF or IF formula I get #NAME?

=IIf([Fee Schedule]![Additional 1099 (up to 770,000])>770000,[Fee
Schedule]![Additional 1099 (up to 770,000)]*[Rates]![Rate1],IIf([Fee
Schedule]![Additional 1099 (up to 770,000)]<770000,0))

Can anyone help

I've tried the above and the below

=IIf([Fee Schedule_Additional 1099 (up to 770,000])>770000,[Fee
Schedule_Additional 1099 (up to 770,000)]*[Rates_Rate1],IIf([Fee
Schedule_Additional 1099 (up to 770,000)]<770000,0))

I've also tried =if(([Fee Schedule_Additional 1099 (up to
770,000)>770000, "True", "False")

This also doesn't return any results for me.

If anyone can help please let me know.

Thank you,

Adam


--
amperzzz
------------------------------------------------------------------------
amperzzz's Profile:
'The Code Cage Forums - View Profile: amperzzz (http://www.thecodecage.com/forumz/member.php?userid=851)
View this thread:
'4 table linked to one form - The Code Cage Forums

(http://www.thecodecage.com/forumz/showthread.php?t=133835)

 

[Douglas J. Steele]

If you want to do arithmetic with them, you don't want them to be text.

Hold on, though. I just took a little closer look at what you're doing. I
hadn't made the connection that you're doing this as the ControlSource
property for a text box. All the fields to which you're referring have to be
in the form's RecordSource. You need to only refer to the fields, not the
tables from which they come:

=IIf([Additional 1099 (up to 770,000])>770000,[Additional 1099 (up to
770,000)]*[Rate1],0)

If the field names are unique in the RecordSource, you'll find that they
were aliased by Access: you'll have to use those Alias names (or else
explicitly alias them yourself)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This still brings back a #Name? result.
Each field in each table is labeled as a "Number" field. Should the
fields be "Text"?

You seem to have an unnecessary IIf statement inside there. Try

=IIf([Fee Schedule]![Additional 1099 (up to 770,000])>770000,[Fee
Schedule]![Additional 1099 (up to 770,000)]*[Rates]![Rate1],0)

The IIf statement has three parts: a boolean expression, what to return
if
the boolean expression is True, and what to return if the boolean
expression
is False.

--
Doug Steele, Microsoft Access MVP
'Doug Steele's Beer and Programming Emporium' ()
(no e-mails, please!)

I created a form with three linked tables. One is Condensed Analysis,
the second is Fee Schedule, the 3rd is Rates, and the final one is
Totals. These tables are all linked by Calendar Year only. I'm trying
to run an IIF code in a free standing text box on the form but cannot
get it to function or give me my desired results. If I enter the
following code I get an accurate result

=sum( [Fee Schedule]![Additional 1099 (up to 770,000)] *
[Rates]![Rate1] )

but if I enter any IIF or IF formula I get #NAME?

=IIf([Fee Schedule]![Additional 1099 (up to 770,000])>770000,[Fee
Schedule]![Additional 1099 (up to 770,000)]*[Rates]![Rate1],IIf([Fee
Schedule]![Additional 1099 (up to 770,000)]<770000,0))

Can anyone help

I've tried the above and the below

=IIf([Fee Schedule_Additional 1099 (up to 770,000])>770000,[Fee
Schedule_Additional 1099 (up to 770,000)]*[Rates_Rate1],IIf([Fee
Schedule_Additional 1099 (up to 770,000)]<770000,0))

I've also tried =if(([Fee Schedule_Additional 1099 (up to
770,000)>770000, "True", "False")

This also doesn't return any results for me.

If anyone can help please let me know.

Thank you,

Adam


--
amperzzz
------------------------------------------------------------------------
amperzzz's Profile:
'The Code Cage Forums - View Profile: amperzzz' (http://www.thecodecage.com/forumz/member.php?userid=851)
View this thread:
'4 table linked to one form - The Code Cage Forums' (http://www.thecodecage.com/forumz/showthread.php?t=133835)

--
amperzzz
------------------------------------------------------------------------
amperzzz's Profile:
http://www.thecodecage.com/forumz/member.php?userid=851
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=133835

 

[Douglas J. Steele]

Good catch, Bruce. I thought I'd checked for that in the original solution.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

One syntax error I noted is that the parentheses in the first line is in
the
wrong place. It is part of the field name (not a good idea, BTW), so it
needs to be inside the bracket. You have something like:

=IIf([Fee Schedule]![Additional 1099 (up to 770,000])>770000,[Fee
Schedule]![Additional 1099 (up to 770,000)]*[Rates]![Rate1],0)

It should be:

=IIf([Fee Schedule]![Additional 1099 (up to 770,000)]>770000,[Fee
Schedule]![Additional 1099 (up to 770,000)]*[Rates]![Rate1],0)

This may not be the only problem. You say there are three linked tables.
Are they all in the form's records source query? If so, try using just
the
field name:

=IIf([Additional 1099 (up to 770,000)]>770000, [Additional 1099 (up to
770,
000)]*[Rate1],0)

For a test, try the following three options as the Control Source of a
text
box:
=[Rates]![Rate1]
=[Rate1]
=Tables![Rates]![Rate1]

The last one assumes Rates is a table. In any case, this should give you
an
idea of which syntax to use in the larger expression.

You really should reconsider your field names. Names should contain
numbers,
letters, and underscores. No spaces, no special characters such as
parentheses. If you need a lot of description in a field name, try
something
like:
Addl1099_Max770k

This still brings back a #Name? result.
Each field in each table is labeled as a "Number" field. Should th
fields be "Text"?

You seem to have an unnecessary IIf statement inside there. Try

[quoted text clipped - 60 lines]

'4 table linked to one form - The Code Cage Forums
(http://www.thecodecage.com/forumz/showthread.php?t=133835)

 

[amperzzz]

Ok none of this is working for me so I must be leaving out crucial
information. Can we try something new? I have 3 tables, for this
conversation we will say table A, table B, and table C. They all
contain a "year" field and are linked in relationships by the year field
(primary key)Table A contains 1 field and the field is labeled total.
Table B contains 1 field and it is labeled allowance. TableC contains 1
field and it is labeled difference. I want to create a query with a
formula that will populate field 1 of table C with the difference
between field 1 of table A and table B.

So to recap
Query=sum([Table A_field 1]-[Table B_field 2]
Poplulate Table C field 1 (only if Years match)

The idea makes sense to me but the answer eludes me.

I created a form with three linked tables. One is Condensed Analysis,
the second is Fee Schedule, the 3rd is Rates, and the final one is
Totals. These tables are all linked by Calendar Year only. I'm trying
to run an IIF code in a free standing text box on the form but cannot
get it to function or give me my desired results. If I enter the
following code I get an accurate result

=sum( [Fee Schedule]![Additional 1099 (up to 770,000)] *
[Rates]![Rate1] )

but if I enter any IIF or IF formula I get #NAME?

=IIf([Fee Schedule]![Additional 1099 (up to 770,000])>770000,[Fee
Schedule]![Additional 1099 (up to 770,000)]*[Rates]![Rate1],IIf([Fee
Schedule]![Additional 1099 (up to 770,000)]<770000,0))

Can anyone help

I've tried the above and the below

=IIf([Fee Schedule_Additional 1099 (up to 770,000])>770000,[Fee
Schedule_Additional 1099 (up to 770,000)]*[Rates_Rate1],IIf([Fee
Schedule_Additional 1099 (up to 770,000)]<770000,0))

I've also tried =if(([Fee Schedule_Additional 1099 (up to
770,000)>770000, "True", "False")

This also doesn't return any results for me.

If anyone can help please let me know.

Thank you,

Adam

 

[John W. Vinson]

Ok none of this is working for me so I must be leaving out crucial
information. Can we try something new? I have 3 tables, for this
conversation we will say table A, table B, and table C. They all
contain a "year" field and are linked in relationships by the year field
(primary key)Table A contains 1 field and the field is labeled total.
Table B contains 1 field and it is labeled allowance. TableC contains 1
field and it is labeled difference. I want to create a query with a
formula that will populate field 1 of table C with the difference
between field 1 of table A and table B.

So to recap
Query=sum([Table A_field 1]-[Table B_field 2]
Poplulate Table C field 1 (only if Years match)

The idea makes sense to me but the answer eludes me.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, using a query joining tables A
and B. And as suggested elsethread, it's much better to use simple text for
table and fieldnames - I've seen Access do wierd things if you have
parentheses in table or fieldnames.