create new field in query not working?

[Geoff Cox]

Hello

I am using

RenewalCost: IIf([1118] Is Not Null,[renewal-costs].school_1118,"0")

the idea being to create a new field called RenwalCost such that if
the 1118 field is set to yes (a yes/no field), then the value for the
renewal cost is taken from the field school_118 in the table called
renewal-costs.

I'm not sure that I have the correct way of detecting a "yes" and
there may well be other errors!

Cheers

Geoff

 

[Rick Brandt]

Hello

I am using

RenewalCost: IIf([1118] Is Not Null,[renewal-costs].school_1118,"0")

the idea being to create a new field called RenwalCost such that if
the 1118 field is set to yes (a yes/no field), then the value for the
renewal cost is taken from the field school_118 in the table called
renewal-costs.

I'm not sure that I have the correct way of detecting a "yes" and
there may well be other errors!

No, you are testign for Null and a YesNo field cannto contain Null. Use...

RenewalCost: IIf([1118] = True, [renewal-costs].school_1118, "0")

....which can actually be shortened to...

RenewalCost: IIf([1118], [renewal-costs].school_1118, "0")

 

[Geoff Cox]

Hello

I am using

RenewalCost: IIf([1118] Is Not Null,[renewal-costs].school_1118,"0")

the idea being to create a new field called RenwalCost such that if
the 1118 field is set to yes (a yes/no field), then the value for the
renewal cost is taken from the field school_118 in the table called
renewal-costs.

I'm not sure that I have the correct way of detecting a "yes" and
there may well be other errors!

No, you are testign for Null and a YesNo field cannto contain Null. Use...

RenewalCost: IIf([1118] = True, [renewal-costs].school_1118, "0")

...which can actually be shortened to...

RenewalCost: IIf([1118], [renewal-costs].school_1118, "0")

Thanks Rick but if I use

RenewalCost: IIf([1118],[renewal-costs].school_1118,"0")

and run the query it asks ME for a value for
renewal-costs.school_1118!?

Cheers

Geoff

 

[Geoff Cox]

Thanks Rick but if I use

RenewalCost: IIf([1118],[renewal-costs].school_1118,"0")

and run the query it asks ME for a value for
renewal-costs.school_1118!?

That indicates that Access cannot find such a field in the tables feeding the
query. I'm not sure your syntax is correct on that. Try...

RenewalCost: IIf([1118],[renewal-costs]![school_1118],"0")

I tried

RenewalCost: IIf([1118],[renewal-costs]![school_1118],"0")

is the ! a typo? any way I tried it and

RenewalCost:
IIf([renewal-end-nov-07-test].[1118]=True,[renewal-costs].[school_1118],"0")

and still get asked the question?!

Cheers

Geoff

 

[Rick Brandt]

I tried

RenewalCost: IIf([1118],[renewal-costs]![school_1118],"0")

is the ! a typo?

Nope. Just an alternative syntax that Access can use.

any way I tried it and

RenewalCost:
IIf([renewal-end-nov-07-test].[1118]=True,[renewal-costs].[school_1118],"0")

and still get asked the question?!

Does your query actually contain a table named "renewal-costs"? If so does it
actually contain a field named "school_1118".

Are you sure your dash should not be an underscore or that the underscore should
not be a dash?

If Access cannot find the field being referenced it will treat it as a parameter
marker and ask you for its value with a prompt.

 

[Geoff Cox]

Does your query actually contain a table named "renewal-costs"? If so does it
actually contain a field named "school_1118".

Are you sure your dash should not be an underscore or that the underscore should
not be a dash?

no - have checked and the names are correct. I tried a simple query
based on the renewal-costs table and the school_1118 field and that
worked OK.

??!!

Cheers

Geoff

 

[Douglas J. Steele]

no - have checked and the names are correct. I tried a simple query
based on the renewal-costs table and the school_1118 field and that
worked OK.

Try simply

RenewalCost: IIf([1118],[school_1118],"0")

Actually, if [school_1118] is a numeric field, you want

RenewalCost: IIf([1118],[school_1118],0)

 

[Geoff Cox]

no - have checked and the names are correct. I tried a simple query
based on the renewal-costs table and the school_1118 field and that
worked OK.

Try simply

RenewalCost: IIf([1118],[school_1118],"0")

Actually, if [school_1118] is a numeric field, you want

RenewalCost: IIf([1118],[school_1118],0)

Douglas,

I haev tried a create field etc in a query which has the 1118 and 1116
fields in it - and the new field gives 100 if the first and 85 if the
second. That works so the problem seems to be with knowing how to get
these figures out of the school_1118 and school_1116 fields in the
table called renewal-costs.

I have been using [renewal-costs].school_1118 etc and this doesn't
seem to be correct.

??!!

Cheers

Geoff

 

[Rick Brandt]

Douglas,

I haev tried a create field etc in a query which has the 1118 and 1116
fields in it - and the new field gives 100 if the first and 85 if the
second. That works so the problem seems to be with knowing how to get
these figures out of the school_1118 and school_1116 fields in the
table called renewal-costs.

I have been using [renewal-costs].school_1118 etc and this doesn't
seem to be correct.

