We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now


build string in vba

Medium Priority
Last Modified: 2012-05-11
I have written some code that goes through some codes where tests failed and attemps to build a string telling me what failed.

I cannot see where to move the code that writes the string to the table as if a client code fails with more than one test the output string is built up incorrectly.

AbiCodeMvris              AbiCode         TestDescription      TestResult      strResult
04048501M3BSJ      04048501          BHP                            0                   BHP,
04048501M3BSJ      04048501          Door                            0            BHP, Door,

the strresult should read: BHP, Door, in both cases

if i move outside the loop then it wont work either and within the loop it does this.
i am in access 2010 vba
Public Function getFailureString()
Dim strSelect As String
Dim strFrom As String
Dim strWhere As String
Dim strQuery As String
Dim strSelectOuter As String
Dim strFromOuter As String
Dim strGroupByOuter As String
Dim strHavingOuter As String
Dim strQueryOuter As String
Dim strOutput As String
Dim db As Database
Set db = CurrentDb
Dim RstOuter As DAO.Recordset
Dim RStFailures As DAO.Recordset
Dim ClientCodeMvris As String
Dim rcount As Long
Dim RstResultTable As DAO.Recordset

strSelectOuter = "SELECT TblAbiTestResults.AbiCodeMvris, TblAbiTestResults.TestResult"
strFromOuter = " FROM TblAbiTestResults"
strGroupByOuter = " GROUP BY TblAbiTestResults.AbiCodeMvris, TblAbiTestResults.TestResult"
strHavingOuter = " HAVING (((TblAbiTestResults.TestResult)=0));"

strQueryOuter = strSelectOuter & strFromOuter & strGroupByOuter & strHavingOuter

Set RstOuter = db.OpenRecordset(strQueryOuter)

Set RstResultTable = db.OpenRecordset("TblAbiTestResults")

strSelect = "SELECT TblAbiTestResults.AbiCodeMvris, TblAbiTestResults.TestResult, TblAbiTestResults.TestDescription, TblAbiTestResults.strResult"
strFrom = " FROM TblAbiTestResults"

'get record count to later deal with comma at end issue ie if next record end dont add comma to string
rcount = RstOuter.RecordCount

With RstOuter
'loop records in outer
Do While .EOF <> True
    'get the clientcodemvris combination
     ClientCodeMvris = .Fields("AbiCodeMvris").Value
'     Debug.Print .Fields("AbiCodeMvris").Value
    'create query based on clientcode
     strWhere = " WHERE (((TblAbiTestResults.AbiCodeMvris)=""" & ClientCodeMvris & """) AND ((TblAbiTestResults.TestResult)=0));"
    'create string
    strQuery = strSelect & strFrom & strWhere
'     Debug.Print strQuery
     Set RStFailures = db.OpenRecordset(strQuery, dbOpenDynaset)

'    Debug.Print strQuery
        With RStFailures
           'check for no records
           If RStFailures.BOF And RStFailures.EOF Then
           Do While .EOF <> True
                'get textual description of why test failed and add to string
               strOutput = strOutput & .Fields("testdescription").Value & ", "
                .Fields("strresult").Value = strOutput
                'because the string is being built in stages records with more than one test failure show string build up
'               Debug.Print strOutput

           End If

        End With
    strOutput = ""

End With

End Function

Open in new window

Watch Question

The logic when building the string is wrong.

Replace lines 76 - 89 with the code attached.
Do While .EOF <> True
    strOutput = strOutput & .Fields("testdescription").Value & ", "

.MoveFirstDo While .EOF <> True
    .Fields("strresult").Value = strOutput

Open in new window

EDIT: Use this one...
Do While .EOF <> True
    strOutput = strOutput & .Fields("testdescription").Value & ", "
Do While .EOF <> True
    .Fields("strresult").Value = strOutput

Open in new window


is it possible for it not too add a comma on when it writes the last part of the string

BHP, Transmission,

should become
BHP, Transmission
Unlock this solution and get a sample of our free trial.
(No credit card required)


thank you
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.