circular reference error message...

[Jacqueline]

In my Access 2000 mdb I am trying to create a report that
must choose the correct mailing address to send the
letter to...
The database holds 3 sets of mailing address for the
medical school applicants...Present...Permanent and Other


My problem is this...on that form there is also a
question which asks:
To which address do you want the final results sent:
Present...Permanent...Other (these are 3
checkboxes....table values 0 and -1)

My ? is: if a student checks off they want their final
results sent to...say their Present address...how do I
display the correct address in the query that runs the
report?
I know it must be some kind of IIF statement that would
need to check which of the 3 boxes are checked...but I
started with this field and am getting an error:
Circular reference caused by Alias Address1 in query
definition's Select List.


Address1: IIf([Send_Results_Pres]= -1,[Address1], IIf
([Send_Results_Perm]= -1,[Perm_Address1],
[Other_Address1]))

The sql view....
SELECT dbo_Users.User_Firstname, dbo_Users.User_Lastname,
IIf([Send_Results_Pres]=-1,[Address1],IIf
([Send_Results_Perm]=-1,[Perm_Address1],
[Other_Address1])) AS Address1
FROM dbo_Users INNER JOIN dbo_Personal ON
dbo_Users.User_ID = dbo_Personal.PersonalID;

 

[JACQUELINE]

-----Original Message-----
In my Access 2000 mdb I am trying to create a report that
must choose the correct mailing address to send the
letter to...
The database holds 3 sets of mailing address for the
medical school applicants...Present...Permanent and Other


My problem is this...on that form there is also a
question which asks:
To which address do you want the final results sent:
Present...Permanent...Other (these are 3
checkboxes....table values 0 and -1)

My ? is: if a student checks off they want their final
results sent to...say their Present address...how do I
display the correct address in the query that runs the
report?
I know it must be some kind of IIF statement that would
need to check which of the 3 boxes are checked...but I
started with this field and am getting an error:
Circular reference caused by Alias Address1 in query
definition's Select List.


Address1: IIf([Send_Results_Pres]= -1,[Address1], IIf
([Send_Results_Perm]= -1,[Perm_Address1],
[Other_Address1]))

The sql view....
SELECT dbo_Users.User_Firstname, dbo_Users.User_Lastname,
IIf([Send_Results_Pres]=-1,[Address1],IIf
([Send_Results_Perm]=-1,[Perm_Address1],
[Other_Address1])) AS Address1
FROM dbo_Users INNER JOIN dbo_Personal ON
dbo_Users.User_ID = dbo_Personal.PersonalID;


.

 

[Ted Allen]

Hi Jacqueline,

The problem appears to be that you have named your
calculated field Address1, which matches one of the
fields in the source table (either that or you
accidentally typed the field name for the present address
as Address1 when it is really something different).

If Address1 is the table's field name for the present
address, try changing the calculated fields name to
something such as SelectedAddress, or whatever you like
(that is different than the table field names). If it is
not, just change [Address1] in your iif() statement for
the present address to the correct field name.

One other observation, your iif() statement assumes that
if permanent or present addresses are not checked, then
it should be sent to other. This is ok if you are
positive that none of the records will ever accidentally
have all three fields not checked. Otherwise, you may
want to consider doing a third check to see if other is
checked, if so use other, if not you may want to default
to permanent, or you may want to do further checks to see
if the len() of permanent is > 0, otherwise check the len
of present, etc to help choose the address to be used in
case none of the fields are checked. May be a moot point
in your case, but I thought I would mention it.

Hope that helps.

-Ted Allen