#Name? Error on Report

Question

So I have two reports, rptApple and rptBanana; two queries, qryApple and qry Banana; and one form frmStrawberry... Both reports and queries use some of the same fields and some fields that only appear on one or the other report... The form contains all fields so that I don't have to enter the information in two different places and I don't have to enter the same information twice for each report... One of the shared fields is FINISH DATE which has a pretty lengthy Calculated Control for the Control Source... I'm getting the correct displaying information on rptApple, but rptBanana keeps showing the #Name? error... And I cannot for the life of me figure out why!!!!! I feel like I've tried everything!!!! Please help!!!

Answer 1

This is what is easier to use:

=IIf([finish1]=#12/25/2005#,"TBD",(IIf([Finish1]=#12/25/2000#,"ASAP",(IIf(Finish1=#10/1/2000#,"N/A",Month(Finish1) & "/" & Day(Finish1))))))

If you want to encapsulate the expression, it would involve creating a new standard module and paste the following code. Save the module with a name like "modBusinessCalcs"

Public Function GetFinishDate(datFinish as Date) as String

' this function will accept the Finish1 field and 

'     return the Finish Date expression

' use the function in a query or control source like:

'   =GetFinishDate([Finish1])

'   FinishDate: GetFinishDate([Finish1])



Select Case datFinish

    Case #12/25/2005#

        GetFinishDate = "TBD"

    Case #12/25/2000#

        GetFinishDate = "ASAP"

    Case #10/1/2000#

        GetFinishDate = "N/A"

    Case Else

        GetFinishDate = Month(datFinish) & "/" & Day(datFinish)

End Select

End Function

Answer 2

Can you tell us if you have a field in your record source named "FinishDate"? Also, please post the text from the control source into a reply. The images are barely adequate.

Also, these expressions are overly complex and should possibly be replaced by a user-defined function. We can help with this but we need the text from the control source, not the image.

Answer 3

Hi dhookom,

I should have specified in my question that I'm a self-taught Access user and still pretty new...

I don't know was an SQL is (I guess "pretty new" is an understatement)... But here are some screen shots of the properties and the Calculated Controls...

rptApple:

rptBanana:

Answer 4

Hi K.Clapp,

It's difficult to determine without seeing any SQL or screen shots. One issue where I see #Name is when a control source contains an expression like:

=...

and the actual name of the control is the name of a field in the report's record source. This can be corrected by changing the name of the text box to something like:

txtFinishDate

If this isn't your issue, please provide the significant properties of the control as well as the SQL of the report's record source.