Report with error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that is populated from the results of a query.

Here is the query:

SELECT PUBLIC_CONCERN.PublicConcernOrder,
PUBLIC_CONCERN.PublicConcernNumber, PUBLIC_CONCERN.PublicConcern,
PUBLIC_CONCERN.Spclst, PUBLIC_CONCERN.Response, COMMENT.LastName,
COMMENT.FirstName, COMMENT.Organization AS Expr1, COMMENT.LetterNumber,
COMMENT.CommentNumber, COMMENT.OrgType, COMMENT.SubjectCategory,
COMMENT.Comment, COMMENT.Category2, COMMENT.[SC#], COMMENT.OrgDescr FROM
PUBLIC_CONCERN LEFT JOIN COMMENT ON PUBLIC_CONCERN.PublicConcernNumber =
COMMENT.[PC#] WHERE (((COMMENT.UseAsSampleComment)=True));


The form populates when (COMMENT.UseAsSampleComment)=True. On the form
there is also a small text box that uses data from the query to populate.
Here is its control source:

="(" & "Ltr " & [LetterNumber] & ", Cmt " & [CommentNumber] & ", " &
[OrgDescr] & ")"


When the query result is empty, (COMMENT.UseAsSampleComment)=False, the text
box displays #Error. I've tried different iif statements to report when the
query is empty but to no avail. I even tried putting the query in the iif
statement. Any suggestions?

Here are some attempts:

=IIf([COMMENT.UseAsSampleComment]=False,("No Comments selected as
Sample"),("(" & "Ltr " & [LetterNumber] & ", Cmt " & [CommentNumber] & ", " &
[OrgDescr] & ")"))

=IIf((([COMMENT].[UseAsSampleComment])=False),("No Comments selected as
Sample"),("(" & "Ltr " & [LetterNumber] & ", Cmt " & [CommentNumber] & ", " &
[OrgDescr] & ")"))

=IIf([COMMENT.UseAsSampleComment]=True,("(" & "Ltr " & [LetterNumber] & ",
Cmt " & [CommentNumber] & ", " & [OrgDescr] & ")"),("No Comments selected as
Sample"))

=IIf(IsNull((SELECT PUBLIC_CONCERN.PublicConcernOrder,
PUBLIC_CONCERN.PublicConcernNumber, PUBLIC_CONCERN.PublicConcern,
PUBLIC_CONCERN.Spclst, PUBLIC_CONCERN.Response, COMMENT.LastName,
COMMENT.FirstName, COMMENT.Organization AS Expr1, COMMENT.LetterNumber,
COMMENT.CommentNumber, COMMENT.OrgType, COMMENT.SubjectCategory,
COMMENT.Comment, COMMENT.Category2, COMMENT.[SC#], COMMENT.OrgDescr FROM
PUBLIC_CONCERN LEFT JOIN COMMENT ON PUBLIC_CONCERN.PublicConcernNumber =
COMMENT.[PC#] WHERE (((COMMENT.UseAsSampleComment)=False));)),("No Comments
selected as Sample"),("(" & "Ltr " & [LetterNumber] & ", Cmt " &
[CommentNumber] & ", " & [OrgDescr] & ")"))



Thanks in advance!
 
Dgreen,

I think it would be easiest to put the expression in the query, and then
have the textbox on the form?/report? bound to the calculated query
field, i.e....

SELECT PUBLIC_CONCERN.PublicConcernOrder,
PUBLIC_CONCERN.PublicConcernNumber, PUBLIC_CONCERN.PublicConcern,
PUBLIC_CONCERN.Spclst, PUBLIC_CONCERN.Response, COMMENT.LastName,
COMMENT.FirstName, COMMENT.Organization, COMMENT.LetterNumber,
COMMENT.CommentNumber, COMMENT.OrgType, COMMENT.SubjectCategory,
COMMENT.Comment, COMMENT.Category2, COMMENT.[SC#], COMMENT.OrgDescr,
"(Ltr " & [LetterNumber] & ", Cmt " & [CommentNumber] & ", " &
[OrgDescr] & ")" AS YourThing FROM PUBLIC_CONCERN LEFT JOIN COMMENT ON
PUBLIC_CONCERN.PublicConcernNumber = COMMENT.[PC#] WHERE
((COMMENT.UseAsSampleComment)=True);

--
Steve Schapel, Microsoft Access MVP

I have a report that is populated from the results of a query.

Here is the query:

SELECT PUBLIC_CONCERN.PublicConcernOrder,
PUBLIC_CONCERN.PublicConcernNumber, PUBLIC_CONCERN.PublicConcern,
PUBLIC_CONCERN.Spclst, PUBLIC_CONCERN.Response, COMMENT.LastName,
COMMENT.FirstName, COMMENT.Organization AS Expr1, COMMENT.LetterNumber,
COMMENT.CommentNumber, COMMENT.OrgType, COMMENT.SubjectCategory,
COMMENT.Comment, COMMENT.Category2, COMMENT.[SC#], COMMENT.OrgDescr FROM
PUBLIC_CONCERN LEFT JOIN COMMENT ON PUBLIC_CONCERN.PublicConcernNumber =
COMMENT.[PC#] WHERE (((COMMENT.UseAsSampleComment)=True));


The form populates when (COMMENT.UseAsSampleComment)=True. On the form
there is also a small text box that uses data from the query to populate.
Here is its control source:

="(" & "Ltr " & [LetterNumber] & ", Cmt " & [CommentNumber] & ", " &
[OrgDescr] & ")"


When the query result is empty, (COMMENT.UseAsSampleComment)=False, the text
box displays #Error. I've tried different iif statements to report when the
query is empty but to no avail. I even tried putting the query in the iif
statement. Any suggestions?

Here are some attempts:

=IIf([COMMENT.UseAsSampleComment]=False,("No Comments selected as
Sample"),("(" & "Ltr " & [LetterNumber] & ", Cmt " & [CommentNumber] & ", " &
[OrgDescr] & ")"))

=IIf((([COMMENT].[UseAsSampleComment])=False),("No Comments selected as
Sample"),("(" & "Ltr " & [LetterNumber] & ", Cmt " & [CommentNumber] & ", " &
[OrgDescr] & ")"))

=IIf([COMMENT.UseAsSampleComment]=True,("(" & "Ltr " & [LetterNumber] & ",
Cmt " & [CommentNumber] & ", " & [OrgDescr] & ")"),("No Comments selected as
Sample"))

=IIf(IsNull((SELECT PUBLIC_CONCERN.PublicConcernOrder,
PUBLIC_CONCERN.PublicConcernNumber, PUBLIC_CONCERN.PublicConcern,
PUBLIC_CONCERN.Spclst, PUBLIC_CONCERN.Response, COMMENT.LastName,
COMMENT.FirstName, COMMENT.Organization AS Expr1, COMMENT.LetterNumber,
COMMENT.CommentNumber, COMMENT.OrgType, COMMENT.SubjectCategory,
COMMENT.Comment, COMMENT.Category2, COMMENT.[SC#], COMMENT.OrgDescr FROM
PUBLIC_CONCERN LEFT JOIN COMMENT ON PUBLIC_CONCERN.PublicConcernNumber =
COMMENT.[PC#] WHERE (((COMMENT.UseAsSampleComment)=False));)),("No Comments
selected as Sample"),("(" & "Ltr " & [LetterNumber] & ", Cmt " &
[CommentNumber] & ", " & [OrgDescr] & ")"))



Thanks in advance!
Click to expand...
 
Back
Top