First row in the field

[samotek]

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))"

 

[Guus2005]

You're not making much sense.
I didn't find a sentence ending with a question mark so i am guessing that you wonder why your query doesn't work?
Loose the ", tblClients1.afid" or just rewrite:

strSQL = "DELETE * FROM tblClients1 WHERE tblClients1.afid > 1"

Enjoy!

 

[Guus2005]

After re-reading you're making a little more sense. Try this:

Delete * from tblClients1 WHERE tblClients1.afid not in (
Select Top 1 from tblClients1 Order by tblclients1.afid)"

I am assuming that field afid is an autonumber field and you want to delete all but the oldest in your table.

HTH Guus

 

[samotek]

My code i have shown
StrSQL = " DELETE tblClients1.*, tblClients1.afid FROM tblClients1 WHERE (((tblClients1.afid)>1))"

works OK.
I want to ask, is it somehow possible to refer to the first row of the field in the table. If the field is called afid, for example if the value of the afid in the first row is 1, to refer to that value and write
StrSQL = " DELETE tblClients1.*, tblClients1.afid FROM tblClients1 WHERE (((tblClients1.afid)>1))"
However, if the value in the first row is 2, i.e. afid = 2, to delete all the rows that have not the value afid = 2
If the value in the first row is 3, for example afid = 3, to delete all the rows where afid is not 3


Thank you in advance

 

[Guus2005]

I thought so.

 

[Rabbie]

The code posted by Guus should do what you are requesting. The tblClients1.* will select all fields in the relevant record so you don't need tblClients1.afid in the Delete clause.

Have you actually tried what Guus gave you?