I have a MS Access Function which ranks a Queries results and is working fine. I have been able to add text to the beginning of the output script. I am now trying to insert a date into the text. Specifically I want the highest date from the records selected by the query. Ive been using (max[MaxOfShootDate]) which maybe completely wrong, MaxOfShootDate is the field name containing the date info.
I added Dim strQry As String to hold the value
dtRank is the placeholder of the value I want.
The part of the date I require is only the MONTH that why the Format(dtRank, "mmmm") is there.
Expand|Select|Wrap|Line Numbers
- Function PBTotal(ByVal dtRank As Date, ByVal boolRanked As Boolean, Optional intTop As Integer = 0) As String
- Dim qdf As QueryDef
- Dim rs As Recordset
- Dim strRank As String
- Dim strQry As String
- strRank = "The highest personal best scores up to " & Format(dtRank, "mmmm") & " were: "
- Set qdf = CurrentDb.QueryDefs("PB5")
- strQry = "Select (max[MaxOfShootDate]) FROM [PB5]"
- strQry = dtRank
- Set rs = qdf.OpenRecordset
- Do While Not rs.EOF
- strRank = strRank & rs.Fields("Member") & " " & rs.Fields("MaxOfMaxOfShoot1") & "; "
- If rs.AbsolutePosition = intTop - 1 Then Exit Do
- rs.MoveNext
- Loop
- Set rs = Nothing
- Set qdf = Nothing
- PBTotal = strRank
- End Function