Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Location
    Kampala, Uganda
    Posts
    32

    Unanswered: Function is not available in expressions....

    I have the following line of code in a form (this is just a snippet);
    rst.Open "SELECT PO.OrderNumber, format(PO.OrderDate, 'mmmm') " & _
    " FROM tblPurchaseOrder POD " .....

    where rst is an ADO.Recordset

    The code has been around for months working happily. Now when I try and run it I get the error;
    Function is not available in expressions in query expression 'format(PO.OrderDate, 'mmmm')'.

    Also, I've used the function =format(<some date>, "dd-mmm-yyyy") in a few text boxes on some forms and they have also stopped working - now they show #NAME. If I use the expression builder to build the formula the format() function no longer appears in the list of available functions!

    I've not changed the version of Access I'm running (2002) or my OS (XP Professional) or installed any patches etc.

    Any ideas what caused it and how I can fix it?

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ?? have you (accidentally) declared a variable or sub or function or constant with name "format" ??

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Apr 2004
    Location
    Kampala, Uganda
    Posts
    32
    I did a search for an illegal definition of 'format' in all my code and couldnt find anything. Couldnt find how to fix the problem so I reverted to a version that was OK from just a few hours back, and th eproblem went away.

    The problem resurfaced when I added an ActiveX control. I have the source for that control and check it for definitions of format but it was clean? Any more ideas. Its quite a good ActiveX control so I'd like to keep using it, but not if it casues other stuff to break.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    well you just killed my other thought: if a prior version runs then there is obviously nothing bad with the A installation.

    no other thoughts - sorry.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by village idiot
    I have the following line of code in a form (this is just a snippet);
    rst.Open "SELECT PO.OrderNumber, format(PO.OrderDate, 'mmmm') " & _
    " FROM tblPurchaseOrder POD " .....

    where rst is an ADO.Recordset

    The code has been around for months working happily. Now when I try and run it I get the error;
    Function is not available in expressions in query expression 'format(PO.OrderDate, 'mmmm')'.

    Also, I've used the function =format(<some date>, "dd-mmm-yyyy") in a few text boxes on some forms and they have also stopped working - now they show #NAME. If I use the expression builder to build the formula the format() function no longer appears in the list of available functions!

    I've not changed the version of Access I'm running (2002) or my OS (XP Professional) or installed any patches etc.

    Any ideas what caused it and how I can fix it?
    Have you checked to see if any references have turned up MISSING? Access might have dropped the dll that the Format function resides in ...

Posting Permissions

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