Solved List results with a comma

[oxicottin]

Hello, I have a query that gives me a list of dates from (AbsentDate) and I want to get a result as a row each separated by a comma and not a column like in the image. How is this done? Thanks

SELECT tbl_YearCalendar.AbsenceDate
FROM tbluAbsenceCodes INNER JOIN tbl_YearCalendar ON tbluAbsenceCodes.AbsenceID = tbl_YearCalendar.AbsenceID
WHERE (((tbl_YearCalendar.EmployeeID)=[forms]![frm_YearCalendar]![cboEmployee]) AND ((tbluAbsenceCodes.AbsenceCode) Not In ("V","VFML","VMA","FMLA","F","JD","ML","PC","SFML","PD","PPL","SD","C-19")))
GROUP BY tbl_YearCalendar.AbsenceDate
HAVING (((tbl_YearCalendar.AbsenceDate) Between DateAdd("m",-6,[Forms]![frm_CalendarInputBox]![subFormCalendarInputBox].[Form]![AbsenceDate]) And [Forms]![frm_CalendarInputBox]![subFormCalendarInputBox].[Form]![AbsenceDate]))
ORDER BY tbl_YearCalendar.AbsenceDate;

 

[arnelgp]

can you use Allen Browne's ConcatRelated() function?

 

[Uncle Gizmo]

I agree with Arnelgp.

I also have an explanation on my website here:-

Display Field Data Horizontally - Nifty Access

Transform, Concatenate Examples A common requirement in MS Access is to extract Columnar data into horizontal data, for instance to make reports look better, to provide a list of emails suitable to send to with Outlook. In the YouTube videos below I explain how this can be done. Display Field...

www.niftyaccess.com

http://

 

[oxicottin]

Ok thanks.... Im going to check it out now!

 

[theDBguy]

Hi. Just another option...

Code Snippets

 

[oxicottin]

@theDBguy I'm lost with this and am not getting it...... I have a query that gives me a result of one row of dates "AbsenceDate". With the code you provided how do I get that querys result to be one row of dates?

SQL:

SELECT
FROM tbluAbsenceCodes INNER JOIN tbl_YearCalendar ON tbluAbsenceCodes.AbsenceID = tbl_YearCalendar.AbsenceID
WHERE (((tbl_YearCalendar.EmployeeID)=[forms]![frm_YearCalendar]![cboEmployee]) AND ((tbluAbsenceCodes.AbsenceCode) Not In ("V","VFML","VMA","FMLA","F","JD","ML","PC","SFML","PD","PPL","SD","C-19")))
GROUP BY tbl_YearCalendar.AbsenceDate
HAVING (((tbl_YearCalendar.AbsenceDate) Between DateAdd("m",-6,[Forms]![frm_CalendarInputBox]![subFormCalendarInputBox].[Form]![AbsenceDate]) And [Forms]![frm_CalendarInputBox]![subFormCalendarInputBox].[Form]![AbsenceDate]));

 

[MajP]

For ease lets assume you save your query as qryAbsence. Then you can show the dates in a calculated control or other query like.
=SimpleCSV("qryAbsence")

 

[theDBguy]

@theDBguy I'm lost with this and am not getting it...... I have a query that gives me a result of one row of dates "AbsenceDate". With the code you provided how do I get that querys result to be one row of dates?

SQL:

SELECT
FROM tbluAbsenceCodes INNER JOIN tbl_YearCalendar ON tbluAbsenceCodes.AbsenceID = tbl_YearCalendar.AbsenceID
WHERE (((tbl_YearCalendar.EmployeeID)=[forms]![frm_YearCalendar]![cboEmployee]) AND ((tbluAbsenceCodes.AbsenceCode) Not In ("V","VFML","VMA","FMLA","F","JD","ML","PC","SFML","PD","PPL","SD","C-19")))
GROUP BY tbl_YearCalendar.AbsenceDate
HAVING (((tbl_YearCalendar.AbsenceDate) Between DateAdd("m",-6,[Forms]![frm_CalendarInputBox]![subFormCalendarInputBox].[Form]![AbsenceDate]) And [Forms]![frm_CalendarInputBox]![subFormCalendarInputBox].[Form]![AbsenceDate]));

Hi. Unfortunately, your query has a parameter in it, which means mine and Allen's code will both fail.

You could try to modify my code to use this other function

Code Snippets

 

[oxicottin]

@MajP I tried that and I get a error

Run-time error '3061':
Too few parameters. Expected 2.

 

[theDBguy]

@MajP I tried that and I get a error

Run-time error '3061':
Too few parameters. Expected 2.

Hi. That's the error I was referring to in my post above about using a parameter query.

 

