Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Posts
    23

    Unanswered: Refer to the first row

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

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    What?

    Scary lookin statement.

    Hmmm...lets see if I have this right:

    Is there any way to refer to a number in the first row of a field in the table. The field is called afid. For example, if the number in afid of the first row is 1, or afid = 1, then to delete all the other numbers:

    Nope....still lost.
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I sometimes walk through the records deleting them until I reach the end of the file (for example, in ADO.....

    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from tblClients1 order by afid ASC"
    rs.open strSQL,currentproject.connection,adopendynamic,adl ockoptimistic
    rs.movefirst 'afid = 1
    rs.movenext 'afid = 2,3,4....
    do while not rs.eof
    rs.delete
    rs.movenext
    loop
    rs.close
    set rs = nothing
    or
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    StrSQL = "Select * from tblClients1 WHERE (((tblClients1.afid)>1))"
    rs.open strSQL,currentproject.connection,adopendynamic,adl ockoptimistic
    rs.movefirst
    do while not rs.eof
    rs.delete
    rs.movenext
    loop
    rs.close
    set rs = nothing
    or
    designing a delete query with criteria where afid <> 1
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by samotek
    StrSQL = " DELETE tblClients1.*, tblClients1.afid FROM tblClients1 WHERE (((tblClients1.afid)>1))"
    Please use the correct syntax when deleting a row from a table
    Code:
    DELETE FROM <TableName>
    WHERE <Criteria>
    George
    Home | Blog

  5. #5
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Sql

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


    copied and pasted directly from the SQL window in the QB - I try to never use a hard coded sql statement unless I thoroughly understand it.

    If your "code" does not run as anticipated , save the sql statement as query object select statement and review the data sheet of the results - if the returned criteria is what you are trying to delete then change to delete query and implement as needed.

    Newbies have to understand - to be code heavy - you have to know code.

    sometimes it is easier to
    docmd.setwarnings false
    docmd.openquery "myquery"
    docmd.setwarnings true

    than dimming and setting and
    db.execute mysql
    blah blah blah

    Sorry guys - I am having a bad morning -
    Dale Houston, TX

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •