Refer to the first row of the table

  • Thread starter Thread starter samotek via AccessMonster.com
  • Start date Start date
S

samotek via AccessMonster.com

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))"
 
In
samotek via AccessMonster.com said:
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))"
Click to expand...

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.
 
Dirk said:
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.
Click to expand...


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 ?
 
In
samotek via AccessMonster.com said:
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 ?
Click to expand...

If you just copied my code, it could be because there's not actually any
field in your table named "WhenCreated". The code example I gave was
based on the idea that there is such a field in the table, containing
the date/time the record was created. If there's no such field in your
table, you have to either find a field that *is* in your table and is
suitable for sorting to find out which record is "first", and then
change the code to refer to that field, or else create such a field and
ensure that it is populated with the appropriate values.

If you think you did that, and you still got the error, then most likely
you misspelled the name of a field. As far as Access is concerned, any
name in a query that it doesn't recognize must be a parameter, so it
gives that error telling you that you didn't provide a value for it.
But really, it's more likely that it just didn't recognize the field
name because you spelled it wrong.
 
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"
 
In
samotek via AccessMonster.com said:
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"
Click to expand...

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.
 
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 said:
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
Click to expand...
[quoted text clipped - 32 lines]
Syntax error in query expression "TblClients1.afid Not FirstAfid"
Click to expand...

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.
Click to expand...
 
In
samotek via AccessMonster.com said:
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
Click to expand...

You've left off one of the parentheses I had in my original SQL
statement. Try this:

StrSQL = _
"DELETE * FROM TblClients1 WHERE afid > " & _
"(SELECT Min(T.afid) FROM TblClients1 As T)"

Also, don't name your function "str". There's a VBA function named
"Str", and giving your function the same name will interfere with its
use.
 
Thank you very much indeed ! Ig was a great help !


Dirk said:
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
Click to expand...
[quoted text clipped - 9 lines]
End Function
Click to expand...

You've left off one of the parentheses I had in my original SQL
statement. Try this:

StrSQL = _
"DELETE * FROM TblClients1 WHERE afid > " & _
"(SELECT Min(T.afid) FROM TblClients1 As T)"

Also, don't name your function "str". There's a VBA function named
"Str", and giving your function the same name will interfere with its
use.
Click to expand...
 
Back
Top