Thread: SQL Max() to fill field
12-10-04, 00:11 #1Registered User
- Join Date
- Oct 2004
Unanswered: SQL Max() to fill field
hey all, just wondering if someone can tell me how to set the value of a field on a form to be the result of an SQL Max() statement? I've tried many different ways and none work. The field could be bound or not, the value could appear on form load, or when the control gets the focus....but nothing's working! The query works fine, but getting it to appear in the field just isnt happening
Also, is there any function (in SQL/VB/whatever) that returns the LAST value entered into a field of a table ie. u specify a field name, and it returns the value of that field for the most recently-added record?
12-10-04, 11:36 #2Cavalier King Charles
- Join Date
- Dec 2002
- Préverenges, Switzerland
short answer: can't.
SELECTs return an arbitrary number of records: A makes no attempt to stick the return in a field, textbox, or variable because there might be hundreds of thousands of records returned.
you have a couple of choices:
myMax = Dmax("fieldNameInQuotes", "tableNameInQuotes", "WHEREclauseWithoutTheWordWHERE")
or transit through a recordset.
you will find tons of hate-mail about Dmax() and his domain aggregate cousins on this site: folk say it's slow.
but Dmax() works, takes no effort to write, is DAO/ADO independent, and if it is running on a local DB on a computer built in the past 15 years "slow" is only theoretical. think carefully before using a zillion Dmax() in a report, but otherwise don't panic too much about speed.
izycurrently using SS 2008R2