Results 1 to 2 of 2
  1. #1
    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?

  2. #2
    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.

    currently using SS 2008R2

Posting Permissions

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