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

[ChristineMD]

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? #$@%#@$#

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

 

[xenou]

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]

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]

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]

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.

 

[xenou]

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).

 

[xenou]

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)