Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mid problem in Access 2003 (Access 2002/2003)

    I've migrated from Access 2002 to Access 2003 and have problems with the following command:
    UPDATE SAPBillings SET SAPBillings.[Text] = Mid([text],2,50)

    Error message:

    Undefined Function "Mid" in expression

    I think I need to load some preferances.

    TIA

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

    Re: Mid problem in Access 2003 (Access 2002/2003)

    Activate the Visual Basic Editor (Alt+F11)
    Select Tools | References...
    Are there any references starting with MISSING? If so, note which ones they are, then clear their check boxes. Finally click OK.
    See if the problem is gone.
    If not, select Tools | References... again.
    Try to locate the correct version of one of the missing references, and tick the corresponding check box.
    Click OK, and test to see if the problem is gone.
    Repeat if necessary.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mid problem in Access 2003 (Access 2002/2003)

    Hans I have no missing links. Any other ideas

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

    Re: Mid problem in Access 2003 (Access 2002/2003)

    Problems with functions such as Mid are *always* caused by missing references... <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>
    Are you sure nothing has changed in the database (apart from moving from Access 2002 to 2003)?

  5. #5
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mid problem in Access 2003 (Access 2002/2003)

    Yes only the software change. 1st Time I opened db and tried to run macro module

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

    Re: Mid problem in Access 2003 (Access 2002/2003)

    Mysterious. Could you do the following?
    - Create a blank new database in Access 2003.
    - Activate the Visual Basic Editor (Alt+F11).
    - Select Insert | Module.
    - Enter or paste the following code into the module:
    <code>
    Sub TestMid()
    Dim strTest As String
    strTest = "Mario"
    MsgBox Mid(strTest, 2, 3)
    End Sub
    </code>
    - Position the insertion point anywhere in the procedure.
    - Press F5 to execute it.
    - Do you get an error message, or do you see a message box with the text 'ari'?

  7. #7
    New Lounger
    Join Date
    Aug 2006
    Location
    Southampton, Hampshire, United Kingdom
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mid problem in Access 2003 (Access 2002/2003)

    I vaguely remember Access 2003 using a proper SQL engine underneath, but I don't have it installed and so can't check.

    Try changing your Mid() to a substring() and see what happens...!

    Matthew

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

    Re: Mid problem in Access 2003 (Access 2002/2003)

    Hi Matthew,

    Welcome to Woody's Lounge!

    Access 2003 uses the same query engine (Microsoft Jet SQL) as previous versions. It allows the use of most VBA functions.

  9. #9
    New Lounger
    Join Date
    Aug 2006
    Location
    Southampton, Hampshire, United Kingdom
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mid problem in Access 2003 (Access 2002/2003)

    My bad - in which case, definitely sounds like a missing reference as you said!

  10. #10
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mid problem in Access 2003 (Access 2002/2003)

    Hans thanks for the advice. I tried it on a new db and it worked. Then I tried to try it on my problem db and when I tried to insert the code into a new module I got the following error:

    Your Microsoft Office Access database or project contains a missing or broken referance to the file "OWC10.dll" version 1.1

    Please assist me in pointing me to the right referance

    Thanks

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

    Re: Mid problem in Access 2003 (Access 2002/2003)

    See <post:=593,703>post 593,703</post:> higher up in this thread.

  12. #12
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mid problem in Access 2003 (Access 2002/2003)

    Hans what was very strange was I did not find any referance with a missing link. Based on the missing dll OWC10.DLL I googled for the file. On this tread (http://www.pcreview.co.uk/forums/thread-1700057.php) it stated the referance is: Microsoft Office XP Web Components. I've tick both referances and it WORKS. Thanks

Posting Permissions

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