Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Undefined Function (W2k Office 2k-SR1)

    Hi,
    I am trying to use MSQuery to retrieve some info from a database into an Excel sheet. I have a user-written function in the database (in this case to determine the last day of the current month) that is used in one of the fields of the query from which I want to display data in Excel. As soon as I attempt to expand the field list of that particular query whilst using the MSQuery wizard, I get an error that says "Undefined function 'EndOfMonth' in expression". (Screen capture attached) I have experienced this before even when a query field uses the built-in Access nz() function. Is there any way around this problem ? TIA
    Attached Images Attached Images

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

    Re: Undefined Function (W2k Office 2k-SR1)

    The standard answer is:

    Open any module
    Select Tools/References...
    See if there are any references saying Missing: ...
    If so, correct this.

    If not, post back.

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Undefined Function (W2k Office 2k-SR1)

    Thanks, but there are no references missing in either Excel or in Access

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Undefined Function (W2k Office 2k-SR1)

    If your function is in a module behind a form, make it public or, move it to a standard module.
    Francois

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

    Re: Undefined Function (W2k Office 2k-SR1)

    Sorry! I didn't look closely enough. I was answering as if your problem was in Access. Now I notice that it is (as you state quite clearly and show in your screenshot) in MS Query.
    I doubt whether MS Query can handle Access-specific features like Nz and user-defined functions. It doesn't in Office 97. Somebody else will be able to tell you if it is possible in Office 2000. Francois, perhaps?

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Undefined Function (W2k Office 2k-SR1)

    Doesn't do any better in 2000 unfortunately. I'd suggest going the DAO route instead.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Undefined Function (W2k Office 2k-SR1)

    I have tried defining it as "Public", but it was already in a standard module called "Utilities" and so should already have been available to any other modules. Maybe MSQuery can't deal with this as Hans suggested ?

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

    Re: Undefined Function (W2k Office 2k-SR1)

    Unfortunately, DAO can't handle Access-specific features either, so the CopyFromRecordset method in Excel won't help either.

    If you want to use MS Query, try to rewrite the query without the user-defined function.
    If that is not possible, you can go the other way: export the query results from Access.

  9. #9
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Undefined Function (W2k Office 2k-SR1)

    Thanks for all your help - I guess I'll have to go the export route. I have a molecular mass calculator that I wrote as a function, so that definitely won't be practical in a calculated query field !

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Undefined Function (W2k Office 2k-SR1)

    True - not sure what I was thinking about there! ADO won't help either, but I suppose you could at least automate the export from Access!
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Undefined Function (W2k Office 2k-SR1)

    Another possibility would be to use OLE Automation from Access, using Excel as the Automation Server to return the results of the calculation. That way the user only ever needs to use a single user interface. Just a thought . . . <img src=/S/thinks.gif border=0 alt=thinks width=15 height=15>
    Wendell

Posting Permissions

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