Max Date Function and unbound text field

[Sam]

Hi,

I have an unbound text box on a form that I want to look
up and display the most recent order date.

I've created a query with the fields:

OrderID OrderDate Customer ID

so I only want to see the orders for this particular
customer - I'm using as criteria Forms!Contacts!CustomerID

My query pulls the correct orders (and sorts them desc by
OrderDate) but I want only the most recent order. Can you
use Max in a query?

Once I get the correct date, I can use a macro to set the
value of the text box. Suggestions? Am I going about
this the right way?

Thank you,
Sam

 

[Wayne Morgan]

Yes, you can use Max in a query. In the query design view, click the Totals
button on the tool bar and look at the options in the extra row it give you.
However, for what you are trying to do, you may find the DMax function the
easiest thing to use to fill the textbox.

 

[Dirk Goldgar]

Hi,

I have an unbound text box on a form that I want to look
up and display the most recent order date.

I've created a query with the fields:

OrderID OrderDate Customer ID

so I only want to see the orders for this particular
customer - I'm using as criteria Forms!Contacts!CustomerID

My query pulls the correct orders (and sorts them desc by
OrderDate) but I want only the most recent order. Can you
use Max in a query?

Once I get the correct date, I can use a macro to set the
value of the text box. Suggestions? Am I going about
this the right way?

Thank you,
Sam

You can indeed use the Max function in a query, as in something like
this:

SELECT Max(OrderDate) As LastOrder
FROM Orders
WHERE CustomerID = [Forms]![Contacts]![CustomerID];

It sounds to me, though, like you could dispense with the stored query
altogether and set the ControlSource of your text box to a DMax
expression like this:

=DMax("OrderDate", "Orders", "CustomerID=" & [CustomerID])

Not only would you not need a stored query, but you would also not need
a macro to set the value of the text box.

 

[Sam]

I had tried this

=DMax("OrderDate", "Orders", "CustomerID=" & [CustomerID])

and I just get an error....one which flashes alarmingly
<grin>

If I use =DMax("[OrderDate]","[Orders]","[CustomerID]="
& " [CustomerID]")

I get a date of 04-June-04 (on all records) which is not
correct...

I'm also wondering what this will do if there are no
orders...

Any more ideas? Is the syntax incorrect? I've had
problems with date functions in this DB before - it won't
get the current date from Date(), I have to use Now()
instead, which of course pulls the time as well, so then I
have to split out the date to show what I want.

 

[Sam]

Never mind last post, got it correct now.

Thank you for all your help. Much appreciated.

Sam

 

[Dirk Goldgar]

(comments interspersed with quoted text)

I had tried this

=DMax("OrderDate", "Orders", "CustomerID=" & [CustomerID])

and I just get an error....one which flashes alarmingly
<grin>

That shouldn't happen. Putting that together with what you say at the
end of your message, I conclude that there's something else wrong with
your database. I suspect you have a broken reference. What error
message do you get?

If I use =DMax("[OrderDate]","[Orders]","[CustomerID]="
& " [CustomerID]")

I get a date of 04-June-04 (on all records) which is not
correct...

That won't happen if you fix the error that is causing my suggested
controlsource not to work, and then use that.

I'm also wondering what this will do if there are no
orders...

The text box will be Null in that case. You will get an error,
displayed in the control as #ERROR, for a new record in which the
CustomerID is not yet entered. You could get around that by modifying
the controlsource expression to

=IIf([CustomerID] Is Null, Null, DMax("OrderDate", "Orders",
"CustomerID=" & [CustomerID]))

Note that the above expression will have been wrapped to two lines by
the newsreader, but it should all go on one line in the ControlSource
property.

Any more ideas? Is the syntax incorrect?

The syntax I proposed should work. It certainly does in the Access 2002
database wherein I tested it. What version of Access are you using?

I've had
problems with date functions in this DB before - it won't
get the current date from Date(), I have to use Now()
instead, which of course pulls the time as well, so then I
have to split out the date to show what I want.

That suggests that either you have unwisely created some object named
"Date", or else you may have a broken reference. Try following Doug
Steele's instructions on fixing broken references, as published here:


http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html