Re: Refer to the first row of the table

Dirk Goldgar wrote:
Is there any way to refer to figure in the frist row of the field in
the table. The field is called afid.For example, if the figure in the
first row is 1, or afid = 1, then to delete all the other figures:

StrSQL = " DELETE tblClients1.*, tblClients1.afid FROM tblClients1
WHERE (( (tblClients1.afid)>1))"

Before you could do anything like this, you'd have to define "first" in
a way that can be imposed by SQL. In the absence of any specific
ordering, a table is unsequenced -- just a "bag of records", as John
Vinson likes to put it. The order in which records are entered isn't
necessarily the order they are returned by a query.

You need to have some field that you can sort on; then you can select
the "first" record *in that sequence*. For example, you might have a
date/time field named "WhenEntered", that has been filled with the date
and time the record was created. Then you can find the value of afid in
the record that has the lowest value of WhenCreated. For example,

Dim FirstAfid As Long
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset(
"SELECT TOP 1 afid FROM tblClients1 ORDER BY WhenCreated")

With rs
If .EOF Then
MsgBox "No records!"
ElseIf IsNull(!afid) Then
MsgBox "No afid in first record!"
Else
FirstAfid = !afid
End If
.Close
End With

After running that code, either FirstAfid has the afid from the
earliest-entered record, or there were no records, or the "first" record
had no afid.

It's not at all clear to me what you want to do after you've got this
value. If I understood that, maybe it would be possible to write it all
in one SQL statement. As it is, with the code above, you can then build
and execute another SQL statement to tdo what you want with the
FirstAfid value.



Thank you fo your reply. Your suggestion seems to me as a solution.I get
however the error "Too few parameters".What may be the reason ?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200708/1

.

Relevant Pages

  • Re: Refer to the first row of the table
    ... Dim FirstAfid As Long ... Set rs = CurrentDb.OpenRecordset("SELECT TOP 1 afid FROM ... MsgBox "No afid in first record!" ... Although your SQL statement could be simplified, ...
    (microsoft.public.access.modulesdaovba)
  • Re: Refer to the first row of the table
    ... expression "TblClients1.afid Not FirstAfid". ... Dim FirstAfid As Long ... Set rs = CurrentDb.OpenRecordset("SELECT TOP 1 afid FROM tblClients1 ORDER ... Syntax error in query expression "TblClients1.afid Not FirstAfid" ...
    (microsoft.public.access.modulesdaovba)
  • Re: Refer to the first row of the table
    ... first row is 1, or afid = 1, then to delete all the other figures: ... Dim FirstAfid As Long ... MsgBox "No afid in first record!" ... and execute another SQL statement to tdo what you want with the ...
    (microsoft.public.access.modulesdaovba)
  • Re: Refer to the first row of the table
    ... Syntax error in query expression 'afid>SELECT MinFROM TblClients1 ... Syntax error in query expression "TblClients1.afid Not FirstAfid" ...
    (microsoft.public.access.modulesdaovba)
Loading