Page 1 of 3 123 LastLast
Results 1 to 15 of 40
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mode Function (2000)

    Now this may seem a little on the simple side, in comparison to some of the more esoteric enquiries posted here. However, I'm attempting to put together a somewhat basic DB which calculates elapsed times between processes, & then provide a few summary fugures on a weekly / monthly basis. I had been doing this with Excel, but decided to make it a little more streamlined using Access, only I can't seem to find out how to get a report to show the 'mode' for the figures, like that used in Excel, as I can with the average, max & min functions. I have tried the good old fashioned help files, but the help file only gives;

    MODE
    Returns the most frequently occurring, or repetitive, value in a range of data.

    If this function returns the #NAME? error value, you may need to install msowcf.dll.

    Syntax

    MODE(number1,number2,...)

    Number1 Number1, Number2, ... are 1 to 30 numbers or references that contain numbers for which you want the mode.

    which I haven't been able to get to work. Any & all help on this would stop me going bald I hope [img]/forums/images/smilies/smile.gif[/img]

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Mode Function (2000)

    Mode is a worksheet function. It isn't part of Access.
    Charlotte

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mode Function (2000)

    As Charlotte remarked, Mode is not an Access function.

    Microsoft provides an example of a user defined Mode function in KB article q210569.

  4. #4
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mode Function (2000)

    Thanks for the replies, particularly the link to the KB.
    I still have a prob with it though, I created the module as the the KB article stated (just used copy & paste [img]/forums/images/smilies/smile.gif[/img] ) but seeing as I'm a complete duffer wher VB is concerned, I don't know wher to start because it's not working.
    As per the article the MS DAO 3.6 Object library has been ticked in references & I've named the module Mode (surprise, surprise), yet wherever I've tried it, the only result is "#Name?", which seems to indicate an unknown function.
    This is the code I copied in;
    Function Mode(tName As String, fldName As String)
    ' The function will initialize:
    ' - A variable for the database object.
    ' - A variable for the snapshot.
    ' - Sets the database object to the opened database.
    ' - Creates a snapshot based on the database object.
    ' - This function requires table and field name parameters where the
    ' arguments are passed using "[" when the name includes spaces.

    Dim ModeDB As DAO.Database
    Dim ssMode As DAO.Recordset
    Dim ModalField1, ModalField2, ModalResult1, ModalResult2

    If tName = "" Or fldName = "" Then Exit Function

    Set ModeDB = CurrentDb()
    Set ssMode = ModeDB.OpenRecordset("SELECT DISTINCTROW Count (" & _
    fldName & ") AS Mode, " & fldName & " FROM " & _
    tName & " GROUP BY " & fldName & " ORDER BY Count(" & _
    fldName & ") DESC;", DB_OPEN_SNAPSHOT)
    ModalField1 = ssMode(fldName)
    ModalResult1 = ssMode!Mode
    ssMode.MoveNext
    ModalField2 = ssMode(fldName)
    ModalResult2 = ssMode!Mode

    If ModalResult1 <> ModalResult2 Then
    Mode = "The Result is Modal: " & ModalField1
    Else
    Mode = "The Result is Bimodal: " & ModalField1 & _
    " and " & ModalField2
    End If
    ssMode.Close
    ModeDB.Close

    End Function

    I hope you can figure this out for me. I know it's only a minor function, but it's one of those which bug the hell out ya.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mode Function (2000)

    If you name the module and the function Mode, Access get confused. Rename your module to anything else.
    Francois

  6. #6
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mode Function (2000)

    Aha! got further this time, thanks Francois.
    But got an error message;

    Compile Error
    User defined type not defined

    & the de###### highlights the first line in yellow;

    Function Mode(tName As String, fldName As String)

    & further down, in an edit highlight;

    ModeDB As DAO.Database

    Thanks for all the help you guys are giving me & certainly for the speed in your replies.

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mode Function (2000)

    In the design view of the module, select the menu Tools | References.
    In the list, look for Microsoft DAO 3.6 Object Library and check it.
    Francois

  8. #8
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mode Function (2000)

    OK, I had that ticked before, but with the probs had resorted to an earlier copy, without the tick. Now there's a runtime error;

    Runtime error '306'.
    Too few parameters, Expected 2.

    & highlighted in yellow;

    Set ssMode = ModeDB.OpenRecordset("SELECT DISTINCTROW Count (" & _
    fldName & ") AS Mode, " & fldName & " FROM " & _
    tName & " GROUP BY " & fldName & " ORDER BY Count(" & _
    fldName & ") DESC;", DB_OPEN_SNAPSHOT)

    This sounds like the code wants to look at more thatn one field, however I'm looking at doing a Mode for each of several fields. Here is the formula I'm putting into a report for one of them;

    =mode("PressWeek","InCaustic")

    the square brackets normally used for tables & fields aren't recognised in this function (as yet!)

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mode Function (2000)

    You are missing some quotes at the beginning of the lines :
    Set ssMode = ModeDB.OpenRecordset("SELECT DISTINCTROW Count (" & _
    "fldName & ") AS Mode, " & fldName & " FROM " & _
    "tName & " GROUP BY " & fldName & " ORDER BY Count(" & _
    "fldName & ") DESC;", DB_OPEN_SNAPSHOT)

    =mode("PressWeek","InCaustic")
    is right if PressWeek is your table and InCaustic the field.
    Francois

  10. #10
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mode Function (2000)

    Oops, that whole paragraph ends up changing to red text, when I put in the quotes.

    Oh & the "PressWeek" is the table & "InCaustic" the field.

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mode Function (2000)

    Sorry but I was wrong about the quotes.
    I attach a little mdb where the function is working.
    Attached Files Attached Files
    Francois

  12. #12
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mode Function (2000)

    OK, well I used your code (though it seemed to be the same as the one on the MSKB article, without the indents) & for some reason it still comes up with a runtime error;

    '3061':
    Too few parameters. Expected 1.

    What I've been asked to produce only requires the one 'Mode' result. Maybe you could look at this extraction from the DB I'm attempting to create, & hopefully you'll spot my error(s) [img]/forums/images/smilies/smile.gif[/img]
    Attached Files Attached Files

  13. #13
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mode Function (2000)

    You have an error in the controlsource of the textbox, as field you have:
    Caustic2Correcton
    should be
    Caustic2Correction
    with an i in correction
    Francois

  14. #14
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mode Function (2000)

    Thanks to all of you for your help, I really do appreciate it.
    I'm not sure what's going wrong, in the main DB that I'm working with, I keep getting this error again;

    Runtime error '3061'.
    Too few parameters, Expected 2.

    The function appears to be looking for 2 sets of 'mode' figures (though I only require 1),but I did wonder if this works with reports from queries. If not, can it work inside a query, so I can enlarge the append query & relevant table, then do the report that way?

  15. #15
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mode Function (2000)

    Modify the first part of the code as follow :
    <pre>Dim ModeDB As DAO.Database
    Dim ssMode As DAO.Recordset
    Dim ModalField1, ModalField2, ModalResult1, ModalResult2
    Dim strSQL As String
    If tName = "" Or fldName = "" Then Exit Function

    Set ModeDB = CurrentDb()
    strSQL = "SELECT DISTINCTROW Count (" & _
    fldName & ") AS Mode, " & fldName & " FROM " & _
    tName & " GROUP BY " & fldName & " ORDER BY Count(" & _
    fldName & ") DESC;"

    Set ssMode = ModeDB.OpenRecordset(strSQL, DB_OPEN_SNAPSHOT)
    ....</pre>

    When you get the error, type in the Immediate window :
    ? strSQL
    Copy the line you get and post it, maybe we can see what's wrong with your sql string.
    Francois

Page 1 of 3 123 LastLast

Posting Permissions

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