MS Access - TSQL Case statement Work/Home Address

Hi Experts,

 I have this bit of VBA code:
 
Option Compare Database
Option Explicit
Dim RC As Long

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo Err_GroupHeader0_Format

    Me!LabelA = LabelBox(Me!Address, "tblNAsAddresses", "", "", 0)
    

    
Exit_GroupHeader0_Format:
    Exit Sub
    
Err_GroupHeader0_Format:
    RC = NOAHErr(2, "GroupHeader0_Format", Error$, Err, Me.Name)
    Resume Exit_GroupHeader0_Format
End Sub

Private Sub Report_Open(Cancel As Integer)
    Dim frm As Form
    Dim buf
    
On Error GoTo Err_Report_Open

    Set frm = Forms!frmNAsReportparm
    Me.RecordSource = SetSprParameters()
    
Exit_Report_Open:
    Exit Sub
Err_Report_Open:
    RC = NOAHErr(2, "Report_Open", Error$, Err, Me.Name)
    Resume Exit_Report_Open
    
End Sub

Open in new window


It calls a Stored Procedure:
Me.RecordSource = SetSprParameters()

 So I do not need to post the:
 " function LabelBox"
 part of the VBA code

The SP:
(
@WorkFileCode nvarchar(50), 
@ReportName nvarchar(100), 
@Login nvarchar(100),
@Date1 DATE,
@ChkBox4 BIT -- Home Address Selected
@ChkBox5 BIT -- show company-paid fees
)
AS
BEGIN
SELECT        Contact, NameSort, ContactName, Email, Phone, Address, MemberExpire, CompanyPaid, EmployerID, Code1, Code2, fncPSWAllIndividualRenew.Date2, GradYear, NewCode2, 
                         HotKey, HotKeyDescription, List, fncPSWAllIndividualRenew.AddressID, Expr1, Which, IsKeyAddress, IsInvoiceAddress, Hold, DirectoryAddress, EmployerAddress, Salutation, Adressee, 
                         Position, Company, Department, Address1, Address2, City, ZipCode, State, StateDesc, County, Region, Country, CountryCode, CountryDisplay, CITYST, 
                         SortLocation, @Date1 AS Date1
FROM            dbo.fncPSWAllIndividualRenew(NULL,NULL, @WorkFileCode, @Date1,YEAR(@date1)) fncPSWAllIndividualRenew JOIN
				tmpNAsRWWorkFile ON tmpNAsRWWorkFile.ContactID = fncPSWAllIndividualRenew.Contact AND 
				tmpNAsRWWorkFile.WorkFileCode = @WorkFileCode AND
				tmpNAsRWWorkFile.ReportName = @ReportName AND
				tmpNAsRWWorkFile.Login = @Login
WHERE		@ChkBox5 = CompanyPaid AND
			Code2 <> 'WIDOW'

Open in new window


So this part:
 Me!LabelA = LabelBox(Me!Address, "tblNAsAddresses", "", "", 0)

 More so:
 Me!Address

 comes from a "SP"
 Select Address
 From tblAddress

 so the "SP" currently works correctly for:
 No added button
 And
 member using the Work Address only.

 So I think I would need change the "SP" to something like:
 @ChkBox4 Bit --radio button parameter used on page
 Select
 Case when @ChkBox4 = '1' As Address
 From tblAddress Left Join fncAddressHome -- where fncAddressHome = member Home Address Only.
 On tblAddress .mem = fncAddressHome.mem


 But it looks like I will need help with getting the "Address" to change:
 (@ChkBox4 = '1' -- user checked the radio button for: Home Address
 Now keep in mind:  Home Or Work
  If  "fncAddressHome .mem" <> Home Address
 then use the Work Address.)

 @ChkBox4 = '0' -- User did not click on
 meaning the user is going to get the Work Address
 Like it is currently working.

 Please help and thanks..
SELECT        Contact, NameSort, ContactName, Email, Phone, Address, MemberExpire, CompanyPaid, EmployerID, Code1, Code2, fncPSWAllIndividualRenew.Date2, GradYear, NewCode2, 
                         HotKey, HotKeyDescription, List, fncPSWAllIndividualRenew.AddressID, Expr1, Which, IsKeyAddress, IsInvoiceAddress, Hold, DirectoryAddress, EmployerAddress, Salutation, Adressee, 
                         Position, Company, Department, Address1, Address2, City, ZipCode, State, StateDesc, County, Region, Country, CountryCode, CountryDisplay, CITYST, 
                         SortLocation, @Date1 AS Date1
