Version Compatibility 2007 <-> 2010 - REALLY?!?!?!

ChristineMD

Board Regular
Joined
Jul 29, 2008
Messages
53
Ok, so I have an access 2010 database (accdb) I'm using as a front end to pull data from a mysql server and an oracle server (linked tables).

I build queries and reports for a user who will run reports from this database who has access 2007. I have 2010.

I have a reasonably straightforward query that uses the "left()" function. When she tries to run this from her version she gets "undefined function in left expression" I'm assuming it doesn't like "right() either, but failed before the 2nd field definition. Are you FREAKING kidding me? I flipping HATE microsoft. Is it because I'm referring to input on a form? they enter a 2 digit year on the form, I look to a field in a table where the two digit year is the first two characters in the field.

I've built her lots of reports in a 2003 (mdb) front end database and there's not been an issue like this. Is it ME or is it M$? I'm fully willing to admit if I'm doing something stupid, but I sure don't see it. And it works when I run it from 2010. They can't build in backward compatibility for the left() function? #$@%#@$#

Code:
SELECT Left([yearseason],2) AS [Year], Right([yearseason],1) AS Season, Sum(IIf(Left([media],1)="P",[quantityordered])) AS [OSP Units], Sum(IIf(Left([media],1)="P",[quantityordered]*[price])) AS [OSP Demand], Sum(IIf(Left([media],1)="A",[quantityordered])) AS [AMZ Units], Sum(IIf(Left([media],1)="A",[quantityordered]*[price])) AS [AMZ Demand], Sum(IIf(Left([media],1)="S",[quantityordered])) AS [SON Units], Sum(IIf(Left([media],1)="S",[quantityordered]*[price])) AS [SON Demand]
FROM orddetail INNER JOIN ordmaster ON orddetail.OrderNumber = ordmaster.OrderNumber
GROUP BY Left([yearseason],2), Right([yearseason],1)
HAVING (((Left([yearseason],2))=[forms]![F21 Reports]![CPYear]));

If any one can tell me if/where I'm going awry, that would be awesome.

Thanks in advance
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Hi,
The left function is backward compatible. The 2010 application is very different from 2007 but that's another story (I wouldn't recommend designing for 2007 on 2010).

To fix this you probably need to set the users references in the VBE. From time to time the reference to VBA get's lost for obscure reasons. Just go into the VBE (Visual Basic Editor, which will pop up if you hit Alt-F11 or view any code module). Then from Tools | References you can re-select the Visual Basic for Applications checkbox - as visible below (the first item in my references list):

<img alt="screenshot" src="http://northernocean.net/etc/mrexcel/20120727_refs.png"/>

Let us know if this doesn't work,
ξ
 

ChristineMD

Board Regular
Joined
Jul 29, 2008
Messages
53
I will definitely give this a shot. I'm assuming I need to do this logged in as the user on their machine though? She's off today. Unless you think this is something that would affect her profile if I logged in as myself or the domain admin, I'll have to wait til Monday. Thanks a million!
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Probably (not sure) it's something you could set as any user/admin if you open the database (you'd have to be the only user in the DB I think, to make any changes like this.
 

ChristineMD

Board Regular
Joined
Jul 29, 2008
Messages
53
Ok great, I can just do it from here then (i'll doublecheck from her system on monday to be sure) I was going to post that I peeked at it from my machine - there are TWO of those in my list (indistinguishable from each other), and neither is checked. I'll just do both...

Still hate M$ :)

Still appreciate your help, too! :)

Edit.. actually I can distinguish the two...

first one is system32\ven2232.OLB
second one is system32\msvbvm60.dll - so therefore I am assuming I really want this one.


edit#2. Jiminy Christmas.

When I try to select the M$ Visual Basic For Applications it says "conflicts with existing module, project or library

I actually don't have any modules created in this database, nor any projects. sigh.
 
Last edited:

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
1) What references do you have checked (and in what order)?
2) What version of Windows are you running?
3) Are you running 64-bit Windows?
4) Are you running a 64-bit installation of Access (hopefully answer is no).
 
Last edited:

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Allen Browne provides a table here of the references you need to be aware of (scroll down just a little):
http://allenbrowne.com/ser-38.html

(might be a little out of date because the file locations are for XP, not Win7)
 

Forum statistics

Threads
1,186,178
Messages
5,956,392
Members
438,249
Latest member
georgebasalic3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top