Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dmax in a Query (2000)

    Hi,

    I have a query made up of three tables.

    One of the these tables links with the unique idenitier of the the main table to create 'case notes'. An option in these case notes is a followup date date box. In the query I want to list all the cases and that last note made toogether with the followup date. However it may be be the last note made, as the followup date field is not madatory. I am trying to use the following code in the criteria for the field:

    DMax("[propfollowup]","tblpropertynotes","salesid=" & [vmgtSalesID] & " and [PropFollowup]=" Is Not Null)

    However is only lists the last record where the followup field is not blank. I need to create a list for all the cases with their last followup date box that is not blank.

    Hopefully this is clear, if not let me know.
    Best Regards,

    Luke

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

    Re: Dmax in a Query (2000)

    Your DMax expression is syntactically incorrect, and I don't understand what you're trying to accomplish. Could you try to explain "However is only lists the last record where the followup field is not blank. I need to create a list for all the cases with their last followup date box that is not blank"? I don't see the difference.

  3. #3
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dmax in a Query (2000)

    Hans,

    sorry, thought it might be confusing.

    We have say 50 active clients with information on them. We have a further table which is linked to the clients table to create notes on them. We could have 100 + notes for each client. In the notes we record the date the notes was entered, who entered the note and if a follow-up date is required to chase anything up. I want to be able to print a list so that it displays the clients name the content of the last note (when there was a date in the followup field). This way we can print this off on a daily basis when the followup date is today or past in order to chase, etc.
    Best Regards,

    Luke

  4. #4
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dmax in a Query (2000)

    Please see attached database, in the queries the relevant one I am trying to work with.

    You will see from the tblPropertyNotes that there are two properties that have followup dates that are different. however in the query it only lists one of them. I need it to list all of them.
    Attached Files Attached Files
    Best Regards,

    Luke

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

    Re: Dmax in a Query (2000)

    What exactly are salesid and vmgtSalesID?

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

    Re: Dmax in a Query (2000)

    It now turns out that you have four tables in the query instead of three, and it seems that you want to have the latest note for a property instead of for a vendor. Is that correct?

  7. #7
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dmax in a Query (2000)

    Yes that is right. Sorry getting confused [img]/forums/images/smilies/sad.gif[/img]
    Best Regards,

    Luke

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

    Re: Dmax in a Query (2000)

    Does the attached version do what you want?
    Note that the query is no longer a totals query, I didn't see the point of that.
    Attached Files Attached Files

  9. #9
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dmax in a Query (2000)

    Yes, seems to work fine.

    Once again, thanks.
    Best Regards,

    Luke

Posting Permissions

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