FROM            dbo.fncPSWAllIndividualRenew(NULL,NULL, @WorkFileCode, @Date1,YEAR(@date1)) fncPSWAllIndividualRenew JOIN
				tmpNAsRWWorkFile ON tmpNAsRWWorkFile.ContactID = fncPSWAllIndividualRenew.Contact AND 
				tmpNAsRWWorkFile.WorkFileCode = @WorkFileCode AND
				tmpNAsRWWorkFile.ReportName = @ReportName AND
				tmpNAsRWWorkFile.Login = @Login
WHERE		@ChkBox5 = CompanyPaid AND
			Code2 <> 'WIDOW'

Open in new window

Amour22015Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Really can't tell what the question is, but if it helps here's a big honkin Access SQL to SQL Server translation article called Migrating your Access Queries to SQL Server Transact-SQL that I wrote awhile back.

Please restate your question in ten words or less.
Amour22015Author Commented:
So it looks like I need something like:
(
@WorkFileCode nvarchar(50), 
@ReportName nvarchar(100), 
@Login nvarchar(100),
@Date1 DATE,
@ChkBox4 BIT -- Home Address Selected
@ChkBox5 BIT -- show company-paid fees
)
AS
BEGIN
SELECT        Contact, NameSort, ContactName, Email, Phone, CASE(@ChkBox4 = '1') AS Address, MemberExpire, CompanyPaid, EmployerID, Code1, Code2, fncPSWAllIndividualRenew.Date2, GradYear, NewCode2, 
                         HotKey, HotKeyDescription, List, fncPSWAllIndividualRenew.AddressID, Expr1, Which, IsKeyAddress, IsInvoiceAddress, Hold, DirectoryAddress, EmployerAddress, Salutation, Adressee, 
                         Position, Company, Department, Address1, Address2, City, ZipCode, State, StateDesc, County, Region, Country, CountryCode, CountryDisplay, CITYST, 
                         SortLocation, @Date1 AS Date1
FROM            dbo.fncPSWAllIndividualRenew(NULL,NULL, @WorkFileCode, @Date1,YEAR(@date1)) fncPSWAllIndividualRenew JOIN
				tmpNAsRWWorkFile ON tmpNAsRWWorkFile.ContactID = fncPSWAllIndividualRenew.Contact AND 
				tmpNAsRWWorkFile.WorkFileCode = @WorkFileCode AND
				tmpNAsRWWorkFile.ReportName = @ReportName AND
				tmpNAsRWWorkFile.Login = @Login Left Join fncAddressHome -- where fncAddressHome = member Home Address Only.
  On tmpNAsRWWorkFile.ContactID = fncAddressHome.Contact
WHERE		@ChkBox5 = CompanyPaid AND
			Code2 <> 'WIDOW'

Open in new window


But clearly I am going to need some help

Thanks
Amour22015Author Commented:
Jim I am not Migrating anything.

This part in the VBA Code:
Me!Address

Is a parameter from the SP:
SELECT        Contact, NameSort, ContactName, Email, Phone, CASE(@ChkBox4 = '1') AS Address,

Address

The Address needs to change Home/Work Address when a button on a page is clicked on.

Thanks
(
@WorkFileCode nvarchar(50), 
@ReportName nvarchar(100), 
@Login nvarchar(100),
@Date1 DATE,
@ChkBox4 BIT -- Home Address Selected
@ChkBox5 BIT -- show company-paid fees
)
AS
BEGIN
SELECT        Contact, NameSort, ContactName, Email, Phone, CASE(@ChkBox4 = '1') AS Address, MemberExpire, CompanyPaid, EmployerID, Code1, Code2, fncPSWAllIndividualRenew.Date2, GradYear, NewCode2, 
                         HotKey, HotKeyDescription, List, fncPSWAllIndividualRenew.AddressID, Expr1, Which, IsKeyAddress, IsInvoiceAddress, Hold, DirectoryAddress, EmployerAddress, Salutation, Adressee, 
                         Position, Company, Department, Address1, Address2, City, ZipCode, State, StateDesc, County, Region, Country, CountryCode, CountryDisplay, CITYST, 
                         SortLocation, @Date1 AS Date1
FROM            dbo.fncPSWAllIndividualRenew(NULL,NULL, @WorkFileCode, @Date1,YEAR(@date1)) fncPSWAllIndividualRenew JOIN
				tmpNAsRWWorkFile ON tmpNAsRWWorkFile.ContactID = fncPSWAllIndividualRenew.Contact AND 
				tmpNAsRWWorkFile.WorkFileCode = @WorkFileCode AND
				tmpNAsRWWorkFile.ReportName = @ReportName AND
				tmpNAsRWWorkFile.Login = @Login Left Join fncAddressHome -- where fncAddressHome = member Home Address Only.
  On tmpNAsRWWorkFile.ContactID = fncAddressHome.Contact
