Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2

    Angry Unanswered: Problem using DMax() in a query

    Hello all,

    I have a query that I use to generate a report that I can't seem to get right.

    The query accesses 2 tables, Customers and xServices. The xServices table will have many entries for each customer.

    I want to create a report from the query that shows only the LAST service for the customer.

    I am trying to use DMax("FollowUpRequired","xServices") - or something similar but either get a syntax error message or the query returns ALL the services for the customer, when I only want the LAST service carried out.

    As you can see I am still a newbie at this but I am learning a lot from this forum.

    My thanks in advance for any assistance.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    How do you determine which row is the last one? Which column do you use for that purpose?
    Have a nice day!

  3. #3
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Hi Sinndho

    In the xServices table records are sorted by date. The field is ServiceDate.

    I guess what I am trying to do is have the most recent service record (ie the last service that a customer had) come up for that customer.

    The idea is that if there is any FollowUpRequired for this customer it will be printed on the report. If this field was left empty at the time of the last service, then nothing would print.

    I hope this is clear.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you would need to find the maximum service date for that customer

    eg
    =dmax("ServiceDate", "xServices", "<customerID> = blah"
    where <CustomerID> is the name of the column identifying the customer
    and blah is the id of that customer
    if the customerid is alpha or alphanumeric encapsualte it with quote marks
    =dmax("ServiceDate", "xServices", "<customerID> = 'blah-di-blah'"

    if you need to identify a specific item of equipment for that customer
    =dmax("ServiceDate", "xServices", "<customerID> = blah AND <equipmentid> = hoot-de-hoot"

    intrinsically the criteria in a domain function such as DMAX is the same as a where clause. and you cna have as many terms part of your where clause as you need to identify the specific row(s)
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Hello Healdem

    Thanks for the reply,

    This is what I ended up trying;
    =DMax("[ServiceDate]","xServices","CustomerID='" & [CustomerID] & "' AND Catagory='Service'")

    but when I run the query I get the error message "The specified filed CustomerID could refer to more than one table listed in the FROM clause of the SQL statement."

    What am I doing wrong?

    Thanks again for your help.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If both tables involved in the query have a column named "CustomerID", you must use a fully qualified name for that column:
    "Customers.CustomerID" or "xServices.CustomerID"
    Have a nice day!

  7. #7
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Hello Sinndho

    Sorry for the delay. Been trying the code and still can't get it. I tried this

    DMax("[Service Date]","xServices.CustomerID","xServices.CustomerID ='" & [xServices.CustomerID] & "' AND Catagory='Service'")

    but now I get a JET engine error

    "... cannot find the input table or query xServices.CustomerID ..."

    which keeps coming up until I give it the old 3 finger salute!

    In this query there are 4 tables with CustomerID (I'm gathering lot of data) but I don't think that should be a problem.

    What am i missing?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    suggest you read the helpfile entry for DMAX or google it

    however Im pretty certain your problems is the second parameter which should be the name of a table or query
    first parameter is the name of the column you want the maximum value
    the second is the table or query name
    the third is the criteria, or where clause you'd normally use in sql minus the word where
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Thanks Healdem

    I'll keep plugging away.

    As I said I'm a bit of a newbie and appreciate your assistance. If I don't make any headway I'll re-post.

  10. #10
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Quote Originally Posted by healdem View Post
    suggest you read the helpfile entry for DMAX or google it

    however Im pretty certain your problems is the second parameter which should be the name of a table or query
    first parameter is the name of the column you want the maximum value
    the second is the table or query name
    the third is the criteria, or where clause you'd normally use in sql minus the word where
    Well I spent hours on this and finally got it right

    Turns out because the Follow Up Required field was a memo field this caused some problems.

    So I ran the DMax on the Service Date, fixed up the incorrect synatx and all is good! Here is the end result for any fellow posters / lurkers that may have similar issues.

    DMax("[Service Date]","xServices","xServices.CustomerID='" & [Customers.CustomerID] & "' AND Catagory='Service'")

    Once again this forum has been great.

    My profilic thanks to healdem and sinndho for your help

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what have you learned from the exercise?

    you have to be careful when referring to things named by you
    initially you refer to servicedate, all one word, yet later your final solution you refer to [service date]
    you have to use the square brackets if you use a space between words in a table or column name. its a nasty little thing, that access does in letting people use spaces in column names. it takes you further away from the SQL standard. its also totally uneccesary as you can set the 'caption property on the column when designing the table for a human readable value.

    so there's a consistency issue
    either separate words in column or table names with an underscore or capitalisation
    eg
    service_date
    or ServiceDate

    you can help yourself with consistency by either adding option explicit to the front of every vba (form, report or code) module or tick the box in the options forcing Access to do this for you. thats only an option in more recent versions of Access, and not the one I have access to here so forget the wording.
    string literals in a where clause must be encapsualted by a ' or "
    numeric literals don't
    date literals should be encapsulated by # and be in ISO (yyyy mm dd) or US (mm dd yyyy)

    usually there's a reason why references are quoted in answers. often you will get a better solution if you treat responses here as suggestions but go and check the suggestions in the helpfile or google it. people make suggestions but often thise suggestions are from fallable memory, so treat 'em as suggested ways of solving the problem but check the detail elsewhere if you are struggling with it.

    Once again this forum has been great.

    My profilic thanks to healdem and sinndho for your help
    thats what we and dbforums are here for
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Hi Healdem

    You are correct in everything you say. Unfortunately when I inherited this project, about 12 years ago, all the tables, at least the core ones, were already set up.

    My use of ServiceDate in my original questions was simply to avoid confusion in my post. I was making necessary corrections based on the suggestions I was given.

    If I had my way I'd start the whole thing again, but it is much to daunting a task to undertake, without devoting serious time to it.

Posting Permissions

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