Format Date

[jgonick]

I'm using the module below to go through and get all birthdays related to a family and list them on a single line. example of output: 1/10, 2/14, 9/30

My problem is getting the date in the correct format on the report. I only need the month and day. "m/d", but it list the year also. I've tried the format in the text box properties, It works correctly if there is only one date. Multiple dates revert back to mm/dd/yyyy format

Any ideas?



Here is the function:
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
'Dim rs As New ADODB.Recordset
'rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

 

[PHV]

Have you tried to put the format in the SELECT clause:
SELECT FamID, Concatenate("SELECT Format(DOB,'m/d') FROM tblFamMem WHERE FamID=" & [FamID]) As Birthdays


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jgonick]

Thanks, that worked great.
I was hoping it would be something simple.


Once again thanks for the help.