Re: [MS_AccessPros] |1 in query expression '|2'

 

It is a huge undertaking to convert the db to 2007 and I am hoping for a solution.

Yes ZFORM21 is open and I understand about join, but the query does what it is supposed todo. 

So how come this works on some computers (also running Access 2013) and not on others?.

Sarah

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Sarah-

I assume zform21 is open and the referenced controls have values.  You should realize that your LEFT JOINS are meaningless because you have a filter (shift.[Shift Name]) on one of the columns in a "right" table.  You'll end up with effectively an INNER JOIN between student and shift.

That error message implies that the query is not "compiled", and Access is getting an undefined error when it tries to compile it.  It's not a good idea to try to run and old .mdb-based application in 2007 and later.  If you have the original mdb, can you convert it to accdb under 2007, then create the run-time file?

John Viescas, author

Effective SQL

SQL Queries for Mere Mortals

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Access Applications

http://www.viescas.com/

On Sep 27, 2016, at 16:40, sarahk@... [MS_Access7 _Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

sql:

SELECT student.[student id], student.[last name], student.[first name], student.mi, student.[end date], student.dob, age([dob],Date()) AS age, student.alzpro, student.sex, student.advd, student.drcd, student.aid, student.sneed, student.[billing type], shift.[Shift Name], student.[start date], physician.*, student.address, student.address1, student.city, student.state, student.zip, student.[tel #1], student.[cl#], student.are, student.[eclast name], student.[ecfirst name], student.ecrelation, student.ecaddress, student.ecaddress1, student.eccity, student.ecstate, student.eczip, student.[ectel#], living.living, religion.religion, student.sun, student.mon, student.tue, student.wed, student.thu, student.fri, student.sat, student.[billing type]

FROM (((student LEFT JOIN shift ON student.[preferred shift] = shift.[Shift #]) LEFT JOIN physician ON student.drcd = physician.drcd) LEFT JOIN religion ON student.religioncd = religion.Religcd) LEFT JOIN living ON student.livingcd = living.livcd

WHERE (((student.[end date]) Is Null) AND ((student.[billing type]) Like [Forms]![zform21]![pmb]) AND ((shift.[Shift Name]) Between [Forms]![zform21]![sfrom] And [Forms]![zform21]![sto]) AND ((student.[start date])<=[forms]![zform21]![pid to]) AND (([last name] & ", " & [first name] & " " & [mi]) Between [Forms]![zform21]![ln from] And [Forms]![zform21]![ln to]) AND ((nz([student]![insrcd],"")) Between nz([Forms]![zform21]![insfrom],"") And nz([Forms]![zform21]![insto],""))) OR (((student.[end date])>[forms]![zform21]![pid to]))

ORDER BY student.[last name], student.[first name], student.mi;

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Sarah-

Do you know which query is breaking?  And if so, what's the SQL?

John Viescas, author

Effective SQL

SQL Queries for Mere Mortals

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Access Applications

http://www.viescas.com/

On Sep 27, 2016, at 00:11, sarahk@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

"|1 in query expression '|2'"

I get this message when I run a query on one of the computers on a network. None of the other computers on the network are having this problem. The other computers have MS Access 2013 installed, the database is located on a Windows 2012 Server every computer has their own front end (mde) (located on the server) and are sharing the back end which is also located on the server.

The computer that is having the problem, is running windows 7 (as are the other computers) and is connecting with RDP to the windows 2012 server and running the application from the server. The server has MS Access 2013 runtime version installed, I also tried 2007 and 2010 runtime, it did not make a difference, I still get the same error. I also tried running the application directly on the sever (not with RDP)  and I still get the same error. All the other forms/queries / reports etc are working.

The application was created in MS Access 2003.

Does anybody have any ideas why the server is getting this error? I tried googling but none of the answers made sense.

As always all help is appreciated.

Sarah

 

 

__._,_.___

---

Posted by: sarahk@schemesoftware.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (5)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___