Click here to Skip to main content
12,998,394 members (70,864 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
Select * from Tb_Sch_TIme_Table;



Sno Sch_Date Session Course Faculty_Code
1 12/01/2013 1 AFF GS
2 12/01/2013 1 AFF NR
3 13/01/2013 1 TFC VB

I tried the below query several times, but I am not getting the correct output.
Collapse | Copy Code

SELECT Tb_Sch_TIme_Table.Sch_Date, Tb_Sch_TIme_Table.Session, Tb_Sch_TIme_Table.Course, Conc("Faculty_Code","Session",[Session],"Tb_Sch_TIme_Table") AS Faculty_Code
FROM Tb_Sch_TIme_Table
GROUP BY Tb_Sch_TIme_Table.Course, [Sch_Date], [Session];

Output as follows using the above query;

Sch_Date Session Course Faculty_Code
12/01/2013 1 AFF GS, NR, VB
13/01/2013 1 TFC GS, NR, VB

The above output is not correct.

I need the output as follows (compare with Select * from Tb_Sch_TIme_Table)


Sch_Date Session Course Faculty_Code
12/01/2013 1 AFF GS, NR
13/01/2013 1 TFC VB


Conc Function code as follows;

Option Compare Database
Option Explicit

Public Function Conc(Fieldx, Identity, Value, Source, Identity1, Value1) As Variant
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
Dim vFld As Variant

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
vFld = Null

SQL = "SELECT [" & Fieldx & "] as Fld" & _
" FROM [" & Source & "]" & _
" WHERE [" & Identity & "]=" & Value & _
" and [" & Identity1 & "]=" & Value1

' open recordset.
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

' concatenate the field.
Do While Not rs.EOF
If Not IsNull(rs!Fld) Then
vFld = vFld & ", " & rs!Fld
End If
rs.MoveNext
Loop
' remove leading comma and space.
vFld = Mid(vFld, 3)

Set cnn = Nothing
Set rs = Nothing

' return concatenated string.
Conc = vFld
End Function


How can I achieve this?
I tried several times, but my output is not matching with select * from Tb_Sch_TIme_Table.
Posted 27-Jan-13 17:14pm
Comments
lewax00 27-Jan-13 23:43pm
   
Repost of http://www.codeproject.com/Questions/535083/concatationplusqueryplusisplusnotplusworkingplusin

If you have additional details to add, please edit your original question. If not, be patient, we aren't paid to do your job, so you might not get an answer right away.
Member 8054539 28-Jan-13 2:29am
   
please reply i don't know see the concante function i send the conc function code and help me please.
Member 8054539 28-Jan-13 3:00am
   
please kindly update the solution for my above posted question conc function.
help me
Daniele Rota Nodari 28-Jan-13 6:45am
   
Hi.
I can see that the Conc function expects 6 parameters but only 4 are passed to it; then, the inner SQL uses all the 6 parameters (including the 2 unassigned).

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.170622.1 | Last Updated 27 Jan 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100