mishlce
asked on
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...
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...
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
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
ASKER
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.
If you simply create a new query and use one of the string functions, what happens?
ASKER
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.
When I tried to continue and then went to the Debug - Compile it is grayed out and does not allow me to chose this.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
OK
Then you can accept your own post as the solution
;-)
Jeff
Then you can accept your own post as the solution
;-)
Jeff
ASKER
I did some trouble shooting and figured it out on my own/
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