• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

I just updated to Office 2007 from 2003 and getting errors in Access Qrys

I recently updated from Office 2003 to 2007.  When I go to run some of my Access Qrys that I get an error.  For example one of my functions in one of my qry is to take the month out of the long date as silled below.  

Month: Mid([MNTDAT],5,2)

When I run this I get an error now that says invalid mid undefined function?
How do I resolve this as well as other trim functions I have for Left Trims etc...  

0
mishlce
Asked:
mishlce
  • 4
  • 3
1 Solution
 
Jeffrey CoachmanCommented:
<I recently updated from Office 2003 to 2007.>
Let's be clear, ...did you "Convert" your files to this new format, or are you simply running the 2003 files under access 2007?

Create a new blank Access 2007 format DB
Import all the objects
Test
0
 
pteranodon72Commented:
mishlce,

A problem with string manipulation functions usually suggests a problem with an unrelated reference, strangely enough. Press Control-G to open the immediate pane of the debugging environment. Then, choose Tools->References from the menu. Check to see if any References are marked as MISSING. If there is a missing one, un-check it, remember its name, and press OK. Go to Debug->Compile.  If the project compiles without any errors, then the reference was unneeded in the database. If you get a "user defined type not defined", then that reference is needed and you'll need to browse to the path for it in the References window.

One bad reference causes trouble in other references. Mid is a function in the VBA library, but it is probably not the culprit, just a symptom of a different broken reference.

HTH,

pT72
0
 
mishlceAuthor Commented:
At work they are updating to new computers.  My old computer, had all my databases that I built in Access 2003. They transferred everything from my old computer to the new computer, and the new computer has Office 2007 installed vs. 2003, all my databases are here, some qrys work fine, some do not.  2003 is completely wiped off my computer.  They (IT ) said that there shouldn't be a problem, but apparantly there is, as these qrys ran fine in 2003 and not fine in 2007.  
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Jeffrey CoachmanCommented:
If you simply create a new query and use one of the string functions, what happens?
0
 
mishlceAuthor Commented:
Yes I still get the error.  I also tried this:  Press Control-G to open the immediate pane of the debugging environment. Then, choose Tools->References from the menu. Check to see if any References are marked as MISSING. If there is a missing one, un-check it, remember its name, and press OK. Go to Debug->Compile.  I was able to go through and un check the missing refrence which was listed as  (Utiility.mda)

When I tried to continue and then went to the Debug - Compile it is grayed out and does not allow me to chose this.    
0
 
mishlceAuthor Commented:
It works now, I had to put all my databases on a computer that has 2003 Office installed.  I then Compacted and repaired all databases then put them back to my new computer with 2007 and it ran fine.
0
 
Jeffrey CoachmanCommented:
OK

Then you can accept your own post as the solution

;-)

Jeff
0
 
mishlceAuthor Commented:
I did some trouble shooting and figured it out on my own/
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now