• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

Create a String in VB

Hello everyone,  I am looking for a bit of help.  I have a macro that I created in excel/vb and I am pulling data from SQL.  I have and ID field that I enter in a table that I want to pull information from.  Now sometimes I need to combine these tables to be pulled at the same time.  The only thing that I know is that I have to make the field a string.  I am lost as to what I need to do now.  Here is my query and I have also attached it in a *.txt file.  Please help.  I have made the areas that I feel are the issue bold.

Application.ScreenUpdating = False
    Sheets("900 File (Plan Elections)").Select
    Range("A6").Select
   
    Dim cnDispatch As ADODB.Connection
    Set cnDispatch = New ADODB.Connection
   
    Dim CompanyID As Integer
    'CompanyID = Worksheets("Data_Worksheet").Cells(7, 4)
    CompanyID = Worksheets("Home Page").Cells(1, 8)
     
    Dim BenefitID As String
    'BenefitID = Worksheets("Home Page").Cells(2, 7)
    BenefitID = Worksheets("Home Page").Cells(2, 8)

     
    Dim strConn As String
     
    strConn = "PROVIDER=SQLOLEDB;"
    strConn = strConn & "SERVER=***.***.**.***;INITIAL CATALOG=*******;"
    strConn = strConn & " User ID=******; Password=********;"
    cnDispatch.Open strConn
     
        Dim rsDispatch As ADODB.Recordset
        Set rsDispatch = New ADODB.Recordset
         
            With rsDispatch
                .ActiveConnection = cnDispatch
                .Open " SELECT '900' AS 'TransactionCode'," & _
                " CASE WHEN PC.MemberType = 0 THEN EE.EmployeeID" & _
                " WHEN PC.MemberType = 1 THEN SP.SpouseID WHEN PC.MemberType = 2 THEN CH.ChildID END AS 'MemberID', EE.SSN AS 'EmployeeSSN', PC.MemberType AS 'MemberType'," & _
                " CASE WHEN PC.MemberType = 0 THEN ISNULL(EE.SSN,'~') WHEN PC.MemberType = 1 THEN ISNULL(SP.SSN,'~') WHEN PC.MemberType = 2 THEN ISNULL(CH.SSN,'~') END AS 'MemberSSN'," & _
                " CASE WHEN PC.MemberType = 0 THEN EE.FirstName WHEN PC.MemberType = 1 THEN SP.FirstName WHEN PC.MemberType = 2 THEN CH.FirstName END AS 'MemberFirstName'," & _
                " CASE WHEN PC.MemberType = 0 THEN EE.LastName WHEN PC.MemberType = 1 THEN SP.LastName WHEN PC.MemberType = 2 THEN CH.LastName END AS 'MemberLastName'," & _
                " PE.PlanModuleID AS 'PlanModuleID', CONVERT(VARCHAR(8), PE.EffectiveDate, 112) AS 'EffectiveDate'," & _
                " CONVERT(VARCHAR(8), PE.TerminationDate, 112) AS 'TerminationDate', '0' AS 'Continuance', '0' AS 'Quaifying Event Name'," & _
                " CONVERT(VARCHAR(8), PE.EffectiveDate, 112) AS 'QEventDate', PC.Coverage AS 'ApprovedCoverage', PC.EmployerCost AS 'ApprovedERCost'," & _
                " PC.EmployeeCost AS 'ApprovedEECost', PC.BuyUp AS 'ApprovedBuy-Up', PC.CompPercent AS 'ApprovedCompensationPercentage', PC.COBRAPremium AS 'ApprovedCOBRAPremium'," & _
                " PC.InitialCoverage AS 'ElectedCoverage', PC.InitialEmployerCost AS 'ElectedEmployerCost', PC.InitialEmployeeCost AS 'ElectedEmployeeCost'," & _
                " PC.InitialCOBRAPremium AS 'ElectedCOBRAPremium', PC.InitialBuyUp AS 'ElectedBuyUp', PC.InitialCompPercent AS 'ElectedCompensationPercentage'," & _
                " PC.ContribInterval AS 'ContribInterval', PC.ContribType AS 'ContribType', PE.TaxElection AS 'TaxElection'," & _
                " CASE WHEN PC.MemberType = 0 THEN CONVERT(VARCHAR(8), EE.DOB, 112) WHEN PC.MemberType = 1 THEN CONVERT(VARCHAR(8), SP.DOB, 112) WHEN PC.MemberType = 2 THEN CONVERT(VARCHAR(8), CH.DOB, 112) END AS 'MemberDOB'" & _
                " FROM PlanCoverages PC JOIN PlanElections PE ON PC.PlanID = PE.PlanID JOIN Employees EE ON EE.EmployeeID = PE.EmployeeID LEFT JOIN Spouses SP ON SP.SpouseID = PC.MemberID LEFT JOIN Children CH ON CH.ChildID = PC.MemberID" & _
                " WHERE EE.CompanyID = '" & CompanyID & "' AND PE.BenefitID = '" & BenefitID & "' AND PE.TerminationDate >= PE.EffectiveDate AND ((PE.EffectiveDate <= GETDATE()) AND (PE.TerminationDate IS NULL OR PE.TerminationDate >= GETDATE()))" & _
                " ORDER BY EE.SSN, PE.BenefitID, PE.EffectiveDate, PE.TerminationDate, MemberType, MemberDOB, MemberLastName, MemberFirstName"
                Sheets("900 File (Plan Elections)").Range("A6").CopyFromRecordset rsDispatch
                Sheets("Home Page").Select
                Range("G1").Select
                .Close
            End With
         
    cnDispatch.Close
    Set rsDispatch = Nothing
    Set cnDispatch = Nothing
    Application.ScreenUpdating = True

End Sub
String-Help.txt
0
mgmee
Asked:
mgmee
  • 4
  • 2
1 Solution
 
nutschCommented:
That should work if you have the right data in cell H2 of your home page tab
0
 
SteveCommented:
I think it possible that BenefitID may be a numeric field in the datasource.
IF this is the case you would need:

AND PE.BenefitID = " & BenefitID & " AND

rather than

AND PE.BenefitID = '" & BenefitID & "' AND
0
 
mgmeeAuthor Commented:
@ The_Barman : Yes the BenefitID is numeric.  I want to be able to enter two numbers, sometimes 3 in the field and have it populate (example: 12,13  or 1,3,11)  I tried to remove the apostrophe like you had above, and it didn't work.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SteveCommented:
Is companyID numeric also...

You will need to drop the ' either side of it otherwise the SQL reads the value as Text...

WHERE EE.CompanyID = " & CompanyID & " AND

rather than

WHERE EE.CompanyID = '" & CompanyID & "' AND
0
 
SteveCommented:
Do you have a working version of the query you could post the actual SQL for.
The .open command is a tough one to read with all the punctuation, the SQL would be simpler to read for errors. Ta.
0
 
SteveCommented:
If you want to have 2 or 3 numbers seperated by commas you will need the IsOneOf rather than EqualTo operator...

AND PE.BenefitID ln(" & BenefitID & ") AND
0
 
mgmeeAuthor Commented:
Thanks The_Barman!!! That solution worked GREAT!!!! I really appreciate it!!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now