rewrite an query in access

[Newbi1]

I have a view in SQL serever 2000 that I was trying to link to access. However every time I linked it, I could not open the table.

So I need to rewrite this view in access. I am not an access programmer, so my SQL seems to be off in this, even though it runs fine in SQL Server.

SELECT dbo.Project.PROIDNO, dbo.Project.PROOFFICE, dbo.Project.PRODEPT, Employee_1.EMPSNAME AS Pic, dbo.Employee.EMPSNAME AS PM,
dbo.Project.PROLNAME
FROM dbo.Project LEFT OUTER JOIN
dbo.Employee ON RIGHT(dbo.Project.PROPRIN, LEN(LTRIM(RTRIM(dbo.Project.PROPRIN))) - 1) = dbo.Employee.EMPIDNO LEFT OUTER JOIN
dbo.Employee Employee_1 ON RIGHT(dbo.Project.PROPM, LEN(LTRIM(RTRIM(dbo.Project.PROPM))) - 1) = Employee_1.EMPIDNO

 

[fredericofonseca]

You should be able to use a pass-through query while keeping the same SQL, as whatever SQL used on these type of queries is executed on the linked server dialect, not on Access.

Regarding your SQL is this an exact copy of the existing SQL?

This because the ltrim/rtrim associated with the "right" you have would not work if you are dealing with a char field with any spaces on it (this if I am reading it well).
e.g.
proprin defined as char(10)
value = " ABC " (e.g. 2 spaces + 3 letters + 5 spaces
len(ltrim(rtrim(proprin)) = 3
so right(proprin,3 - 1) = " " e.g. 2 spaces.

It would seem more logical to have
RIGHT(LTRIM(RTRIM(dbo.Project.PROPRIN)), LEN(LTRIM(RTRIM(dbo.Project.PROPRIN))) - 1)






Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Newbi1]

I am removing the first character from the text field. when they swicthed systems, the IDs have an extra character ion the front and I need to remove it to get the query to work.

 

[fredericofonseca]

As I said, and as far as I can see, it will only work if you don't have any trailing/leading space, and on that case you don't need the rtrim/ltrim, just use the "right" function

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com