Hide #Error on complicated calculated field

[GrandMasterTuck]

[SOLVED] - Hide #Error on complicated calculated field

Hi folks, I have a report with some pretty complicated calculated fields. The fields work EXACTLY as I want them to, except for when the [Text98] box = "" in the code below:

=IIf(IsNull(DLookUp("[PersonName]","qryMonthlyScheduleResults","[AssignedDate]=[Text98] And [LocationName]=[TempVars]![Loca] & ' Results AM'")),"",DLookUp("[PersonName]","qryMonthlyScheduleResults","[AssignedDate]=[Text98] And [LocationName]=[TempVars]![Loca] & ' Results AM'") & " AM")

What I want to do is hide this field if it returns a #Error, and allow it to be visible if not. I only get a #Error in this field if [Text98] is blank, and [Text98] has some complicated code, itself, to determine whether or not a date appears. Basically, the report is a weekly calendar, and in this particular field, [Text98] is the date display of the upper-left most box, which is a Sunday. If the calendar month doesn't start on a Sunday, that box should be blank, but if the month DOES start on Sunday, the box will show, for example, "4/1/2015". Again, that works perfectly, as does the field with the code block I posted, UNLESS that Sunday box shows blank (because the month begins on Monday or some other day other than Sunday), then I get a #Error with that block of code.

Is there an easy way to modify my block of code to return nothing if the value returns #Error? I've tried about ten different variations, and I think I'm getting the syntax wrong... Thanks for any suggestions you guys can give...

 

[maw230]

A quick google search returned this:

http://www.simply-access.com/Hiding-Error-Messages.html

 

[GrandMasterTuck]

Thanks, Maw, but again, the whole nested Iif was throwing me for a loop. I couldn't figure out how to get the syntax right. The way I ended up solving it was to use the tutorial on this page to create a text box that returned black squares if the condition was true, and I simply colored the text white instead of black. Works perfect. Thanks for the input, tho!

https://sites.google.com/site/msaccesscode/controls-2/conditionalformattingvisibility

 

[maw230]

Glad you got it sorted. Those nested IIFs can get out of hand sometimes.