Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Import External Data (2003)

    I am not sure if this is an Excel problem or an Access problem. I am trying to link some queries in an Access Database to some Excel spreadsheets. I am using Data, Import External Data, I select the MS Access Database as a data source, go to where database is and select it, then the queries appear and I select the one I want and click on the + sign to reveal the fields and I get an error message -"undefined function 'NZ' in expression. I put that query into another query thinking that that would correct the error but the new query gets the same error. I need the NZ function in the query to display 0 instead of blanks. Is there a way to get around this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Import External Data (2003)

    The import routine uses ODBC to connect to the Access database. Unfortunately, the ODBC driver does not "know" the Nz function, so you cannot import queries that use Nz directly or indirectly.

    As a workaround, you could replace Nz([FieldName],0) in the query with IIf(IsNull([FieldName]),0,[FieldName])
    Or post-process the imported table in Excel.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Import External Data (2003)

    Thanks Hans. That worked great...

Posting Permissions

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