Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2006
    Posts
    26

    Question Unanswered: Broken SQL from Access 2003 to 2007

    I had the SQL below which worked fine in Access 2003, but after upgrading the workstations to Access 2007, it's causing an error Undefined function 'Right' in expression. What doesn't make sense is that there is no 'Right' function used in the query. Please help.

    Code:
    INSERT INTO [Property Data] ( [PromasLookup#], [Key #], [Ledger #], [Owner's Name], [Street #], [Street Name], City, [ZIP Code], Rent, [sq ft] )
    SELECT [0 Interim Insert Query - Do not delete].Lookup, [0 Interim Insert Query - Do not delete].Expr1, [0 Interim Insert Query - Do not delete].ID, [0 Interim Insert Query - Do not delete].Last, [0 Interim Insert Query - Do not delete].Numbers, [0 Interim Insert Query - Do not delete].Address, City.CityAbbr, [0 Interim Insert Query - Do not delete].UnitZIP, [0 Interim Insert Query - Do not delete].Rent, [0 Interim Insert Query - Do not delete].SF
    FROM ([0 Interim Insert Query - Do not delete] LEFT JOIN [Property Data] ON [0 Interim Insert Query - Do not delete].Lookup = [Property Data].[PromasLookup#]) INNER JOIN City ON [0 Interim Insert Query - Do not delete].City = City.City
    WHERE ((([Property Data].[PromasLookup#]) Is Null)) and UNIT.Lookup <> 463;
    We're just a speck of dust in a universe.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    that sounds like a missing VBA reference
    right is actually a VBA function not part of ANSI SQL
    check the references in a code window, tools referneces.

    im guesing its just that the old code is looking for a specific library that has no whanged
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2006
    Posts
    26

    Question

    I cannot find Tools->References. Perhaps, I should have clarified that this query is a stand alone object, not associated with any form. Sorry.
    We're just a speck of dust in a universe.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by itchibahn View Post
    I cannot find Tools->References. Perhaps, I should have clarified that this query is a stand alone object, not associated with any form. Sorry.
    It does not change anything. Open the VBA Editor (ALT+F11), then open the "Tools" Menu and select "References". In the Reference Dialog box, see if a reference is not marked as "Missing". If it is, recreate it.

    See also: Access Reference Problems
    Have a nice day!

  5. #5
    Join Date
    Feb 2006
    Posts
    26

    SOLVED: Broken SQL from Access 2003 to 2007

    I am so behind, way behind, like access97. There's so much to learn on access2007...
    It was the utility.mda that was missing, I just disabled it since I don't think it's being used. All seems to be good now.

    Thank you!!!!
    We're just a speck of dust in a universe.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

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