Extract max date and use the value in a line of code

[Paulo357]

Iam a complete novice to writing code so I started with a snippet of code that produced what I wanted and have been add to it and modifying bits to get where I want to be.. so my code maybe a little disorganized..s orry.

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.

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

Appreciate any help.. baby step please

[Marc Brown]

Try using the function DMax. You can look it up in help for a better description of the syntax. It should look like this.

dtRank = DMax("MaxOfShoo tDate", "PB5")

[Paulo357]

Hi Marc
Thanks for the reply
Your code returned the same answer as mine = April.. I need the answer to equal May.
I checked the Query PB5 and it contains April and May dates.. any ideas?

[Paulo357]

I have created what I want to see as a result in a Query, ie: May

SELECT Max(Format([MaxOfShootDate],"mmmm")) AS [Month]
FROM PB3
WITH OWNERACCESS OPTION;

Is there any what to adapt this to run in my Function in place of

dtRank = DMax("MaxOfShootDate", "PB5")

I tried this which didn't work

Set dtRank = (Format("MaxOfShootDate", "mmmm", "Month", "PB5"))

Anyone any ideas please

[Paulo357]

OK
I have now gone down the path of trying to create a Public Function to get the value from Query PB7 and plug that answer [which is "May"] into my line of text.
Here is my code so far:

Option Compare Database
Option Explicit
Public strgOutPut As String
Public QueryMonth As Variant
Public Function GetGlobal(VariableType As String) As Variant
Select Case VariableType
Case GlbValue
Case "PB7"
GetGlobal = Month

End Function
Sub RankTest()
Debug.Print PBTotal(#5/20/2008#, True)
Debug.Print PBTotal(#5/20/2008#, False)
End Sub

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
Dim max As VbDateTimeFormat

strRank = "The highest personal best scores up to " = strgOutPut & " were: "

Set qdf = CurrentDb.QueryDefs("PB5")


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

The way I written it I get a date value of 05/06/12, where I am expecting "May"
Can anyone help me now?

Paul