[oxicottin]

@theDBguy I wouldn't know how to modify that code to work.... Sorry I didn't see your last post before I posted with the error.

 

[MajP]

Call the function passing in the empID, startDate, and endDate

Public Function GetDates(EmpID As String, DateStart As Date, DateEnd As Date)
  Dim strSql As String
  Dim strDateStart As String
  Dim strDateEnd As String
  Dim rs As DAO.Recordset
  Dim strCSV As String

  strDateStart = "#" & Format(DateStart, "MM/DD/YYYY") & "#"
  strDateEnd = "#" & Format(DateEnd, "MM/DD/YYYY") & "#"


  strSql = " SELECT tbl_YearCalendar.AbsenceDate FROM tbluAbsenceCodes INNER JOIN tbl_YearCalendar ON tbluAbsenceCodes.AbsenceID = tbl_YearCalendar.AbsenceID "
  strSql = strSql & "WHERE tbl_YearCalendar.EmployeeID = " & EmpID & " AND tbluAbsenceCodes.AbsenceCode Not In ('V','VFML','VMA','FMLA','F','JD','ML','PC','SFML','PD','PPL','SD','C-19') "
  strSql = strSql & "GROUP BY tbl_YearCalendar.AbsenceDate HAVING tbl_YearCalendar.AbsenceDate Between " & strStartDate & " And " & strEndDate
  strSql = strSql & " ORDER BY tbl_YearCalendar.AbsenceDat "
 'verify the SQL works
 debug.print strSql

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)

'Concatenate the first (and should be the only one) field from the SQL statement
With rs
    Do While Not .EOF
        strCSV = strCSV & strDelim & .Fields(0)
        .MoveNext
    Loop
    .Close
End With

'Remove the leading delimiter and return the result
GetDates = Mid$(strCSV, Len(strDelim) + 1)

Set rs = Nothing
Set db = Nothing

End Function

 

[theDBguy]

@theDBguy I wouldn't know how to modify that code to work.... Sorry I didn't see your last post before I posted with the error.

Okay, I'm on the road right now. I'll post something later.

Edit: Oops, too slow.

 

[MajP]

Or you can still use the original function but resolve the SQL string first then pass it in to simpleCSV

 

[oxicottin]

@MajP your function confuses me because my SQL for the dates are

Between DateAdd("m",-6,[Forms]![frm_CalendarInputBox]![subFormCalendarInputBox].[Form]![AbsenceDate])

So my start date is "AbsenceDate" and my end date is "DateAdd("m",-6". Would I use it like:

=GetDates([EmpID],[AbsenceDate],DateAdd("m",-6)

 

[MajP]

That is kind of confusing verbiage.

So my start date is "AbsenceDate" and my end date is "DateAdd("m",-6"

The thing you want to call the end date is six months before the end of the range and you want to call that the start date? You want to call the absence date which is 6 months later than the beginning of the range the startdate?

I would expect some code like this

dim AbDate as date
dim AbDateMinus6 as date
dim empID as long

empID = [forms]![frm_YearCalendar]![cboEmployee]
abDate = [Forms]![frm_CalendarInputBox]![subFormCalendarInputBox].[Form]![AbsenceDate]
abdateMinus6 = dateAdd("m",-6, AbDate)

Me.SomeTextBox = getDate(empID, abDateMinus6, AbDate)

In my function I put EMPID as a string argument. Change that to long.

If you want it to make more sense to you then maybe change the arguments

Public Function GetDates(EmpID As Long, AbsenceDateMinus6 As Date, AbsenceDate As Date)

Then do a find and replace on the start and end dates.

 

[oxicottin]

Ok now I'm starting to see what your doing but I'm getting a different error and it highlights
Me.txtAbsentDates = GetDates(empID, AbDateMinus6, AbDate) that I put in my reports on open event.

Run-time error'-2147352567 (800020009)':
You cant assign a value to this object.

The Debug.print gives the correct sql which shows in the immediate window below:

SELECT tbl_YearCalendar.AbsenceDate FROM tbluAbsenceCodes INNER JOIN tbl_YearCalendar ON tbluAbsenceCodes.AbsenceID = tbl_YearCalendar.AbsenceID WHERE tbl_YearCalendar.EmployeeID = 34 AND tbluAbsenceCodes.AbsenceCode Not In ('V','VFML','VMA','FMLA','F','JD','ML','PC','SFML','PD','PPL','SD','C-19') GROUP BY tbl_YearCalendar.AbsenceDate HAVING tbl_YearCalendar.AbsenceDate Between #01/01/2021# And #07/01/2021# ORDER BY tbl_YearCalendar.AbsenceDate

In your function I had to add a few "Variable not defined" so I added those. I changed the empId from string to long also as suggested.
Below is the function:

Public Function GetDates(empID As Long, DateStart As Date, DateEnd As Date)
  Dim strSql, strDelim, strCSV, strStartDate, strEndDate As String
  Dim rs As dao.Recordset
  Dim db As dao.Database

  strStartDate = "#" & Format(DateStart, "MM/DD/YYYY") & "#"
  strEndDate = "#" & Format(DateEnd, "MM/DD/YYYY") & "#"


  strSql = " SELECT tbl_YearCalendar.AbsenceDate FROM tbluAbsenceCodes INNER JOIN tbl_YearCalendar ON tbluAbsenceCodes.AbsenceID = tbl_YearCalendar.AbsenceID "
  strSql = strSql & "WHERE tbl_YearCalendar.EmployeeID = " & empID & " AND tbluAbsenceCodes.AbsenceCode Not In ('V','VFML','VMA','FMLA','F','JD','ML','PC','SFML','PD','PPL','SD','C-19') "
  strSql = strSql & "GROUP BY tbl_YearCalendar.AbsenceDate HAVING tbl_YearCalendar.AbsenceDate Between " & strStartDate & " And " & strEndDate
  strSql = strSql & " ORDER BY tbl_YearCalendar.AbsenceDate "
'verify the SQL works
Debug.Print strSql

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)