WHERE		@ChkBox5 = CompanyPaid AND
			Code2 <> 'WIDOW'

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Amour22015Author Commented:
This has noting to do with Migrating from Access to TSQL.

This is only a TSQL Question.

In the SP the Selected "Address" is what changes and I will also need to Left Join:
fncAddressHome -- where fncAddressHome = member Home Address Only.
  On tmpNAsRWWorkFile.ContactID = fncAddressHome.Contact

In my first post I am trying to give everything needed to show the link between Access and TSQL
I use Access for "Reports" and the Home/Work Address comes out on the report depending on:
User clicking on a radio button on a page and that Work Address is one of the records.  If no Home Address (even when user clicks on radio button) then it will give a Work Address.

Thanks for any help.
Nick67Commented:
Ok
Me.RecordSource = SetSprParameters()
But that's a function.
Let's see it!

because you are passing in a pile of parameters
@WorkFileCode nvarchar(50),
@ReportName nvarchar(100),
@Login nvarchar(100),
@Date1 DATE,
@ChkBox4 BIT -- Home Address Selected
@ChkBox5 BIT -- show company-paid fees

But, I think I understand your question, you want something conditional based on
@ChkBox4 BIT -- Home Address Selected
so

in the T-SQL you need if then

if @ChkBox4 BIT <> 1 then
SELECT        Contact, NameSort, ContactName, Email, Phone, CASE(@ChkBox4 = '1') AS Address, MemberExpire, CompanyPaid, EmployerID, Code1, Code2, fncPSWAllIndividualRenew.Date2, GradYear, NewCode2,
                         HotKey, HotKeyDescription, List, fncPSWAllIndividualRenew.AddressID, Expr1, Which, IsKeyAddress, IsInvoiceAddress, Hold, DirectoryAddress, EmployerAddress, Salutation, Adressee,
                         Position, Company, Department, Address1, Address2, City, ZipCode, State, StateDesc, County, Region, Country, CountryCode, CountryDisplay, CITYST,
                         SortLocation, @Date1 AS Date1
FROM            dbo.fncPSWAllIndividualRenew(NULL,NULL, @WorkFileCode, @Date1,YEAR(@date1)) fncPSWAllIndividualRenew JOIN
                        tmpNAsRWWorkFile ON tmpNAsRWWorkFile.ContactID = fncPSWAllIndividualRenew.Contact AND
                        tmpNAsRWWorkFile.WorkFileCode = @WorkFileCode AND
                        tmpNAsRWWorkFile.ReportName = @ReportName AND
                        tmpNAsRWWorkFile.Login = @Login Left Join fncAddressHome -- where fncAddressHome = member Home Address Only.
  On tmpNAsRWWorkFile.ContactID = fncAddressHome.Contact
WHERE            @ChkBox5 = CompanyPaid AND
                  Code2 <> 'WIDOW'
else
'appropriate T-SQL here
end
Amour22015Author Commented:
Mentions:
Me.RecordSource = SetSprParameters()
 But that's a function.
 Let's see it!

Not a function it is a SP. But within the posted SP it calls a function.

Turns out that I had to:
First alter: fncPSWAllIndividualRenew

like:
Case When @chkBox4 = 1 then ISNULL(fncUtlAddressSingle.AddressID,IOUs.[Address]) Else IOUs.[Address] End AS Address

And:
Left Join fncUtlAddressSingle('Home',1) ON IOUs.Contact = fncUtlAddressSingle.Contact LEFT OUTER JOIN
                        dbo.fncUtlAddress() AS fncUtlAddress ON Case When @chkBox4 = 1 then ISNULL(fncUtlAddressSingle.AddressID,IOUs.[Address]) Else IOUs.[Address] End = fncUtlAddress.AddressID

And then put the @chkBox4 in every dependencies with a return

Thanks for helping
Nick67Commented:
Not a function it is a SP
Not a chance.
Look at the brackets on the end of SetSprParameters()
It's a VBA function that is setting the SQL of a passthrough query object someplace.
And those parameters are going in, so they have to come from somewhere.
But no matter.
It is what the sproc does with the parameter that matters

There's many way to do it.
A stored procedure is not limited to a single select statement and can call UDF as you are doing, too.
I'd probably declare a table variable and then do a select/insert for the appropriate address.
If that returned 0 rows, I then do a select/insert for the default address.
Then in the final select returned to Access, I'd select the fields of the table variable along with everything else.
Whatever works!
I do try to avoid UDFs though.  They can be performance killers if you are not careful.

Do you have the problem solved?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amour22015Author Commented:
Yes I finally solved the problem, had someone here help me that knew more about the DB, I just started at the company.

Thank you for helping me.
Amour22015Author Commented:
Great thank you for clarifying
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.