Re: [MS_AccessPros] Converting to Access SQL

 

Art - have you stepped through the code to see if it is doing what you expected? Put a breakpoint on:

Select Case Me.cmbChooseCommunity

When the code stops press F8 to step through each line. If "All" is selected I'm guessing the code for Case 0 will run.

Regards,

Bill Mosca

On February 26, 2018 at 10:27 AM "Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

 

 

 

I am having an issue with  my cmbChooseCommunity which is supposed to filter sfrmTenantList; The subform using a working version qryTenantListing you see below:

 

 

Private Sub cmbChooseCommunity_AfterUpdate()

Select Case Me.cmbChooseCommunity

 

   

 

    Case 0

 

      Forms![frmTenantListingDashboard]![sfrmTenantList].Form.RecordSource = "qryTenantListing"

 

      Forms![frmTenantListingDashboard]![sfrmTenantList].Form.Requery

 

 

 

   End Select

 End Sub

Working version of qryTenantListing:

SELECT B4602200.NAME, tblUnit.UnitCode, tblUnit.BedroomCount, tblUnit.CurrentStatus, tblProject.[Project Number], tblUnit.UnitStatus, tblUnit.ClassificationType, tblUnit.UnitHousingType, tlkpCommunity.CommunityName, B4602200.BSSI_Tenant_Lease_Status, B4602200..CUSTNMBR, B4602200.BSSI_Lease_Execution_Dat AS Move_In_Date, Trim([ADDRESS1]) & ", " & Trim([CITY]) & ", " & Trim([STATE]) & " " & Trim([Zip]) AS TenantAddress, tblUnit.UnitID, tblUnit.CommunityID
FROM ((B4602200 INNER JOIN (tblUnit INNER JOIN tblProject ON tblUnit.ProjectID = tblProject.ProjectID) ON B4602200.LOCNCODE = tblUnit.UnitCode) INNER JOIN tlkpCommunity ON tblUnit.CommunityID = tlkpCommunity.CommunityID) INNER JOIN RM00101 ON B4602200.CUSTNMBR = RM00101.CUSTNMBR
WHERE (((tblUnit.CurrentStatus)="CRHA Use") AND ((B4602200.BSSI_Tenant_Lease_Status)=2))
ORDER BY tblUnit.UnitCode;

When I Change my Combo box to the "All" item, it runs the code but the subform remains empty. But if I run the query it runs fine...

If I select another community is the list, it filters the subform fine.

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Valar Dohaeris”

---

From: "wrmosca wrmosca@comcast.net [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "dbalorenzini@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
Sent: Sunday, February 25, 2018 11:48 AM
Subject: Re: [MS_AccessPros] Converting to Access SQL

 

 

Art- your company is paying this consultant. Make him convert it...or look up case blocks in T-SQL and Iff statements in Access. You'll find you can convert it yourself.

Grow your skills!

Regards,

Bill Mosca

------ Original message------

From: dbalorenzini@yahoo.com [MS_Access_Professionals]

Date: Sun, Feb 25, 2018 8:36 AM

To: MS_Access_Professionals@yahoogroups.com;

Cc:

Subject:[MS_AccessPros] Converting to Access SQL

 

We have a technician from a vendor we work with that was supposed to aide in a creation of a report. I sent him my version of the code and he sent back this?

SELECT

        case when B4602200.BSSI_Tenant_Lease_Status =  2

     then B4602200.NAME 

     else 'Vacant'

     end as 'Name'

       ,case when B4602200.BSSI_Tenant_Lease_Status =  2

     then B4602200.CUSTNMBR 

     else 'Vacant'

     end as 'Customer Number'

       ,case when B4602200.BSSI_Tenant_Lease_Status = 2

     then 'Active Lease'

     else 'No Active Lease'

     end as 'Lease Status'

       ,case when B4602200.BSSI_Tenant_Lease_Status =  2

     then B4602200.BSSI_Lease_Execution_Dat

     else 'Vacant'

     end as 'Move In Date'

       ,tblUnit.UnitCode

       ,tblUnit.BedroomCount

       ,tblUnit.CurrentStatus

       ,tblProject.[Project Number]

       ,tblUnit.UnitStatus

       ,tblUnit.ClassificationType

       ,tblUnit.UnitHousingType

       ,tlkpCommunity.CommunityName

       ,Trim([ADDRESS1]) & ", " & Trim([CITY]) & ", " & Trim([STATE]) & " " & Trim([Zip]) AS TenantAddress

       ,tblUnit.UnitID

       ,tblUnit.CommunityID

FROM ((B4602200 INNER JOIN (tblUnit INNER JOIN tblProject ON tblUnit.ProjectID = tblProject.ProjectID)

              ON B4602200.LOCNCODE = tblUnit.UnitCode)

              INNER JOIN tlkpCommunity ON tblUnit.CommunityID = tlkpCommunity.CommunityID)

              INNER JOIN RM00101 ON B4602200.CUSTNMBR = RM00101.CUSTNMBR

WHERE tblUnit.CurrentStatus = 'CRHA Use'

ORDER BY tblUnit.UnitCode

I am hoping you could help me decipher it. I need help on the case statements. I believe he is writing T-SQL against a SQL Server but I did tell him we were working with an access backend.

Thank you 

Art Lorenzini

Sioux Falls, sd

 

 

 

 

 

__._,_.___

---

Posted by: Bill Mosca <wrmosca@comcast.net>

---

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.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___