Iif Statement

[Guest]

I am trying to get the user to input the policy number if auto assign is set
to no.

wrkfld1 : Iif([AutoAssign] = "No", [Enter Policy Number])

Except I get dialog box "Enter Policy Number" even if my AutoAssign is set
to yes, which I don't want it to. Does anyone have any suggestions. Thanks
in advance.

 

[Guest]

hi Leslie,

Try this:

wrkfld1 : Iif([AutoAssign] = "No", [Enter Policy Number], [AutoAssign])

You were just missing one of the criteria for the IIf statement.

 

[Guest]

I tried that, however I still get the box asking me to enter policy number
when AutoAssign is a Yes. My code is the following:

wrkfld1: IIf([AutoAssign]="No",[Enter Policy Number],[PolicyNo])

this is an append query and the field is appended to field [PolicyNo]
--
Leslie

hi Leslie,

Try this:

wrkfld1 : Iif([AutoAssign] = "No", [Enter Policy Number], [AutoAssign])

You were just missing one of the criteria for the IIf statement.

I am trying to get the user to input the policy number if auto assign is set
to no.

wrkfld1 : Iif([AutoAssign] = "No", [Enter Policy Number])

Except I get dialog box "Enter Policy Number" even if my AutoAssign is set
to yes, which I don't want it to. Does anyone have any suggestions. Thanks
in advance.

 

[Guest]

OK. If the column in both tables is called "PolicyNo" then you may want to
precede the "PolicyNo" with a table name. For example: tblname.[PolicyNo]

I tried that, however I still get the box asking me to enter policy number
when AutoAssign is a Yes. My code is the following:

wrkfld1: IIf([AutoAssign]="No",[Enter Policy Number],[PolicyNo])

this is an append query and the field is appended to field [PolicyNo]
--
Leslie

hi Leslie,

Try this:

wrkfld1 : Iif([AutoAssign] = "No", [Enter Policy Number], [AutoAssign])

You were just missing one of the criteria for the IIf statement.

I am trying to get the user to input the policy number if auto assign is set
to no.

wrkfld1 : Iif([AutoAssign] = "No", [Enter Policy Number])

Except I get dialog box "Enter Policy Number" even if my AutoAssign is set
to yes, which I don't want it to. Does anyone have any suggestions. Thanks
in advance.

 

[Guest]

I'm sorry geebee but i only have [PolicyNo] in one table. I have also tried,
just perhaps it might work, to add tblname.[policyno] and i continue to get
the message to enter a policy number. I have also confirmed my field in the
table [AutoAssign] is "Yes". I was thinking it might have something to do
with the [] around "Enter Policy Number". Does the query think this is a
field I have in one of my tables?

--
Leslie

OK. If the column in both tables is called "PolicyNo" then you may want to
precede the "PolicyNo" with a table name. For example: tblname.[PolicyNo]

I tried that, however I still get the box asking me to enter policy number
when AutoAssign is a Yes. My code is the following:

wrkfld1: IIf([AutoAssign]="No",[Enter Policy Number],[PolicyNo])

this is an append query and the field is appended to field [PolicyNo]
--
Leslie

hi Leslie,

Try this:

wrkfld1 : Iif([AutoAssign] = "No", [Enter Policy Number], [AutoAssign])

You were just missing one of the criteria for the IIf statement.




:

I am trying to get the user to input the policy number if auto assign is set
to no.

wrkfld1 : Iif([AutoAssign] = "No", [Enter Policy Number])

Except I get dialog box "Enter Policy Number" even if my AutoAssign is set
to yes, which I don't want it to. Does anyone have any suggestions. Thanks
in advance.

 

[Guest]

Here is a sample complete APPEND query. And it works for me:

INSERT INTO test2 ( city, num2, num3, state )
SELECT test3.city, test3.num2, test3.num3, IIf([city]="ddd",[enter policy
number],[state])
FROM test3;

OK. If the column in both tables is called "PolicyNo" then you may want to
precede the "PolicyNo" with a table name. For example: tblname.[PolicyNo]

I tried that, however I still get the box asking me to enter policy number
when AutoAssign is a Yes. My code is the following:

wrkfld1: IIf([AutoAssign]="No",[Enter Policy Number],[PolicyNo])

this is an append query and the field is appended to field [PolicyNo]
--
Leslie

hi Leslie,

Try this:

wrkfld1 : Iif([AutoAssign] = "No", [Enter Policy Number], [AutoAssign])

You were just missing one of the criteria for the IIf statement.




:

I am trying to get the user to input the policy number if auto assign is set
to no.

wrkfld1 : Iif([AutoAssign] = "No", [Enter Policy Number])

