Re: Refer to the first row of the table

In 76cb49043acd7@uwe">news:76cb49043acd7@uwe,
samotek via AccessMonster.com <u15330@uwe> 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
is osmehting i Access i do not know.Now i have corrected it and it is
OK, i get no error ? Now i tried to build and sql in the smae code,
trying todelete all the afids which are not equal to FirstAfid, but i
get the error Syntax error in query expression "TblClients1.afid Not
FirstAfid".
Could yo help me correct the code ? My whole function is the
following : Public Function STR()
Dim FirstAfid As Long
Dim StrSQL As String

Dim rs As DAO.Recordset

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

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

StrSQL = " DELETE TblClients1.*, TblClients1.afid FROM TblClients1
WHERE TblClients1.afid NOT FirstAfid"
CurrentDb.Execute StrSQL

End Function
=========================

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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

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
    ... Syntax error in query expression 'afid>SELECT MinFROM TblClients1 ... 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
    ... 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)
Loading