And just to be clear...in the top of your query where the tables are shown you
DO have a table named renewal-costs right?

 

[Geoff Cox]

Douglas,

I haev tried a create field etc in a query which has the 1118 and 1116
fields in it - and the new field gives 100 if the first and 85 if the
second. That works so the problem seems to be with knowing how to get
these figures out of the school_1118 and school_1116 fields in the
table called renewal-costs.

I have been using [renewal-costs].school_1118 etc and this doesn't
seem to be correct.

And just to be clear...in the top of your query where the tables are shown you
DO have a table named renewal-costs right?

good grief! I do now.

but still get the question as before.

do I need to pull the fields down or just address them correctly?

Cheers

Geoff

 

[Geoff Cox]

Douglas,

I haev tried a create field etc in a query which has the 1118 and 1116
fields in it - and the new field gives 100 if the first and 85 if the
second. That works so the problem seems to be with knowing how to get
these figures out of the school_1118 and school_1116 fields in the
table called renewal-costs.

I have been using [renewal-costs].school_1118 etc and this doesn't
seem to be correct.

And just to be clear...in the top of your query where the tables are shown you
DO have a table named renewal-costs right?

Rick,

if I run the query before adding the renewal-costs table it runs
correctly.

After adding the table - I just get an empty row. The 2 tables are
joined by IDs but have I to do something else?

Geoff

 

[John W. Vinson]

After adding the table - I just get an empty row. The 2 tables are
joined by IDs but have I to do something else?

Please post the SQL of your query. No way anyone can solve a problem that they
can't see!

John W. Vinson [MVP]

 

[Geoff Cox]

Please post the SQL of your query. No way anyone can solve a problem that they
can't see!

John W. Vinson [MVP]

John,

I am trying a different approach which might give a better idea of
what I want.

Instead of trying to get the costs from a different table I am putting
them into the create new field bit.
RenewalCost:
iif([1118] and [type]="a",100,
iif([1118] and [type]="c",60,
iif([1118] and [type]="d",130,
iif([1116] and [type]="a",85,
iif([1116] and [type]="c",50,
iif([1116] and [type]="d",100,
iif([1618] and [type]="a",85,
iif([1618] and [type]="c",50,
iif([1618] and [type]="d",100,
iif([1118plus] and [type]="a",100,
iif([1118plus] and [type]="c",60,
iif([1118plus] and [type]="d",130,
iif([1116plus] and [type]="a",85,
iif([1116plus] and [type]="c",50,
iif([1116plus] and [type]="d",100,0
))))))))))))))))

Problem now is that I get error saying "too complex"!

My guess is that there are just too many characters?

Is there a shorter way of doing this?

Or is there some other error?

Cheers

Geoff

 

[Geoff Cox]

On Wed, 21 Nov 2007 15:10:50 -0500, "Douglas J. Steele"

Douglas,

Thanks for your help in my other post - just to let others know I am
happy now with this different soloution. If anyone can tell me where I
was wrong in trying to get the info from another table I won't object!

Renewal_cost:
IIF([1118], Switch([type]="a",100,[type]="c",60,[type]="d",130),
IIF([1116], Switch([type]="a",85,[type]="c",50,[type]="d",100),
IIF([1618], Switch([type]="a",85,[type]="c",50,[type]="d",100),
IIF([1118plus], Switch([type]="a",100,[type]="c",60,[type]="d",130),
IIF([1116plus],
Switch([type]="a",85,[type]="c",50,[type]="d",100),0)))))

Cheers

Geoff

 

[John W. Vinson]

Instead of trying to get the costs from a different table I am putting
them into the create new field bit.
RenewalCost:
iif([1118] and [type]="a",100,
iif([1118] and [type]="c",60,
iif([1118] and [type]="d",130,
iif([1116] and [type]="a",85,
iif([1116] and [type]="c",50,
iif([1116] and [type]="d",100,
iif([1618] and [type]="a",85,
iif([1618] and [type]="c",50,
iif([1618] and [type]="d",100,
iif([1118plus] and [type]="a",100,
iif([1118plus] and [type]="c",60,
iif([1118plus] and [type]="d",130,
iif([1116plus] and [type]="a",85,
iif([1116plus] and [type]="c",50,
iif([1116plus] and [type]="d",100,0
))))))))))))))))

Problem now is that I get error saying "too complex"!

Not surprised at all.

My guess is that there are just too many characters?

Yes, or just too complex with the nesting.

Is there a shorter way of doing this?

Not with your current apparently flawed table design. It looks like you're
storing a product or model number (1118, 1116, 1618 etc.) in the fieldnames of
several Yes/No fields. That's not a good design! If you add a new model you
must restructure your table, change all your queries, change all your Switch()
expressions... Even with that, if you need to raise or lower the cost, you now
need to burrow down into a complicated Switch() function and change the
hardcoded costs. Ouch!!!

Much better would be to store data in a field, not a fieldname. If you had a
text field named Model with values "1118", "1116", "1118plus" and so on, then
a very simple join would solve your problem with no code, no IIF's, and no
Switch().

John W. Vinson [MVP]