Results 1 to 7 of 7

Thread: #Name? error

  1. #1
    Join Date
    May 2006
    Posts
    29

    Unanswered: #Name? error

    I have two textboxes on my report:
    one uses Trim([CITY] & " " & [State] & " " & [ZIP])
    and another uses =Format(Now(),"Long Date")

    I'm not sure why it generates #Name? error on my report. Does anyone have any idea?

    Thanks!

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Make sure you have the fields named CITY, STATE and ZIP on your report/recordsource.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    also not blank values
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    May 2006
    Posts
    29
    So should I change it to:
    Trim(iif([CITY]="","",[City]) & " " & iif([State]=""," ",[State]) & " " & iif([ZIP]=""," ",[ZIP])

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You should check to make sure these fields are in your recordsource query and on the report itself. You're getting the #Name# because it cannot find one of the fields (City, STATE or ZIP) on the report or in the query (if it's happening regardless of the data.)

    Either that or the Trim is not being recognized because you don't have the proper References or they aren't loading correctly (in which case other expressions such as LEFT, RIGHT, etc... should also fail.)
    Last edited by pkstormy; 12-25-09 at 21:53.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I suspect Myle has hit on the answer, although he didn't go into it in detail. I think you probably have no data in one or more fields and you've done nothing to account for it.

    In Access a field without data, or a field where the data has been deleted, is Null, not a Zero-length string, which is all you're checking for with

    iif([CITY]="","",[City])

    When Access encounters a Null field in trying to evaluate your expression, it doesn't know what to do with it.

    You need to be checking for Nulls using something like

    iif(IsNull([CITY],"",[City]))

    or better yet, you can simply use a trait of Null. Anything added to a Null will give you a Null, so you can use

    =Trim (([City] + " ") & (" " + [State]) & (" " + [Zip]))

    and Access will omit the missing component(s) and display the data that is present in the way you want.

    This assumes that you've checked, as pkstormy suggested, and found these fields to be a part of the report's recordset. Because of the date thing popping an error, you also need to check your references, as he suggested.

    I also expect the Trim() is unnecessary.
    Last edited by Missinglinq; 12-27-09 at 13:52.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    May 2006
    Posts
    29
    Thank you all!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •