ACCESS FEED: Re: [MS_AccessPros] Re: r SQL Server query to Access

Perfection!!!! Thanks to everybody that helped.
With Warm Regards, Arthur D. LorenziniIT System ManagerCheyenne River Housing AuthorityWk.(605)964-4265 Ext. 130Fax (605)964-1070
"Anyone who claimed that old age had brought them patience was either lying or senile."

From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
Sent: Monday, April 13, 2015 4:55 PM
Subject: RE: [MS_AccessPros] Re: r SQL Server query to Access

Art,
Fix it like:
TRIM(Mid(CustName, 1, Instr( Custname,","))) As LastName,

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 13 Apr 2015 21:43:31 +0000
Subject: Re: [MS_AccessPros] Re: r SQL Server query to Access

That got rid of the error but I ended up with the first name instead of the last name. :)
With Warm Regards, Arthur D. LorenziniIT System ManagerCheyenne River Housing AuthorityWk.(605)964-4265 Ext. 130Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."

From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
Sent: Monday, April 13, 2015 4:39 PM
Subject: RE: [MS_AccessPros] Re: r SQL Server query to Access

Try remove the ", 0"

TRIM(Mid(CustName, Instr( Custname,","))) As LastName,

Duane Hookom, MVP
MS Access

> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Mon, 13 Apr 2015 21:31:31 +0000
> Subject: Re: [MS_AccessPros] Re: r SQL Server query to Access
>
> Ran this:
> 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
> It worked great except for the line  TRIM(Mid(CustName, 0, Instr( Custname,","))) As LastName, the dataset returned #Func in the column.
> With Warm Regards, Arthur D. LorenziniIT System ManagerCheyenne River Housing AuthorityWk.(605)964-4265 Ext. 130Fax (605)964-1070
> "Anyone who claimed that old age had brought them patience was either lying or senile."
>
>
>
>
> From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
> To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
> Sent: Monday, April 13, 2015 2:58 PM
> Subject: RE: [MS_AccessPros] Re: r SQL Server query to Access
>
> 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:
>
> 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 Duane Hookom, MVP
> 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
> But it says SUBSTRING is a undefined function.
> With Warm Regards, Arthur D. LorenziniIT System ManagerCheyenne River Housing AuthorityWk.(605)964-4265 Ext. 130Fax (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
>
> 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]
>
> ---In MS_Access_Professionals@yahoogroups.com, <dbalorenzini@yahoo.com> wrote : 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 ) 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; 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.
>
>
>
>
> #yiv1542789142 #yiv1542789142 -- #yiv1542789142ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1542789142 #yiv1542789142ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1542789142 #yiv1542789142ygrp-mkp #yiv1542789142hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv1542789142 #yiv1542789142ygrp-mkp #yiv1542789142ads {margin-bottom:10px;}#yiv1542789142 #yiv1542789142ygrp-mkp .yiv1542789142ad {padding:0 0;}#yiv1542789142 #yiv1542789142ygrp-mkp .yiv1542789142ad p {margin:0;}#yiv1542789142 #yiv1542789142ygrp-mkp .yiv1542789142ad a {color:#0000ff;text-decoration:none;}#yiv1542789142 #yiv1542789142ygrp-sponsor #yiv1542789142ygrp-lc {font-family:Arial;}#yiv1542789142 #yiv1542789142ygrp-sponsor #yiv1542789142ygrp-lc #yiv1542789142hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1542789142 #yiv1542789142ygrp-sponsor #yiv1542789142ygrp-lc .yiv1542789142ad {margin-bottom:10px;padding:0 0;}#yiv1542789142 #yiv1542789142actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1542789142 #yiv1542789142activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1542789142 #yiv1542789142activity span {font-weight:700;}#yiv1542789142 #yiv1542789142activity span:first-child {text-transform:uppercase;}#yiv1542789142 #yiv1542789142activity span a {color:#5085b6;text-decoration:none;}#yiv1542789142 #yiv1542789142activity span span {color:#ff7900;}#yiv1542789142 #yiv1542789142activity span .yiv1542789142underline {text-decoration:underline;}#yiv1542789142 .yiv1542789142attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv1542789142 .yiv1542789142attach div a {text-decoration:none;}#yiv1542789142 .yiv1542789142attach img {border:none;padding-right:5px;}#yiv1542789142 .yiv1542789142attach label {display:block;margin-bottom:5px;}#yiv1542789142 .yiv1542789142attach label a {text-decoration:none;}#yiv1542789142 blockquote {margin:0 0 0 4px;}#yiv1542789142 .yiv1542789142bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv1542789142 .yiv1542789142bold a {text-decoration:none;}#yiv1542789142 dd.yiv1542789142last p a {font-family:Verdana;font-weight:700;}#yiv1542789142 dd.yiv1542789142last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1542789142 dd.yiv1542789142last p span.yiv1542789142yshortcuts {margin-right:0;}#yiv1542789142 div.yiv1542789142attach-table div div a {text-decoration:none;}#yiv1542789142 div.yiv1542789142attach-table {width:400px;}#yiv1542789142 div.yiv1542789142file-title a, #yiv1542789142 div.yiv1542789142file-title a:active, #yiv1542789142 div.yiv1542789142file-title a:hover, #yiv1542789142 div.yiv1542789142file-title a:visited {text-decoration:none;}#yiv1542789142 div.yiv1542789142photo-title a, #yiv1542789142 div.yiv1542789142photo-title a:active, #yiv1542789142 div.yiv1542789142photo-title a:hover, #yiv1542789142 div.yiv1542789142photo-title a:visited {text-decoration:none;}#yiv1542789142 div#yiv1542789142ygrp-mlmsg #yiv1542789142ygrp-msg p a span.yiv1542789142yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1542789142 .yiv1542789142green {color:#628c2a;}#yiv1542789142 .yiv1542789142MsoNormal {margin:0 0 0 0;}#yiv1542789142 o {font-size:0;}#yiv1542789142 #yiv1542789142photos div {float:left;width:72px;}#yiv1542789142 #yiv1542789142photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv1542789142 #yiv1542789142photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1542789142 #yiv1542789142reco-category {font-size:77%;}#yiv1542789142 #yiv1542789142reco-desc {font-size:77%;}#yiv1542789142 .yiv1542789142replbq {margin:4px;}#yiv1542789142 #yiv1542789142ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv1542789142 #yiv1542789142ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1542789142 #yiv1542789142ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1542789142 #yiv1542789142ygrp-mlmsg select, #yiv1542789142 input, #yiv1542789142 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv1542789142 #yiv1542789142ygrp-mlmsg pre, #yiv1542789142 code {font:115% monospace;}#yiv1542789142 #yiv1542789142ygrp-mlmsg * {line-height:1.22em;}#yiv1542789142 #yiv1542789142ygrp-mlmsg #yiv1542789142logo {padding-bottom:10px;}#yiv1542789142 #yiv1542789142ygrp-msg p a {font-family:Verdana;}#yiv1542789142 #yiv1542789142ygrp-msg p#yiv1542789142attach-count span {color:#1E66AE;font-weight:700;}#yiv1542789142 #yiv1542789142ygrp-reco #yiv1542789142reco-head {color:#ff7900;font-weight:700;}#yiv1542789142 #yiv1542789142ygrp-reco {margin-bottom:20px;padding:0px;}#yiv1542789142 #yiv1542789142ygrp-sponsor #yiv1542789142ov li a {font-size:130%;text-decoration:none;}#yiv1542789142 #yiv1542789142ygrp-sponsor #yiv1542789142ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv1542789142 #yiv1542789142ygrp-sponsor #yiv1542789142ov ul {margin:0;padding:0 0 0 8px;}#yiv1542789142 #yiv1542789142ygrp-text {font-family:Georgia;}#yiv1542789142 #yiv1542789142ygrp-text p {margin:0 0 1em 0;}#yiv1542789142 #yiv1542789142ygrp-text tt {font-size:120%;}#yiv1542789142 #yiv1542789142ygrp-vital ul li:last-child {border-right:none !important;}#yiv1542789142
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
> Posted by: Art Lorenzini <dbalorenzini@yahoo.com>
> ------------------------------------
>
>
> ------------------------------------
>
> Yahoo Groups Links
>
>
>

#yiv6592191767 #yiv6592191767 -- #yiv6592191767ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv6592191767 #yiv6592191767ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv6592191767 #yiv6592191767ygrp-mkp #yiv6592191767hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv6592191767 #yiv6592191767ygrp-mkp #yiv6592191767ads {margin-bottom:10px;}#yiv6592191767 #yiv6592191767ygrp-mkp .yiv6592191767ad {padding:0 0;}#yiv6592191767 #yiv6592191767ygrp-mkp .yiv6592191767ad p {margin:0;}#yiv6592191767 #yiv6592191767ygrp-mkp .yiv6592191767ad a {color:#0000ff;text-decoration:none;}#yiv6592191767 #yiv6592191767ygrp-sponsor #yiv6592191767ygrp-lc {font-family:Arial;}#yiv6592191767 #yiv6592191767ygrp-sponsor #yiv6592191767ygrp-lc #yiv6592191767hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv6592191767 #yiv6592191767ygrp-sponsor #yiv6592191767ygrp-lc .yiv6592191767ad {margin-bottom:10px;padding:0 0;}#yiv6592191767 #yiv6592191767actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv6592191767 #yiv6592191767activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv6592191767 #yiv6592191767activity span {font-weight:700;}#yiv6592191767 #yiv6592191767activity span:first-child {text-transform:uppercase;}#yiv6592191767 #yiv6592191767activity span a {color:#5085b6;text-decoration:none;}#yiv6592191767 #yiv6592191767activity span span {color:#ff7900;}#yiv6592191767 #yiv6592191767activity span .yiv6592191767underline {text-decoration:underline;}#yiv6592191767 .yiv6592191767attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv6592191767 .yiv6592191767attach div a {text-decoration:none;}#yiv6592191767 .yiv6592191767attach img {border:none;padding-right:5px;}#yiv6592191767 .yiv6592191767attach label {display:block;margin-bottom:5px;}#yiv6592191767 .yiv6592191767attach label a {text-decoration:none;}#yiv6592191767 blockquote {margin:0 0 0 4px;}#yiv6592191767 .yiv6592191767bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv6592191767 .yiv6592191767bold a {text-decoration:none;}#yiv6592191767 dd.yiv6592191767last p a {font-family:Verdana;font-weight:700;}#yiv6592191767 dd.yiv6592191767last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv6592191767 dd.yiv6592191767last p span.yiv6592191767yshortcuts {margin-right:0;}#yiv6592191767 div.yiv6592191767attach-table div div a {text-decoration:none;}#yiv6592191767 div.yiv6592191767attach-table {width:400px;}#yiv6592191767 div.yiv6592191767file-title a, #yiv6592191767 div.yiv6592191767file-title a:active, #yiv6592191767 div.yiv6592191767file-title a:hover, #yiv6592191767 div.yiv6592191767file-title a:visited {text-decoration:none;}#yiv6592191767 div.yiv6592191767photo-title a, #yiv6592191767 div.yiv6592191767photo-title a:active, #yiv6592191767 div.yiv6592191767photo-title a:hover, #yiv6592191767 div.yiv6592191767photo-title a:visited {text-decoration:none;}#yiv6592191767 div#yiv6592191767ygrp-mlmsg #yiv6592191767ygrp-msg p a span.yiv6592191767yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv6592191767 .yiv6592191767green {color:#628c2a;}#yiv6592191767 .yiv6592191767MsoNormal {margin:0 0 0 0;}#yiv6592191767 o {font-size:0;}#yiv6592191767 #yiv6592191767photos div {float:left;width:72px;}#yiv6592191767 #yiv6592191767photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv6592191767 #yiv6592191767photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv6592191767 #yiv6592191767reco-category {font-size:77%;}#yiv6592191767 #yiv6592191767reco-desc {font-size:77%;}#yiv6592191767 .yiv6592191767replbq {margin:4px;}#yiv6592191767 #yiv6592191767ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv6592191767 #yiv6592191767ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv6592191767 #yiv6592191767ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv6592191767 #yiv6592191767ygrp-mlmsg select, #yiv6592191767 input, #yiv6592191767 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv6592191767 #yiv6592191767ygrp-mlmsg pre, #yiv6592191767 code {font:115% monospace;}#yiv6592191767 #yiv6592191767ygrp-mlmsg * {line-height:1.22em;}#yiv6592191767 #yiv6592191767ygrp-mlmsg #yiv6592191767logo {padding-bottom:10px;}#yiv6592191767 #yiv6592191767ygrp-msg p a {font-family:Verdana;}#yiv6592191767 #yiv6592191767ygrp-msg p#yiv6592191767attach-count span {color:#1E66AE;font-weight:700;}#yiv6592191767 #yiv6592191767ygrp-reco #yiv6592191767reco-head {color:#ff7900;font-weight:700;}#yiv6592191767 #yiv6592191767ygrp-reco {margin-bottom:20px;padding:0px;}#yiv6592191767 #yiv6592191767ygrp-sponsor #yiv6592191767ov li a {font-size:130%;text-decoration:none;}#yiv6592191767 #yiv6592191767ygrp-sponsor #yiv6592191767ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv6592191767 #yiv6592191767ygrp-sponsor #yiv6592191767ov ul {margin:0;padding:0 0 0 8px;}#yiv6592191767 #yiv6592191767ygrp-text {font-family:Georgia;}#yiv6592191767 #yiv6592191767ygrp-text p {margin:0 0 1em 0;}#yiv6592191767 #yiv6592191767ygrp-text tt {font-size:120%;}#yiv6592191767 #yiv6592191767ygrp-vital ul li:last-child {border-right:none !important;}#yiv6592191767

[Non-text portions of this message have been removed]