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.

How do you determine which row is the last one? Which column do you use for that purpose?

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.

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)

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.

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"

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?

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

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.

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

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.

thats what we and dbforums are here for

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.