Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2004
    Posts
    64

    Question Unanswered: Need to get oldest and newest value

    Greetings everyone,
    I have a form that is in a customer support database. The form (Customer information) has a subform (case Information), which is then linked to another form (case updates).

    On the case information subform (call it subCaseInfo), I assign a case number and take down various info about the customer's setup. What I would like to do is add a couple of unbound text fields that would display the initial entry and last entry in the linked frmCaseUpdates form. The idea is that the tech would be able to see the problem and resolution on the main form without having to open up the updates form. DMIN and DMAX won't pull the info in for a memo field, and I have tried building an SQL string which works, but of course I can't bind a text box to an SQL statement. Any ideas?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    How did you use DMIN and DMAX?

    You should be using them against either a date field or, if all else fails, the primary key.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jul 2004
    Posts
    64
    Yeh, the problem I don't understand is how I pull the memo field into the DMAX function.. Here is what I have:

    DMAX("case_date","case_entry", "case_number = 'JG1401S'")

    Eventually I will replace the case number value with a variable, but what I don't get is how I use this to get the memo field?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I see... try this:

    DLookup("memofield", "case_entry", "case_number = 'JG14015' AND case_date = DMAX("case_date","case_entry", "case_number = 'JG1401S'"))

    You could toss that into a function that takes a case number as a parameter as well..

    Code:
    Public Sub GetMaxDate(case As String) As Date
    GetMaxDate = DLookup("memofield", "case_entry", "case_number = '" & case & _ 
    "' AND case_date = DMAX("case_date","case_entry", "case_number = '" & case & "'"))
    Usage for this instance would be:

    GetMaxDate('JG14015')
    Last edited by Teddy; 07-21-04 at 10:45.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jul 2004
    Posts
    64
    Thanks for the reply.. We are pretty close.. Using the dlookup I get a type mismatch, yet I can use each of them by themselves successfully. Using the code, I get an "Expected:Identifier" referencing "(case As String)". I may be trying to use the code wrong, as I am not that up on using public code (working on it though).

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Oh, that's because I"m an idiot.

    Change the "As Date" to "As String" at the end of the first line. So it should read:
    Public Sub GetMaxDate(case As String) As String
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Jul 2004
    Posts
    64
    heh, I know I have done my share of stupid things while trying to code.
    hmm.. Still getting the error message.. This code should go in a standard module correct?

  8. #8
    Join Date
    Jul 2004
    Posts
    64
    ok, how about this one (backing up a bit).. Just see what is wrong with it:


    DLookup("Case_number", "case_entry_table", "case_date = 7/15/2004 5:48:47 PM")

    I have also tried:

    DLookup("Case_number", "case_entry_table", "case_date = #7/15/2004 5:48:47 PM#")

    and

    DLookup("Case_number", "case_entry_table", "case_date = #7/15/2004#")

    all of these give me an OLE object error

Posting Permissions

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