Except I get dialog box "Enter Policy Number" even if my AutoAssign is set
to yes, which I don't want it to. Does anyone have any suggestions. Thanks
in advance.

 

[Guest]

I appreciate your help geebee. I guess I'll try to find a different way
around this problem.
--
Leslie

Here is a sample complete APPEND query. And it works for me:

INSERT INTO test2 ( city, num2, num3, state )
SELECT test3.city, test3.num2, test3.num3, IIf([city]="ddd",[enter policy
number],[state])
FROM test3;

OK. If the column in both tables is called "PolicyNo" then you may want to
precede the "PolicyNo" with a table name. For example: tblname.[PolicyNo]

I tried that, however I still get the box asking me to enter policy number
when AutoAssign is a Yes. My code is the following:

wrkfld1: IIf([AutoAssign]="No",[Enter Policy Number],[PolicyNo])

this is an append query and the field is appended to field [PolicyNo]
--
Leslie


:

hi Leslie,

Try this:

wrkfld1 : Iif([AutoAssign] = "No", [Enter Policy Number], [AutoAssign])

You were just missing one of the criteria for the IIf statement.




:

I am trying to get the user to input the policy number if auto assign is set
to no.

wrkfld1 : Iif([AutoAssign] = "No", [Enter Policy Number])

Except I get dialog box "Enter Policy Number" even if my AutoAssign is set
to yes, which I don't want it to. Does anyone have any suggestions. Thanks
in advance.

 

[Guest]

Please post your entire APPEND query, so I can take a look. And NEVER give
up until you have tried all viable solutions.

I appreciate your help geebee. I guess I'll try to find a different way
around this problem.
--
Leslie

Here is a sample complete APPEND query. And it works for me:

INSERT INTO test2 ( city, num2, num3, state )
SELECT test3.city, test3.num2, test3.num3, IIf([city]="ddd",[enter policy
number],[state])
FROM test3;

OK. If the column in both tables is called "PolicyNo" then you may want to
precede the "PolicyNo" with a table name. For example: tblname.[PolicyNo]



:

I tried that, however I still get the box asking me to enter policy number
when AutoAssign is a Yes. My code is the following:

wrkfld1: IIf([AutoAssign]="No",[Enter Policy Number],[PolicyNo])

this is an append query and the field is appended to field [PolicyNo]
--
Leslie


:

hi Leslie,

Try this:

wrkfld1 : Iif([AutoAssign] = "No", [Enter Policy Number], [AutoAssign])

You were just missing one of the criteria for the IIf statement.




:

I am trying to get the user to input the policy number if auto assign is set
to no.

wrkfld1 : Iif([AutoAssign] = "No", [Enter Policy Number])

Except I get dialog box "Enter Policy Number" even if my AutoAssign is set
to yes, which I don't want it to. Does anyone have any suggestions. Thanks
in advance.

 

[raskew via AccessMonster.com]

Hi -
Is [AutoAssign] a boolean field? If so, try changing "Yes" to True (no
quotes).

Bob

Please post your entire APPEND query, so I can take a look. And NEVER give
up until you have tried all viable solutions.

I appreciate your help geebee. I guess I'll try to find a different way
around this problem.

[quoted text clipped - 32 lines]

 

[Guest]

This is my Append Query -
INSERT INTO [ZRP - All Policies] ( Carrier, CustomerNo, ApplicationNo,
PolicyNo )
SELECT [Application Information].Carrier, [Application
Information].CustomerNo, [Application Information].ApplicationNo,
First(IIf([AutoAssign]="No",[Enter Policy Number],[Application
Information].PolicyNo)) AS wrkfld1
FROM [Application Information], [ZRP - All Policies];

I know the brackets are not around PolicyNo on the if stmt but when I go
back into query they have been removed by access not me.
--
Leslie

Please post your entire APPEND query, so I can take a look. And NEVER give
up until you have tried all viable solutions.

I appreciate your help geebee. I guess I'll try to find a different way
around this problem.
--
Leslie

Here is a sample complete APPEND query. And it works for me:

INSERT INTO test2 ( city, num2, num3, state )
SELECT test3.city, test3.num2, test3.num3, IIf([city]="ddd",[enter policy
number],[state])
FROM test3;



:

OK. If the column in both tables is called "PolicyNo" then you may want to
precede the "PolicyNo" with a table name. For example: tblname.[PolicyNo]



:

I tried that, however I still get the box asking me to enter policy number
when AutoAssign is a Yes. My code is the following:

wrkfld1: IIf([AutoAssign]="No",[Enter Policy Number],[PolicyNo])

