SUBSTRING is not valid in Access SQL. Use Mid(). You will also need to replace CHARINDEX with InStr() and change the order of the arguments:
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 13 Apr 2015 19:01:28 +0000
Subject: Re: [MS_AccessPros] Re: r SQL Server query to Access
With Warm Regards,
From: "Liz Ravenwood liz_ravenwood@beaerospace.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "'MS_Access_Professionals@yahoogroups.com'" <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, April 13, 2015 1:11 PM
Subject: RE: [MS_AccessPros] Re: r SQL Server query to Access
Art
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
select * from
(select custnmbr AS CustID, custclas AS [Class], ADRSCODE AS UnitID,Hold,
TRIM(Mid(CustName, 0, Instr( Custname,","))) As LastName,
CUSTNAME AS TenantName,
address1, address2, city, state, zip from RM00101
where INACTIVE = 0 and city & "" <> "" AND CUSTCLAS="Rental" and Hold = 0
) a
inner join
(Select CUSTNMBR, CUSTNAME, LOCNCODE, LOCNDSCR, BSSI_PortfolioID, BSSI_Description1,
BSSI_Tenant_Lease_Status, BSSI_Lease_Execution_Dat, BSSI_Lease_Termination_D
From B4602200 where
BSSI_Tenant_Lease_Status = 2) b
on a.CustID = b.CUSTNMBR
(select custnmbr AS CustID, custclas AS [Class], ADRSCODE AS UnitID,Hold,
TRIM(Mid(CustName, 0, Instr( Custname,","))) As LastName,
CUSTNAME AS TenantName,
address1, address2, city, state, zip from RM00101
where INACTIVE = 0 and city & "" <> "" AND CUSTCLAS="Rental" and Hold = 0
) a
inner join
(Select CUSTNMBR, CUSTNAME, LOCNCODE, LOCNDSCR, BSSI_PortfolioID, BSSI_Description1,
BSSI_Tenant_Lease_Status, BSSI_Lease_Execution_Dat, BSSI_Lease_Termination_D
From B4602200 where
BSSI_Tenant_Lease_Status = 2) b
on a.CustID = b.CUSTNMBR
Duane Hookom, MVP
MS Access
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 13 Apr 2015 19:01:28 +0000
Subject: Re: [MS_AccessPros] Re: r SQL Server query to Access
I tried it this way
select * from
(select custnmbr AS CustID, custclas AS [Class], ADRSCODE AS UnitID,Hold,
LTRIM(RTRIM(SUBSTRING(CustName, 0, CHARINDEX(',', Custname)))) As LastName,
CUSTNAME AS TenantName,
address1, address2, city, state, zip from RM00101
where INACTIVE = 0 and city <> " " AND CUSTCLAS="Rental" and Hold = 0
) a
inner join
(Select CUSTNMBR, CUSTNAME, LOCNCODE, LOCNDSCR, BSSI_PortfolioID, BSSI_Description1,
BSSI_Tenant_Lease_Status, BSSI_Lease_Execution_Dat, BSSI_Lease_Termination_D
From B4602200 where
BSSI_Tenant_Lease_Status = 2) b
on a.CustID = b.CUSTNMBR
(select custnmbr AS CustID, custclas AS [Class], ADRSCODE AS UnitID,Hold,
LTRIM(RTRIM(SUBSTRING(CustName, 0, CHARINDEX(',', Custname)))) As LastName,
CUSTNAME AS TenantName,
address1, address2, city, state, zip from RM00101
where INACTIVE = 0 and city <> " " AND CUSTCLAS="Rental" and Hold = 0
) a
inner join
(Select CUSTNMBR, CUSTNAME, LOCNCODE, LOCNDSCR, BSSI_PortfolioID, BSSI_Description1,
BSSI_Tenant_Lease_Status, BSSI_Lease_Execution_Dat, BSSI_Lease_Termination_D
From B4602200 where
BSSI_Tenant_Lease_Status = 2) b
on a.CustID = b.CUSTNMBR
But it says SUBSTRING is a undefined function.
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Anyone who claimed that old age had brought them patience was either lying or senile."
From: "Liz Ravenwood liz_ravenwood@beaerospace.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "'MS_Access_Professionals@yahoogroups.com'" <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, April 13, 2015 1:11 PM
Subject: RE: [MS_AccessPros] Re: r SQL Server query to Access
The "as a" before inner join doesn't look right either. I think Access sql doesn't have those words.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, April 13, 2015 10:54 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: r SQL Server query to Access
Sent: Monday, April 13, 2015 10:54 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: r SQL Server query to Access
Art
First save each of those 2 nested queries as queries. Then use them as tables in your main query. If you don't want to do that then try putting brackets around Class. It's probably a reserved word.
custclas AS [Class]
Is there a app or can someone help me to convert the following SQL Server Query to Access query:
SELECT a.CustID, a.UnitID, *
FROM (select custnmbr AS CustID, custclas AS Class, ADRSCODE AS UnitID,Hold,
CUSTNAME AS TenantName,
address1, address2, city, state, zip from RM00101
where INACTIVE = 0 and city <> ' ' AND CUSTCLAS='Rental' and Hold = 0
FROM (select custnmbr AS CustID, custclas AS Class, ADRSCODE AS UnitID,Hold,
CUSTNAME AS TenantName,
address1, address2, city, state, zip from RM00101
where INACTIVE = 0 and city <> ' ' AND CUSTCLAS='Rental' and Hold = 0
) AS a INNER JOIN (Select CUSTNMBR, CUSTNAME, LOCNCODE, LOC! NDSCR, BSSI_PortfolioID, BSSI_Description1,
BSSI_Tenant_Lease_Status, BSSI_Lease_Execution_Dat, BSSI_Lease_Termination_D
From B4602200 where
BSSI_Tenant_Lease_Status = 2) AS b ON a.CustID = b.CUSTNMBR;
BSSI_Tenant_Lease_Status, BSSI_Lease_Execution_Dat, BSSI_Lease_Termination_D
From B4602200 where
BSSI_Tenant_Lease_Status = 2) AS b ON a.CustID = b.CUSTNMBR;
WHen I try to run this in ACCESS I get ODBC call error.
Thank you
Art Lorenzini
South Dakota.
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (11) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar