Help me to complete this code

[SF]

Hi,

I have a Access XP function below to list all province names related to a
particular Mission Order in one line (Province1, Province2, Province4) from
recordset connects to SQL 2000 Server.

Now how do I replace the last "," with " and " so that I get Province1,
Province2 and Province4.

Public Function ProvList(MO_Num As Integer)
Dim rs As New ADODB.Recordset
Dim I As Integer
Dim Txt As String
Dim SQL As String

SQL = "SELECT TOP 100 PERCENT dbo.tblProvinces.Pv_Province_e AS Province_e
FROM dbo.tblProject INNER JOIN"
SQL = SQL & " dbo.tblMissionOrderDetails ON dbo.tblProject.Pr_ObjectID =
dbo.tblMissionOrderDetails.md_ObjectID INNER JOIN"
SQL = SQL & " dbo.tblProvinces ON dbo.tblProject.Pr_ProvinceID =
dbo.tblProvinces.Pv_ProvinceID"
SQL = SQL & " WHERE (dbo.tblMissionOrderDetails.md_MissionOrder_No = " &
MO_Num & ") GROUP BY dbo.tblProvinces.Pv_Province_e"

rs.Open SQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
If rs.RecordCount > 0 Then
For I = 1 To rs.RecordCount
Txt = Txt & rs![Province_e] & ", "
rs.MoveNext
Next I
End If
Set rs = Nothing
ProvList = Left(Txt, Len(Txt) - 2)
End Function


SF

 

[Ron Weiner]

Add this as the last line in the function.

ProvList = left(ProvList,instrrev(ProvList,",")-1) & " and" &
mid(ProvList,instrrev(ProvList,",")+1,len(ProvList))

It finds the last comma and replaces it with an " and"

Ron W

 

[SF]

Thank you Ron W
SF

Add this as the last line in the function.

ProvList = left(ProvList,instrrev(ProvList,",")-1) & " and" &
mid(ProvList,instrrev(ProvList,",")+1,len(ProvList))

It finds the last comma and replaces it with an " and"

Ron W

Hi,

I have a Access XP function below to list all province names related to a
particular Mission Order in one line (Province1, Province2, Province4) from
recordset connects to SQL 2000 Server.

Now how do I replace the last "," with " and " so that I get Province1,
Province2 and Province4.

Public Function ProvList(MO_Num As Integer)
Dim rs As New ADODB.Recordset
Dim I As Integer
Dim Txt As String
Dim SQL As String

SQL = "SELECT TOP 100 PERCENT dbo.tblProvinces.Pv_Province_e AS Province_e
FROM dbo.tblProject INNER JOIN"
SQL = SQL & " dbo.tblMissionOrderDetails ON dbo.tblProject.Pr_ObjectID =
dbo.tblMissionOrderDetails.md_ObjectID INNER JOIN"
SQL = SQL & " dbo.tblProvinces ON dbo.tblProject.Pr_ProvinceID =
dbo.tblProvinces.Pv_ProvinceID"
SQL = SQL & " WHERE (dbo.tblMissionOrderDetails.md_MissionOrder_No = " &
MO_Num & ") GROUP BY dbo.tblProvinces.Pv_Province_e"

rs.Open SQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
If rs.RecordCount > 0 Then
For I = 1 To rs.RecordCount
Txt = Txt & rs![Province_e] & ", "
rs.MoveNext
Next I
End If
Set rs = Nothing
ProvList = Left(Txt, Len(Txt) - 2)
End Function


SF