Rabu, 28 September 2016

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


So I did as suggested, removed Access and reinstalled runtime version 2007. I am still having this problem.

Any other suggestions?. Anybody?


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

Hi Sarah,


The fact that the end-users are connecting to the server via RDP and running their own Remote Desktop session means that the OS and the version of Access running on their local PC's is irrelevant.  The problem is with the server and the version of access that's running on it.  FWIW, I've had some really peculiar problems with the 2013 & 2016 Access runtimes running on both Server 2012 & Windows 7/8.1/10, particularly Windows 10.  In each case, I solved the problems by completely removing & reinstalling Access on the affected machines.


A few things to check:

-          Make sure that Access was installed correctly on the RDP server.  The server needs to be switched into Install mode before installing any application software.  If it wasn't, completely remove Access, reboot the server & install it properly.

-          Make sure there aren't any older versions of Access on the same server as IME this can cause some weird problems.

-          Try creating a new database & importing all the objects from the original database.  This sometimes fixes unexplained problems.

-          Is zForm21 a Report Selection form?  If so, what I tend to do in situations like this is to create the query SQL in VBA and plug the actual field values into the query rather than referencing the form controls.  This usually results in a smaller wury that is much easier to debug if things aren't working as expected.


I hope this is of some help.





From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 27 September 2016 18:53
To: MS_Access_Professionals@yahoogroups.com
Subject: 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?.


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



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


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



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 :



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


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.










Posted by: sarahk@schemesoftware.com
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (7)

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.



Tidak ada komentar:

Posting Komentar