Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Date Formatting (Access 2000)

    This is a repeat question of an old problem but with a bit of a twist. I need to display a date field in either datasheet or continuous forms mode (no editing takes place with the data. it is just for display and subsequent record selection). The problem is that the date needs to to either display the date as dd-mmm or the word Complete (if the complete flag set true) or N/A (if the NA flag set true). My real problem is that in Access XP this is easy to do with nested IIF() statements in the underlying query but this does not work in Access 2000 which is the version the client is using (upgrading is not an option).

    The expression in XP is: XDate: IIF([Complete]=true,"Complete",IIF([NA]=true,"N/A",CStr(Format([DateField],"dd-mmm")))).

    I cannot use code in the form as whatever I do to the control applies to all records not just the specified record (datasheet or continuous forms).

    In Access 2000 there appear to be 2 problems. First nested IIF() statements are not allowed. This I can fix by using nested queries. i.e the first query deals with the first test and the second with the second. That also works fine. The problem that I cannot find an answer to is that the user wants the date formatted "dd-mmm" for reasons of space (there are a total of 14 of these dates to display and or print). Access 2000 does not allow the Format() function in a query expression according to the error messages I am receiving and the IIF() function changes the data type of the result so that I cannot format later. Is there any way I can fix the format of the date such that it travels through the IIF functions correctly (table formatting does not do it) or is there another way I can deal with the problem.

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

    Re: Date Formatting (Access 2000)

    I would investigate a bit further, for both nested IIf's and Format are allowed in expressions in all versions of Access at least since Access 95. Perhaps there is a references problem on the Access 2000 PC; you must test this on the client's PC with Access 2000, not on your own:
    - Activate the Visual Basic Editor (Alt+F11)
    - Select Tools | References...
    - Clear the check box for any reference starting with MISSING.
    - Click OK.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date Formatting (Access 2000)

    I have already checked for this and there are no missing references. However your point was well taken. In checking the versions further, my laptop is running Win2K and Office 2002 and it does not work. My home desktop is running Win XP and Office XP and it does work. The specific error message is:

    Function is not available in expressions in query expression and the problem seems to be strictly related to the Format() function. i.e. if I remove it the query works. The following are the libraries in the refrence list in order:

    Visual Basic for Applications
    Microsoft Access 10.0 Object Library
    Microsoft DAO 3.6 Object Library
    OLE Automation
    Microsoft ActiveX Data Objects 2.1 Library

    The only reference that is different is that the DAO library on my desktop is version 3.51 as opposed to 3.6

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

    Re: Date Formatting (Access 2000)

    The reference to DAO 3.51 is probably your problem. Office 2000 and 2002 should always reference DAO 3.6.
    Wendell

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date Formatting (Access 2000)

    Very strange. The 3.51 version was not the problem as that was on the working computer. What I did was to create a brand new test database on the laptop and try it out there. Everything worked fine. When I looked at the libraries there was an additional one MS VB for Applications Extensibility 5.3.

    When I added that reference to the problem database everything seems to work fine. What I still don't understand is why it worked without that reference library on a WinXP/Office XP machine (with the 3.51 DAO reference)

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

    Re: Date Formatting (Access 2000)

    I should have known better - the format function isn't a part of DAO. It sounds as if you have something else that requires the Applications Extensibility, and the problem wasn't actually the Format() statement but some other VBA or calculated expression. I have seen that happen, and it nearly always involves a missing reference. Strange these things!
    Wendell

  7. #7
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date Formatting (Access 2000)

    Many thanks Wendell. Every time I use this forum I am amazed and gratified by the excellent responses from the moderators.

    Is there any way of chasing down missing references when there is no actual missing error in the reference list, other than trial and error?

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

    Re: Date Formatting (Access 2000)

    Unfortunately, I don't know of any way other than plugging in references one at a time until you solve the problem, and then removing the ones that didn't solve the problem. One of the vagaries of the various Office object models.
    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
  •