Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DMax (Access 2000)

    I want to build a delete query that deletes only the last line but only when the Yes/No field is set to True.I want to use Dmax but I make it with errors. How can I achieve this? My simple query is like that :
    SELECT baskets.basketid, baskets.England
    FROM baskets
    WHERE (((baskets.England)=True));

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DMax (Access 2000)

    What is the last record? The one with the highest basketid? If so, try

    SELECT baskets.basketid, baskets.England
    FROM baskets
    WHERE baskets.England=True AND baskets.basketid = DMax("basketid", "baskets")

    When you have tested that the query returns the correct record, change it to a delete query.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DMax (Access 2000)

    Thank you for the reply.It is difficult for me to explain therefore i will try with the attached example. The table baskets contains two basketID having England to yes : it is 2 and 4. What i want is :
    1. to delete all the previous basketid having England = Yes
    2. to show only the latest basketId, in my case it is 4
    In other words, in my example, i want to delete basketid = 2 and to show basketid = 4

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DMax (Access 2000)

    A select query can display records.
    A delete query can delete records.
    A query cannot be a select query and a delete query at the same time. It is either one or the other.
    So you must create a select query to view records, and a delete query to delete records.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DMax (Access 2000)

    Yes,i told you what i intend to do. But why your select query does not show the result? As with the attached table, the query does not show the result which should be basket

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DMax (Access 2000)

    The select query would look like this:

    SELECT baskets.basketid, baskets.England
    FROM baskets
    WHERE (((baskets.basketid)=DMax("basketid","baskets","En gland=True")));

    The delete query would look like this:

    DELETE baskets.basketid, baskets.England
    FROM baskets
    WHERE (((baskets.basketid)<DMax("basketid","baskets","En gland=True")) AND ((baskets.England)=True));

  7. #7
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DMax (Access 2000)

    Thank you so much for your reply.When i try the delete query, i get the result of "too many paremeters".As you can see from my attachement, i tried both cases with "" and without but cannot succeed.
    Where is my fault ?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DMax (Access 2000)

    Attachment? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  9. #9
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DMax (Access 2000)

    Sorry !

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DMax (Access 2000)

    You are now using the SQL statement in VBA code. To do this, you must place quotes <code>"</code> before and after the SQL statement. These tell VBA where the statement begins and ends.

    If you have quotes within the SQL statement, this confuses VBA. It cannot tell which ones indicate the beginning and end of the SQL statement and which ones belong in it. To work around this, you can either double the double quotes within the SQL string:
    <code>
    strSQL = "DELETE basketid, England " & _
    "FROM baskets " & _
    "WHERE basketid<DMax(""basketid"",""baskets"",""England=T rue"") AND England=True"
    </code>
    or replace the double quotes with single quotes:
    <code>
    strSQL = "DELETE basketid, England " & _
    "FROM baskets " & _
    "WHERE basketid<DMax('basketid','baskets','England=True') AND England=True"</code>

  11. #11
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DMax (Access 2000)

    Hans, thank you so much.naturally it works now.As i wanted i delete now all the previous records of basketid where England = True.It means basketid = 4 remains and basket id = 2 will be deleted.
    And how about the opposite case ? That is to say, to delete only the last record where England = True? Changing the < to > will not do.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DMax (Access 2000)

    That would be
    <code>
    strSQL = "DELETE basketid, England " & _
    "FROM baskets " & _
    "WHERE basketid=DMax('basketid','baskets','England=True') "
    </code>
    You want to delete the last one, so you use =, not >.

  13. #13
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DMax (Access 2000)

    Thank you for everything.I really have a very high opinion of you since you not only have a deep knowledge but you also understand the real problems of the users

Posting Permissions

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