Getting #Type! error in calculated field

Question

I have an unbound field "ttlAnnualRevenue" that sums two revenue fields on my form.  The underlying table has a field called "CommunityRevenueSizeID" linked to my "CommunityRevenueSize" Table.  If the "ttlAnnualRevenue" is >than 10,000,000 I want the "CommunityRevenueSizeID" to be 3.

I am using this statement in the Expression Builder window for "CommunityRevenueSizeID" and keep getting the #Type! error.  What am I doing wrong?

= IIf( [TtlAnnualRevenue] > 10000000 then [CommunityRevenueSizeID] = 3

Thanks

Rob

Answer 1

Thanks Scott

Not sure I am following :(

The relationship is set up between FirstNational (FK) table and CommunityRevenueSize table (PK).

I have an unbound text box ttlAnnualRevenue (currency) to sum the AnnualRevenueExpenditureGov (currency) and AnnualRevenueEspenditureOSR (currency).  If that total is more than 10 million I want CommunityRevenueSizeID to be equal to 3. If the total is between 5 and 9.999 million I want it to be 2 and if the total is less than 5M I want it to be 1.

Here are snags of the relationship and the text boxes on the form.

Answer 2

CommunityRevenueSizeID should be a Foreign Key in your data table. You are, apparently, binding it to the PK field of your lookup table.

Answer 3

Crap Karl - my headline is incorrect.  I am not getting the error in a calculated text box. The error is popping up in the CommunityRevenueSizeID text box (long integer). I am trying to base the result into that box using the value in the ttlRevenue textbox.

Sorry about that!!

Answer 4

Hi Karl

Sorry for using wrong terminology!

Revenue text boxes are currency. The two revenue text boxes are fields in the table FirstNational.  The unbound ttlAnnualRevenue text box is calculated by adding the two revenue boxes.

CommunityRevenueSizeID is long integer PK in the CommunityRevenueSize table.

Yes the CommunityReveueSizeID would change if the ttlAnnualRevenue rose above 10M.

Is think I know the problem but am not sure how to resolve it.  In the CommunityRevenueSizeID I have built the expression =IIf([TtlAnnualRevenue]>9999999,[CommunityRevenueSizeID]=3).

I am wondering if the expression needs to be placed somewhere else?  Or maybe it should be an If else VBA code in the CommunityRevenueSizeID text box?

The communitySize table has three fields.  An autonumber ID field and two short text fields like this:

Does that help?

Answer 5

Are you wanting to revise the data contained within CommunityRevenueSizeID field based upon the unbound field text box "ttlAnnualRevenue" that sums two revenue fields?

EDIT --

Are both of your 'revenue fields' datatype number?

Further EDIT --

Based upon your other post it seems your are trying to change data in a primary key or foreign key field.

Is my assumption correct?

Is your CommunityRevenueSizeID field a number or text field?