Create a String in VB

Posted on 2012-08-13
Last Modified: 2012-08-13
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
    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
            End With
    Set rsDispatch = Nothing
    Set cnDispatch = Nothing
    Application.ScreenUpdating = True

End Sub
Question by:mgmee
    LVL 39

    Expert Comment

    That should work if you have the right data in cell H2 of your home page tab
    LVL 24

    Expert Comment

    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

    Author Comment

    @ 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.
    LVL 24

    Expert Comment

    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
    LVL 24

    Expert Comment

    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.
    LVL 24

    Accepted Solution

    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

    Author Closing Comment

    Thanks The_Barman!!! That solution worked GREAT!!!! I really appreciate it!!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
    Article by: Leon
    Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now