By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,073 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

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

P: 18
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..sorry.

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
  1. Function PBTotal(ByVal dtRank As Date, ByVal boolRanked As Boolean, Optional intTop As Integer = 0) As String
  2. Dim qdf As QueryDef
  3. Dim rs As Recordset
  4.  
  5. Dim strRank As String
  6. Dim strQry As String
  7.  
  8. strRank = "The highest personal best scores up to " & Format(dtRank, "mmmm") & " were: "
  9.  
  10.  
  11. Set qdf = CurrentDb.QueryDefs("PB5")
  12. strQry = "Select (max[MaxOfShootDate]) FROM [PB5]"
  13. strQry = dtRank
  14.  
  15. Set rs = qdf.OpenRecordset
  16. Do While Not rs.EOF
  17.  
  18.     strRank = strRank & rs.Fields("Member") & " " & rs.Fields("MaxOfMaxOfShoot1") & "; "
  19.     If rs.AbsolutePosition = intTop - 1 Then Exit Do
  20.     rs.MoveNext
  21. Loop
  22.  
  23. Set rs = Nothing
  24. Set qdf = Nothing
  25.  
  26. PBTotal = strRank
  27. End Function
  28.  
Appreciate any help.. baby step please
May 30 '12 #1
Share this Question
Share on Google+
4 Replies


P: 16
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("MaxOfShootDate", "PB5")
May 30 '12 #2

P: 18
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?
May 31 '12 #3

P: 18
I have created what I want to see as a result in a Query, ie: May
Expand|Select|Wrap|Line Numbers
  1. SELECT Max(Format([MaxOfShootDate],"mmmm")) AS [Month]
  2. FROM PB3
  3. WITH OWNERACCESS OPTION;
  4.  
  5.  
Is there any what to adapt this to run in my Function in place of
Expand|Select|Wrap|Line Numbers
  1. dtRank = DMax("MaxOfShootDate", "PB5") 
  2.  
I tried this which didn't work
Expand|Select|Wrap|Line Numbers
  1. Set dtRank = (Format("MaxOfShootDate", "mmmm", "Month", "PB5"))
  2.  
Anyone any ideas please
Jun 1 '12 #4

P: 18
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:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Public strgOutPut As String
  4. Public QueryMonth As Variant
  5. Public Function GetGlobal(VariableType As String) As Variant
  6. Select Case VariableType
  7. Case GlbValue
  8. Case "PB7"
  9. GetGlobal = Month
  10.  
  11. End Function
  12. Sub RankTest()
  13. Debug.Print PBTotal(#5/20/2008#, True)
  14. Debug.Print PBTotal(#5/20/2008#, False)
  15. End Sub
  16.  
  17. Function PBTotal(ByVal dtRank As Date, ByVal boolRanked As Boolean, Optional intTop As Integer = 0) As String
  18. Dim qdf As QueryDef
  19. Dim rs As Recordset
  20.  
  21. Dim strRank As String
  22. Dim strQry As String
  23. Dim max As VbDateTimeFormat
  24.  
  25. strRank = "The highest personal best scores up to " = strgOutPut & " were: "
  26.  
  27. Set qdf = CurrentDb.QueryDefs("PB5")
  28.  
  29.  
  30. Set rs = qdf.OpenRecordset
  31. Do While Not rs.EOF
  32.  
  33.     strRank = strRank & rs.Fields("Member") & " " & rs.Fields("MaxOfMaxOfShoot1") & "; "
  34.     If rs.AbsolutePosition = intTop - 1 Then Exit Do
  35.     rs.MoveNext
  36. Loop
  37.  
  38. Set rs = Nothing
  39. Set qdf = Nothing
  40.  
  41. PBTotal = strRank
  42. End Function
  43.  
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
Jun 7 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.