this is an append query and the field is appended to field [PolicyNo]
--
Leslie


:

hi Leslie,

Try this:

wrkfld1 : Iif([AutoAssign] = "No", [Enter Policy Number], [AutoAssign])

You were just missing one of the criteria for the IIf statement.




:

I am trying to get the user to input the policy number if auto assign is set
to no.

wrkfld1 : Iif([AutoAssign] = "No", [Enter Policy Number])

Except I get dialog box "Enter Policy Number" even if my AutoAssign is set
to yes, which I don't want it to. Does anyone have any suggestions. Thanks
in advance.

 

[Guest]

No [AutoAssign] is a text field. In the table, I did a lookup for "Yes" or
"No". Field is defaulted to "No".
--
Leslie

Hi -
Is [AutoAssign] a boolean field? If so, try changing "Yes" to True (no
quotes).

Bob

Please post your entire APPEND query, so I can take a look. And NEVER give
up until you have tried all viable solutions.

I appreciate your help geebee. I guess I'll try to find a different way
around this problem.

[quoted text clipped - 32 lines]

to yes, which I don't want it to. Does anyone have any suggestions. Thanks
in advance.

 

[Guest]

hi,

Thanks. Earlier, you said that [policyno] was not in both tables. but
looking at your query, it looks like it is in both tables. if it is NOT in
both tables, Access is throwing up a towel, and saying "huh?". So thats why
it is asking you to enter [policyno]. I noticed also that you have an
aggregate "First" expression in your query. This is not necessary. The IF
statement is used to determine the value for EACH ROW in the column. So no
need to use the First keyword here. So your amended query would be (which
works for me, assuming that I have [policyno] in both tables:

INSERT INTO test2 ( autoassign, policyno )
SELECT test3.autoassign, IIf([test3].[AutoAssign]="No",[Enter Policy
Number],[test3].[PolicyNo]) AS Expr1
FROM test3;


INSERT INTO [ZRP - All Policies] ( Carrier, CustomerNo, ApplicationNo,
PolicyNo )
SELECT [Application Information].Carrier, [Application
Information].CustomerNo, [Application Information].ApplicationNo,
IIf([AutoAssign]="No",[Enter Policy Number],[Application
Information].PolicyNo) AS wrkfld1
FROM [Application Information];

PS: Another recommendation would be to avoid using dashes in entity names.
In this case you use a dash in the "ZRP - All Policies" table name. I would
recommend either "ZRP_All_Policies" or "ZRPAllPolicies".

This will help in the future, preventing Access from thinking it has to do
some sort of subtraction.

Feel free to contact me directly. In the meantime, if this does not work, I
am curious as to why you need to use "First" in the query. This should not
be necessary.

Hope this helps!

This is my Append Query -
INSERT INTO [ZRP - All Policies] ( Carrier, CustomerNo, ApplicationNo,
PolicyNo )
SELECT [Application Information].Carrier, [Application
Information].CustomerNo, [Application Information].ApplicationNo,
First(IIf([AutoAssign]="No",[Enter Policy Number],[Application
Information].PolicyNo)) AS wrkfld1
FROM [Application Information], [ZRP - All Policies];

I know the brackets are not around PolicyNo on the if stmt but when I go
back into query they have been removed by access not me.
--
Leslie

Please post your entire APPEND query, so I can take a look. And NEVER give
up until you have tried all viable solutions.

I appreciate your help geebee. I guess I'll try to find a different way
around this problem.
--
Leslie


:

Here is a sample complete APPEND query. And it works for me:

INSERT INTO test2 ( city, num2, num3, state )
SELECT test3.city, test3.num2, test3.num3, IIf([city]="ddd",[enter policy
number],[state])
FROM test3;



:

OK. If the column in both tables is called "PolicyNo" then you may want to
precede the "PolicyNo" with a table name. For example: tblname.[PolicyNo]



:

I tried that, however I still get the box asking me to enter policy number
when AutoAssign is a Yes. My code is the following:

wrkfld1: IIf([AutoAssign]="No",[Enter Policy Number],[PolicyNo])

this is an append query and the field is appended to field [PolicyNo]
--
Leslie


:

hi Leslie,

Try this:

wrkfld1 : Iif([AutoAssign] = "No", [Enter Policy Number], [AutoAssign])

You were just missing one of the criteria for the IIf statement.




:

I am trying to get the user to input the policy number if auto assign is set
to no.

wrkfld1 : Iif([AutoAssign] = "No", [Enter Policy Number])

Except I get dialog box "Enter Policy Number" even if my AutoAssign is set
to yes, which I don't want it to. Does anyone have any suggestions. Thanks
in advance.