Assuming the query works on its own (i.e. it returns the expected results
in datasheet view), do you have any text boxes that have the same names
as the fields? That can cause the #Error problem.
Since you have a query you can use the IIf expression in a new query
field. In query design view, at the top of an empty column, do something
like:
InitialName: IIf(IsNull([txtInstitution]),[txtInitial] & " " &
[txtlastname],[txtInstitution])
Then you can set the control source of the text box to InitialName (or
whatever you decide to call the query field).
In any case, you should check for name duplication. When you drag a
field from the field list onto a report it gives the control the same
name as the field. This was not one of Microsoft's better choices with
Access. Even if you use a query as I have described, duplicate names for
fields and controls can cause problems in other areas.
Tony Williams said:
This is what I have as the source of the control
=IIf(IsNull([txtInstitution]),[txtInitial] & " " &
[txtlastname],[txtInstitution])
and this is the sql of the query
SELECT tblInstitution1.txtRefNbr, tblInstitution1.txtInstitution,
tblInstitution1.txtInitial, tblInstitution1.txtlastname,
tblInstitution1.txtCountry
FROM tblInstitution1
WHERE (((tblInstitution1.txtCountry)<>"UK"));
This is the same sorce statment that I have in my other report but it
wont work in my three others the above being a typical example??????
Thanks Bruce
Tony
What is the expression that generates the #Error indication?
Bruce it worked on one report and not the others? They are all based
on queries and I've added the extra fields initial and lastname to the
queries but I'm getting#Error. Any ideas? Real odd!
Thanks
Tony
Is tblInstitution1 the report's record source? If so, there is no
need to reference it. If txtInstitution comes from a form or some
other object, you need to name the object type (table, form, etc.):
[Forms]![frmYourForm]![YourField]. You don't put quotes around the
fields; doing so causes the literal text to appear. Also, IsNull is
placed elsewhere, and needs its own parentheses:
IIf(IsNull([txtInstitution]),[txtInitial] & " " &
[txtlastname],[txtInstitution])
I thought I had cracked if statements but I'm getting a syntax error
with this
= IIf ( [tblInstitution1]![txtInstitution] isNull,"
[tblInstitution1]![txtInitial]" & " " &
"[tblInstitution1]![txtlastname]" ,
"[tblInstitution1]![txtInstitution]" )
I'm using it as a control on a report. can anyone tell me what's
wrong with this?
Thanks
Tony
