Use of DMax in Excel not working./

Posted on 2012-09-01
Last Modified: 2012-09-01
Hi Team,

the given below code is not working form me.  Can you please help.

Private Sub RecTimeAddition()
    'Database Declariation
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim dblDocID As Double

    'Database Connection
    Set db = OpenDatabase("DatebasePath.mdb")
    Set rs = db.OpenRecordset("tblRecevied", dbOpenTable)
    'Get the Doc ID
    dblDocID = DMax("rs.[tblRecevied].[DocID]", "rs.[tblRecevied]")

End Sub

Open in new window

Question by:Sandesh555
    LVL 48

    Expert Comment

    Try this way:

    dblDocID = DMax(rs.[tblRecevied].[DocID], rs.[tblRecevied])

    Note: That way you are not getting the results from the recordset for the DMAX function but using that as strings
    LVL 29

    Accepted Solution

    Set db = OpenDatabase("DatebasePath.mdb")
    dblDocID  = db.OpenRecordset ("Select Max(DocID) From tblReceived").Field(0)


    Set db = OpenDatabase("DatebasePath.mdb")
    Set rs = db.OpenRecordset ("Select Max(DocID) As MaxID From tblReceived")
    dblDocID  = rs!MaxID
    LVL 44

    Expert Comment

    by:Martin Liss
    As I understand it (I've never used it) DMAX works on a "database" consisting of a range of cells in Excel, rather than an Access database. Here's an example.
    LVL 29

    Expert Comment

    Looking at the code I gave you and the one you provided, I see you have tblReceived spelled as tblRecevied, so you need to adjust accordingly.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now