list items separate and not in string (1 Viewer)

[tubar]

I have a module that works with a query. Im still learning how to build a module. I have everything working; except, id like my "f1" results not to string. can some one point me in a direction?

Public Function fnConcat(dteValue As Variant) As Variant

    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strResult As String
    
    dteValue = Nz(dteValue, 0)
    strSQL = "SELECT f1 FROM qryARROW WHERE #" & Format(dteValue, "mm/dd/yyyy") & _
            "# Between content_S_DATE And END_DATE"
            
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    
    With rs
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        While Not .EOF
            strResult = strResult & !f1 & ", "
            .MoveNext
        Wend
        .Close
    End With
    Set rs = Nothing
    If Len(strResult) > 0 Then
        strResult = Left(strResult, Len(strResult) - 2)
        fnConcat = strResult
    End If
End Function

 

[pbaldy]

I'm confused; the whole point of the function is to create a string of f1 values. If you don't want that, don't use the function.

 

[GohDiamond]

Do you mean to have each comma separated item on a new line?

strResult = strResult & !f1 & ", "[COLOR="Red"] & vbLf[/COLOR]

and then

strResult = Left(strResult, Len(strResult) [COLOR="red"]- 3[/COLOR])

Otherwise, what pbaldy said

Cheers,
Goh

 

[tubar]

Thanks GohDiamond!

That is exactly what i want. I made your changes but i still have the f1 values on one line separated by a comma. Any other suggestions?

 

[static]

While Not .EOF
    if len(strResult) then strResult = strResult & ", "  & vbnewline
    strResult = strResult & !f1
    .MoveNext
Wend

and remove the last If

 

[arnelgp]

It will alway be a string since you declare strResult As String. Concarenating to a string results to a string.

 

[tubar]

Thank you arnelgp!

Makes sense. i need to learn more about strings! Thanks for the help before as well!!

 

[jdraw]

Confused as Paul said in post #2.

How about showing us a sample of what you want for output?

 

[tubar]

I have a table that lists events with a start and end date. Im want to duplicate these records in a report.
Example:
EVENT 1 3/15/18 – 3/20/18
EVENT 2 3/15/18 - 3/15/18

outputs
3/15 event 1
3/15 event 2
3/16 event 1
3/17 event 1
and so on.

arnelgp helped me a great deal by explaining Concarenating... there are steps you need to do in order
to get the resulting query that you need.

first is creating a Query based on Cartesian
of Tables.

we want to create Query against tblEvents (the main table).

firstly, create 3 tables (see tblDays, tblMonths, tblYear).
create a Cartesian query agains these tables (qryDays).

second, on VBA create a function that will combine Events
on a particular date (see Module1, fnConcat function).

finally create the final query that will output the days
and the Events associated with that date (FinalQuery).

examine the Criteria of the FinalQuery, and how it calls
fnConcat to produce the results you need.

but what ended up happening was
3/15 event 1, event 2
3/16 event 1
3/17 event 1