DMAX function in query

  • Thread starter Thread starter Jim Franklin
  • Start date Start date
J

Jim Franklin

Hi,

I have two tables, tbl_Orders and tbl_Letters. Both have a common Cust_ID
field and each has a date field, Order_Date and Letter_Date respectively.
Each customer may have several letter s and several orders.

I am trying to write a query that, for every letter in my tbl_Letters, tells
me the date of the last order placed by the customer BEFORE receiving that
letter (i.e. ignoring any orders placed afterwards)

It sounds simple, but I have been using a select query on tbl_Letters with a
DMAX function, but this does not work. The expression I am using is

DMax("[Order Date]","Orders","[Order Date] < #" & [Letter_Date] & "# AND
[Customer ID] = " & [Cust_ID])

My date fields are formatted as dd/mm/yy - does this matter? And is this the
correct way to do it?

Thanks as always for any help,
Jim F.
 
I have two tables, tbl_Orders and tbl_Letters. Both have a common Cust_ID
field and each has a date field, Order_Date and Letter_Date respectively.
Each customer may have several letter s and several orders.

I am trying to write a query that, for every letter in my tbl_Letters, tells
me the date of the last order placed by the customer BEFORE receiving that
letter (i.e. ignoring any orders placed afterwards)

It sounds simple, but I have been using a select query on tbl_Letters with a
DMAX function, but this does not work. The expression I am using is

DMax("[Order Date]","Orders","[Order Date] < #" & [Letter_Date] & "# AND
[Customer ID] = " & [Cust_ID])

My date fields are formatted as dd/mm/yy - does this matter? And is this the
correct way to do it?
Click to expand...
Hi Jim,

If you use "#" delimiter in any SQL clause,
including domain functions, the dates must
be in US format.

Allen has a wrapper function (SQLDate) here, so you
never have to think about it again:

http://members.iinet.net.au/~allenbrowne/ser-36.html

or just format it in the DMax function

DMax("[Order Date]","Orders","[Order Date] < "
& Format([LetterDate], "\#mm\/dd\/yyyy\#") &
"AND [Customer ID] = " & [Cust_ID])

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
Back
Top