'Concatenate the first (and should be the only one) field from the SQL statement
With rs
    Do While Not .EOF
        strCSV = strCSV & strDelim & .Fields(0)
        .MoveNext
    Loop
    .Close
End With

'Remove the leading delimiter and return the result
GetDates = Mid$(strCSV, Len(strDelim) + 1)

Set rs = Nothing
Set db = Nothing

End Function

All that being said any idea on the error message?

 

[theDBguy]

Okay, I'm on the road right now. I'll post something later.

Edit: Oops, too slow.

Okay, I just put this one together very quickly, so please let me know if you get any errors.

Use this updated version of the SimpleCSV() function and basically use it like @MajP showed you in Post #7.

SimpleCSV() v2.0

A few years ago, I wrote a simple function called SimpleCSV() to concatenate records and posted it on my website. One main drawback of thi...

thedbguy.blogspot.com

 

[oxicottin]

@MajP I finally got a good result with your function but the result is like the image without a comma in-between each date.

I changed your function around and called it from the reports query using Test: GetDates() and now it opens with no errors but like I said all jumbled together.

Here is the function im now trying that seems to work.

Public Function GetDates()
  Dim strSql, strDelim, strCSV, strStartDate, strEndDate As String
  Dim rs As dao.Recordset
  Dim db As dao.Database

  Dim AbsentDate As Date
  Dim AbsentDateMinus6 As Date
  Dim empID As Long

  empID = [Forms]![frm_YearCalendar]![cboEmployee]
  AbsentDate = [Forms]![frm_CalendarInputBox]![subFormCalendarInputBox].[Form]![AbsenceDate]
  AbsentDateMinus6 = DateAdd("m", -6, AbsentDate)


  strStartDate = "#" & Format(AbsentDateMinus6, "MM/DD/YYYY") & "#"
  strEndDate = "#" & Format(AbsentDate, "MM/DD/YYYY") & "#"


  strSql = " SELECT tbl_YearCalendar.AbsenceDate FROM tbluAbsenceCodes INNER JOIN tbl_YearCalendar ON tbluAbsenceCodes.AbsenceID = tbl_YearCalendar.AbsenceID "
  strSql = strSql & "WHERE tbl_YearCalendar.EmployeeID = " & empID & " AND tbluAbsenceCodes.AbsenceCode Not In ('V','VFML','VMA','FMLA','F','JD','ML','PC','SFML','PD','PPL','SD','C-19') "
  strSql = strSql & "GROUP BY tbl_YearCalendar.AbsenceDate HAVING tbl_YearCalendar.AbsenceDate Between " & strStartDate & " And " & strEndDate
  strSql = strSql & " ORDER BY tbl_YearCalendar.AbsenceDate "
'verify the SQL works
Debug.Print strSql

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)

'Concatenate the first (and should be the only one) field from the SQL statement
With rs
    Do While Not .EOF
        strCSV = strCSV & strDelim & .Fields(0)
        .MoveNext
    Loop
    .Close
End With

'Remove the leading delimiter and return the result
GetDates = Mid$(strCSV, Len(strDelim) + 1)

Set rs = Nothing
Set db = Nothing

End Function

 

[MajP]

you never set strDelim to anything so your delimeter is an empty string. Probably want something like
strDelim = ","
or ";"
or " "