Re: Refer to the first row of the table

Thank you so much indeed ! Your line of code which i think is very clever,
gives me a syntax error and i must have written something wrong somewhere.
Would you have a look :
Syntax error in query expression 'afid >SELECT Min(T.afid) FROM TblClients1
As T)'

Public Function str()
Dim StrSQL As String
StrSQL = " DELETE * FROM TblClients1 WHERE afid > SELECT Min(T.afid) FROM
TblClients1 As T)"
CurrentDb.Execute StrSQL

End Function


Dirk Goldgar wrote:
Thank you so much indeed. You are right in what you guesed,i just
didnt see that i have no field named "WhenCreated" and i thought this
[quoted text clipped - 32 lines]

Syntax error in query expression "TblClients1.afid Not FirstAfid"

Although your SQL statement could be simplified, there are two main
errors in it. First, you need to use the "<>" ("not equal") operator
instead of just "NOT". Second, you need to embed the *value* of
FirstAfid in the SQL string, not the variable name. Aside from those
errors, remember that you don't want to do this unless you actually
found a value for FirstAfid. So I would amend your code like this:

'---- start of revised section of code ----
With rs
If .EOF Then
MsgBox "No records!"
ElseIf IsNull(!afid) Then
MsgBox "No afid in first record!"
Else
FirstAfid = !afid
StrSQL = _
"DELETE * FROM TblClients1 WHERE afid <> " & FirstAfid
CurrentDb.Execute StrSQL, dbFailOnError
End If
.Close
End With
'---- end of revised section of code ----

Now that you've made it clear that you want to order by afid, and only
want to keep the records with the lowest afid, the whole process could
be simplified to a single delete query:

DELETE * FROM TblClients1
WHERE afid >
(SELECT Min(T.afid) FROM TblClients1 As T)

That statement uses a subquery to find out the lowest afid value, and
then deletes all records with afid greater than that value. I haven't
tested the SQL, but something along those lines ought to work.


--
Message posted via http://www.accessmonster.com

.

Relevant Pages

  • 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
    ... 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
    ... 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
    ... 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)