Active TopicsActive Topics  Display List of Forum MembersMemberlist  Search The ForumSearch  HelpHelp
  RegisterRegister  LoginLogin
Please consider donating $1 per sample downloaded, find out why here. If downloading multiple samples, please donate once for all of them.
Amazon Honor System  Click Here
to Pay Learn More
Access 97, 2000, 2002 (XP) (Forum Locked Forum Locked)
 RAL Forums : Access 97, 2000, 2002 (XP)
Subject Topic: Remove comma from last record. Post ReplyPost New Topic
Author
Message << Prev Topic | Next Topic >>
myhnews
Newbie
Newbie


Joined: 30†August†2004
Location: United States
Posts: 3
Posted: 10†September†2004 at 1:06pm | IP Logged Quote myhnews

Iím currently using the function below,
However, this function also removes the Comma from the last 2 sub records. I would like the function to remove the comma from the last sub record only.

Can anyone please please tell me what line I have to modify?

Thanks
code:


Function LocationString(CompID As Long)
Dim db              As DAO.Database
Dim rs              As DAO.Recordset
Dim CompanyID       As Long
Dim Location        As String
Dim LocList        As String
Dim lngOneComma     As Long
Dim lngTwoComma     As Long
Dim tmpString       As String
Dim i               As Long
Dim address2 As String ''''''''''
Dim address3 As String

Set db = CurrentDb
Set rs = db.OpenRecordset("tblLocations")
LocList = ""
Do While Not rs.EOF
    CompanyID = Nz(rs(0))
    Location = Nz(rs(1)) & " "
    address2 = Nz(rs(2)) & " " '''''''''
    address3 = Nz(rs(3)) '& " "
    
        If (CompanyID = CompID) Then
        LocList = LocList & Location & address2 & address3 & ", "
    End If
    
   ' If (CompanyID = CompID) Then
   '     LocList = LocList & Location & ", "
'   End If
    
    rs.MoveNext
Loop

lngOneComma = 0
lngTwoComma = 0

For i = 1 To Len(LocList)
    If Mid(LocList, i, 1) = "," Then
        lngTwoComma = lngOneComma
        lngOneComma = i
    End If
Next i

tmpString = ""
If lngTwoComma = 0 & lngOneComma <> 0 Then
    For i = 1 To Len(LocList)
        If i = lngOneComma Then
            tmpString = tmpString
        Else
            tmpString = tmpString & Mid(LocList, i, 1)
        End If
    Next i
ElseIf lngTwoComma <> 0 & lngOneComma <> 0 Then
    For i = 1 To Len(LocList)
        If i = lngTwoComma Then
            tmpString = tmpString & " and"
        ElseIf i = lngOneComma Then
            tmpString = tmpString
        Else
            tmpString = tmpString & Mid(LocList, i, 1)
        End If
    Next i
Else
    tmpString = LocList
End If

LocationString = tmpString

End Function

Back to Top View myhnews's Profile Search for other posts by myhnews Send Private Message Add to Buddy List
 

Sorry, you can NOT post a reply.
This forum has been locked by a forum administrator.

  Post ReplyPost New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

Powered by Web Wiz Forums version 7.01
Copyright ©2001-2003 Web Wiz Guide