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

Note that Mid(string, 1, len) is the same as Left(string, len)

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Apr 14, 2015, at 12:07 AM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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]

------------------------------------
Posted by: Art Lorenzini <dbalorenzini@yahoo.com>
------------------------------------

------------------------------------

Yahoo Groups Links