Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2010
    Posts
    15

    Unanswered: Using DMax() and DMin() with a Recordset

    I have created a recordset that is a subset of records from another table
    based on age and gender. Three of the columns in the recordset (among others)
    are a Score, HighLimit and LowLimit. I need to determin if sngCurlUpCount:
    - is greater than the largest value in the HighLimit
    - or lower thant the lowest value in the HighLimit .

    'This returns my recordset of 4 rows
    Dim strSQL As String
    'Query the look up table rows for the passed age and gender.
    strSQL = "SELECT * " & _
    "FROM MECurlUpTable " & _
    "WHERE AgeLowLimit < " & [sngAge] & _
    " And AgeHighLimit > " & [sngAge] & _
    " And Male = " & [bytMale] & ";"

    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

    When i execute this code i see that DMax(rs("HighLimit") and DMin(rs
    ("HighLimit") return the same value (40). But I know the high value in the
    HighLimit column is 40 and the low value is 13.
    Debug.Print sngCurlUpCount
    Debug.Print DMax(rs("HighLimit"), "MECurlUpTable")
    Debug.Print DMin(rs("HighLimit"), "MECurlUpTable")

    This is the code i wanted to use to return the correct score.
    Select Case sngCurlUpCount
    Case Is >= DMax(rs("HighLimit"), "MECurlUpTable")
    varITAScoreCUC = 4
    Case Is <= DMin(rs("HighLimit"), "MECurlUpTable")
    varITAScoreCUC = 1
    End Select

    Should I be using something other than the domain functions or have I messed up some where else?

    Thanks for the help
    Last edited by new2access123; 02-14-10 at 20:43.

  2. #2
    Join Date
    Feb 2010
    Posts
    15
    I think I found the solution. Domain functions can not be used with record sets. So
    DMin(rs("HighLimit"), "MECurlUpTable") will be evaluated correctly. The problem is it does not through an error. I'm new and learning.

  3. #3
    Join Date
    May 2009
    Posts
    258
    What you are attempting is to find the maximum value in a column named after the value of HighLimit in MECurlUpTable. For example, if rs("HighLimit") is 40, with the DMax function as you have written, it'll be looking for a column named "40". You should use the following instead:
    Code:
    DMax("HighLimit", "MECurlUpTable")
    DMin("HighLimit", "MECurlUpTable")
    Regards,

    Ax

  4. #4
    Join Date
    Feb 2010
    Posts
    15
    Quote Originally Posted by Ax238 View Post
    What you are attempting is to find the maximum value in a column named after the value of HighLimit in MECurlUpTable. For example, if rs("HighLimit") is 40, with the DMax function as you have written, it'll be looking for a column named "40". You should use the following instead:
    Code:
    DMax("HighLimit", "MECurlUpTable")
    DMin("HighLimit", "MECurlUpTable")
    Regards,

    Ax
    The statements in your code snippets reference the source table. I was hoping that the domain functions would work on a record set. Every thing that i have found indicates domain functions will not work on a recordset. What I will have to do is order the record set in the SQL that populates it then rs.MoveFirst and rs.MoiveLast testing the values.

  5. #5
    Join Date
    May 2009
    Posts
    258
    Am I missing something? Your statements also reference the source table. All I did was reference the field name, rather than its value.

    I may be wrong, but are you looking for the highest "HighLimit" from your query? If that's the case, you can just add the same criteria to the domain functions:
    Code:
    DMax("HighLimit", "MECurlUpTable", "AgeLowLimit<" & [sngAge] & " And AgeHighLimit>" & [sngAge] & " And Male=" & [bytMale])
    DMin("HighLimit", "MECurlUpTable", "AgeLowLimit<" & [sngAge] & " And AgeHighLimit>" & [sngAge] & " And Male=" & [bytMale])
    Ax

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The SQL language also have functions (MAX(), MIN(), etc.) that can be used to open a recordset, eventually combined with WHERE, GROUP BY, HAVING, etc.
    Code:
    strSQL = SELECT MAX(HighLimit) AS MaxHighLimit, MIN(HighLimit) AS MinHighLimit FROM ...
    rst = CurrentDb.OpenRecordSet(strSQL, dbOpenSnapshot)
    Have a nice day!

  7. #7
    Join Date
    Feb 2010
    Posts
    15
    [QUOTE=Ax238;6448417]Am I missing something? Your statements also reference the source table. All I did was reference the field name, rather than its value.

    In my statement I was attempting to execute the domain function on a recordset by using rs("HighLimit"):

    DMin(rs("HighLimit"), "MECurlUpTable")

    in your examplr you were referencing the source table:

    DMax("HighLimit", "MECurlUpTable",

    As i understand it domain functions can not be used on recordsets. so to acheive the same goal i will need to order the SQL that populates the rs by HighLimit then rs.MoveFirst, s.MoveLast to get the high and low values.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Access domain function do not work with recordsets and cannot be used as parts of a recordset (see Access documentation or http://office.microsoft.com/en-us/ac...CH100728911033).
    Either you use a domain function such as:
    Code:
    Maximum = DMax("<Field>", "<Table or Query>", "<Optional criteria>")
    or you use SQL aggregate functions to create the recordset (see http://msdn.microsoft.com/en-us/libr...SQL.80).aspx):
    Code:
    SQL = "SELECT MAX(<Field>) AS MaxField FROM <Table or Query> WHERE <Optional criteria>"
    Set rst = db.OpenRecordset(SQL)
    Maximun = rst!MaxField
    The advantage of the second method is that you can have multiple aggregate functions in the SQL statement:
    Code:
    SELECT MAX(...), MIN(...), etc.
    In both cases you have to reference the source table or query anyway.
    Have a nice day!

  9. #9
    Join Date
    May 2009
    Posts
    258
    Quote Originally Posted by new2access123 View Post
    In my statement I was attempting to execute the domain function on a recordset by using rs("HighLimit"):

    DMin(rs("HighLimit"), "MECurlUpTable")

    in your examplr you were referencing the source table:

    DMax("HighLimit", "MECurlUpTable",
    Actually, we were both referencing the source table (MECurlUpTable), the only difference is that you were attempting to use the value of HighLimit as a field name in the source table (e.g. if rs("HighLimit") had a value of 40, your logic would be looking for a field named "40" in the source table), while I was using "HighLimit" as a field name in the source table.

    Quote Originally Posted by new2access123 View Post
    As i understand it domain functions can not be used on recordsets. so to acheive the same goal i will need to order the SQL that populates the rs by HighLimit then rs.MoveFirst, s.MoveLast to get the high and low values.
    Look at the examples in my last post, using domain functions is still an option, you just need to add the same criteria that you used to get the recordset.

    Ax

  10. #10
    Join Date
    Feb 2010
    Posts
    15
    Sinndho
    Thanks for the inputI understand your suggestion and it is helpfull. I was wondering if there is a home page to http://office.microsoft.com/en-us/ac...CH100728911033)? Where I can find an index to the various commands and vunctions.

  11. #11
    Join Date
    Feb 2010
    Posts
    15
    Quote Originally Posted by Ax238 View Post
    Actually, we were both referencing the source table (MECurlUpTable), the only difference is that you were attempting to use the value of HighLimit as a field name in the source table (e.g. if rs("HighLimit") had a value of 40, your logic would be looking for a field named "40" in the source table), while I was using "HighLimit" as a field name in the source table.

    Look at the examples in my last post, using domain functions is still an option, you just need to add the same criteria that you used to get the recordset.

    Ax
    Thanks for the feed back. After your explanation I equate that I did to something like an old FoxPro Macro substitution.

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The first source of documentation remains the Access help system. It's worth to invest a little time in learning how to use it efficiently. Other than that, there are various sites that provide precious information, both Microsoft and non-Microsoft ones (if I may say so).
    Among them:
    Microsoft Office Development (everything Office)
    The Access Web - Welcome
    Helen Feddema's Home Page
    Martin Green's Access Tips
    Tony's Main Microsoft Access Page
    Welcome To Roger's Access Library
    Microsoft Access help, tutorials, examples
    FMS Technical Papers - DAO Advanced Programming
    ADO Tutorial
    Database - CodeProject
    FAQ MS-Access - Club des décideurs et professionnels en Informatique (in French)
    And many others that are as valuable as these ones (sorry for those I forget).
    Have a nice day!

  13. #13
    Join Date
    Feb 2010
    Posts
    15
    Quote Originally Posted by Sinndho View Post
    The first source of documentation remains the Access help system. It's worth to invest a little time in learning how to use it efficiently. Other than that, there are various sites that provide precious information, both Microsoft and non-Microsoft ones (if I may say so).
    Among them:
    Microsoft Office Development (everything Office)
    The Access Web - Welcome
    Helen Feddema's Home Page
    Martin Green's Access Tips
    Tony's Main Microsoft Access Page
    Welcome To Roger's Access Library
    Microsoft Access help, tutorials, examples
    FMS Technical Papers - DAO Advanced Programming
    ADO Tutorial
    Database - CodeProject
    FAQ MS-Access - Club des décideurs et professionnels en Informatique (in French)
    And many others that are as valuable as these ones (sorry for those I forget).
    Great resources! I will be using them. Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •