?
Solved

VB and Access: Concatenate values based on IF statement

Posted on 2004-11-08
7
Medium Priority
?
175 Views
Last Modified: 2012-05-05
Hi all,

I have a function (below, written by someone else) that I use in an access query. It references another query that contains an ID field, a field called PercentCover (which contains one of four letters) and a field called Species (which is a number).   THe Species number refers to the place of the Species in a list of 116 plants (stored in a table).

At the moment the function opens the first query and concatenates into a new field all values (comma separated) of PercentCover that have the same ID number. What I'm wanting to do is edit the function so that it creates a comma separated field in the new query containing all the values of PercentCover associated with ID 6 but also represent all the other species in the species table that are missing by adding commas in the appropriate places. For example, if ID 6 contained 'Calluna' and 'Rhodo', and 'Calluna' was third in the species list while 'Rhodo.' was fifth, the output would be something like <,,D,,O,,,>.

I have a rough idea what I need to do and have got so far with editing it but am not getting anywhere. I've highlighted the part I'm having problems with. Any help with this would be much appreciated. The original code for the function plus an explantion of what it does is at the following url

http://www.mvps.org/access/modules/mdl0008.htm

Thanks in advance

Chris


*****CODE STARTS*******

Function fConcatComma(stTable As String, _
                    stForFld As String, _
                    stFldToConcat As String, _
                    stForFldType As String, _
                    vForFldVal As Variant) _
                    As String

Dim lodb As DAO.Database, lors As DAO.Recordset
Dim lovConcat As Variant, loCriteria As String
Dim loSQL As String
Dim CurrentSpeciesVal As Long, PreviousSpeciesVal As Long
Dim stringval As Long
Dim test As String
Const cQ = """"
CurrentSpeciesVal = 1
PreviousSpeciesVal = 1

    On Error GoTo Err_fConcatComma
   
    Set lodb = CurrentDb
   
    loSQL = "SELECT [" & stFldToConcat & "], [Species] FROM ["
    loSQL = loSQL & stTable & "] WHERE "
   
    Select Case stForFldType
        Case "String":
            loSQL = loSQL & "[" & stForFld & "] =" & cQ & vForFldVal & cQ
        Case "Long", "Integer", "Double":    'AutoNumber is Type Long
            loSQL = loSQL & "[" & stForFld & "] = " & vForFldVal
        Case Else
            GoTo Err_fConcatComma
    End Select
   
    Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)
   
    'Are we sure that duplicates exist in stFldToConcat
    With lors
        If .RecordCount <> 0 Then
            'start concatenating records
            Do While Not .EOF

'I HAVE EDITED THE CODE AND HAVE PROBLEMS FROM HERE

                If lors("Species") = CurrentSpeciesVal Then
                    lovConcat = lovConcat & lors(stFldToConcat) & ","
                    PreviousSpeciesVal = lors("Species")
                    .MoveNext
                    CurrentSpeciesVal = lors("Species")
                Else
                    MsgBox ("Second Part")
                    stringval = CurrentSpeciesVal - PreviousSpeciesVal
                    lovConcat = lovConcat & String(stringval, ",")
                    .MoveNext
                    CurrentSpeciesVal = lors("Species")
                End If

'TO HERE

            Loop
        Else
            GoTo Exit_fConcatComma
        End If
    End With
       
    'That's it... you should have a concatenated string now
    'Just Trim the trailing ;
    fConcatComma = "<" & Left(lovConcat, Len(lovConcat) - 1) & ">"
       

Exit_fConcatComma:
    Set lors = Nothing: Set lodb = Nothing
    Exit Function

Err_fConcatComma:
    MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
    Resume Exit_fConcatComma
End Function
0
Comment
Question by:cd3chris
  • 3
  • 2
6 Comments
 
LVL 3

Expert Comment

by:leeskelton83
ID: 12523459
Can you describe the problems you encounter and include where they occur?
0
 

Author Comment

by:cd3chris
ID: 12523589
Hi. Thanks for your interest but I've just managed to sort the problem myself. Not sure what I should do to close the query though. Cheers.

Chris
0
 

Author Comment

by:cd3chris
ID: 12523619
For those who are interested here is my fixed code .

Function fConcatComma(stTable As String, _
                    stForFld As String, _
                    stFldToConcat As String, _
                    stForFldType As String, _
                    vForFldVal As Variant) _
                    As String

Dim lodb As DAO.Database, lors As DAO.Recordset
Dim lovConcat As Variant, loCriteria As String
Dim loSQL As String
Dim CurrentSpeciesVal As Long, PreviousSpeciesVal As Long
Dim stringval As Long
Dim CommaChoice As String
Const cQ = """"
CurrentSpeciesVal = 1
PreviousSpeciesVal = 1

    On Error GoTo Err_fConcatComma
   
    Set lodb = CurrentDb
   
    loSQL = "SELECT [" & stFldToConcat & "], [Species] FROM ["
    loSQL = loSQL & stTable & "] WHERE "
   
    Select Case stForFldType
        Case "String":
            loSQL = loSQL & "[" & stForFld & "] =" & cQ & vForFldVal & cQ
        Case "Long", "Integer", "Double":    'AutoNumber is Type Long
            loSQL = loSQL & "[" & stForFld & "] = " & vForFldVal
        Case Else
            GoTo Err_fConcatComma
    End Select
   
    Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)
   
    'Are we sure that duplicates exist in stFldToConcat
    With lors
        If .RecordCount <> 0 Then
            'start concatenating records
            Do While Not .EOF
               
                If lors("Species") > CurrentSpeciesVal Then
                   
                    stringval = lors("Species") - CurrentSpeciesVal
                       
                        If stringval = 0 Then
                            stringval = 1
                        End If
                   
                    CommaChoice = String(stringval, ",") & lors(stFldToConcat)
                     
                Else
                    CommaChoice = lors(stFldToConcat) & ","
                End If
               
                lovConcat = lovConcat & CommaChoice
                CurrentSpeciesVal = lors("Species")
               
            .MoveNext
           
            If .EOF Then
                stringval = 116 - CurrentSpeciesVal
                CommaChoice = String(stringval, ",")
                lovConcat = lovConcat & CommaChoice
            End If
           
            Loop
        Else
            GoTo Exit_fConcatComma
        End If
    End With
       
    'That's it... you should have a concatenated string now
    'Just Trim the trailing ;
    'fConcatComma = "<" & Left(lovConcat, Len(lovConcat) - 1) & ">"
       
   
    fConcatComma = "<" & lovConcat & ">"

Exit_fConcatComma:
    Set lors = Nothing: Set lodb = Nothing
    Exit Function

Err_fConcatComma:
    MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
    Resume Exit_fConcatComma
End Function
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 3

Expert Comment

by:leeskelton83
ID: 12523872
Setting the Recordset to nothing should close it and wipthe memory clean.
0
 

Author Comment

by:cd3chris
ID: 12524119
Sorry, I shouldnt have used the word 'query', I should have said how do I close this question! Have left a message in the Community Support area so hopefully it'll be closed. THanks for the input Lee. Cheers.

Chris
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12574150
Closed, 500 points refunded.

modulo
Community Support Moderator
Experts Exchange
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month5 days, 13 hours left to enroll

589 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