Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

delete records

Discussion in 'Information Technology' started by Tom, Jul 28, 2006.

Tags:
  1. Tom

    Tom
    Expand Collapse
    Guest

    I`m traying to create a query that delete records by dates.
    I created a form with 2 unbound text boxes to enter dates.
    I created a delete query that the cratiria is under date. between textbox1
    and textbox2. it didnt work that well.

    Is there a better way to delete records between dates?
    What is the right way to do it?


    Thanks a lot,

    Tom
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    What you describe sounds reasonable.

    I am not sure what "didn't work that well" actually means, but try this:

    1. In query design view, choose Parameters from the Query menu.
    Enter 2 rows into the dialog that match the expression in your critiera.
    For example, if the form is named Form1, the 2 rows would be:
    [Forms].[Form1].[textbox1] Date/Time
    [Forms].[Form1].[textbox2] Date/Time

    2. If the text boxes are unbound, open the form in design view, and set the
    Format property of both text boxes to:
    Short Date
    or similar.

    This should ensure that both the form and the query understand the dates
    correctly, and so the query deletes the right records.

    If it still fails, switch the query to SQL View (View menu), and post the
    SQL statement as a folllowup to this thread.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Tom" <partner1973@yahoo.com> wrote in message
    news:u7HUTg%23cGHA.4312@TK2MSFTNGP05.phx.gbl...
    > I`m traying to create a query that delete records by dates.
    > I created a form with 2 unbound text boxes to enter dates.
    > I created a delete query that the cratiria is under date. between textbox1
    > and textbox2. it didnt work that well.
    >
    > Is there a better way to delete records between dates?
    > What is the right way to do it?
    >
    >
    > Thanks a lot,
    >
    > Tom
     
  4. Tom

    Tom
    Expand Collapse
    Guest

    Thanks for your response, your answer gave me the confidance that what I did
    was right.
    I deleted all the elements and rebuild it, it works great now.
    thanks,
    Tom
    "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    news:%23l2lr7%23cGHA.3908@TK2MSFTNGP04.phx.gbl...
    > What you describe sounds reasonable.
    >
    > I am not sure what "didn't work that well" actually means, but try this:
    >
    > 1. In query design view, choose Parameters from the Query menu.
    > Enter 2 rows into the dialog that match the expression in your critiera.
    > For example, if the form is named Form1, the 2 rows would be:
    > [Forms].[Form1].[textbox1] Date/Time
    > [Forms].[Form1].[textbox2] Date/Time
    >
    > 2. If the text boxes are unbound, open the form in design view, and set
    > the Format property of both text boxes to:
    > Short Date
    > or similar.
    >
    > This should ensure that both the form and the query understand the dates
    > correctly, and so the query deletes the right records.
    >
    > If it still fails, switch the query to SQL View (View menu), and post the
    > SQL statement as a folllowup to this thread.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Tom" <partner1973@yahoo.com> wrote in message
    > news:u7HUTg%23cGHA.4312@TK2MSFTNGP05.phx.gbl...
    >> I`m traying to create a query that delete records by dates.
    >> I created a form with 2 unbound text boxes to enter dates.
    >> I created a delete query that the cratiria is under date. between
    >> textbox1 and textbox2. it didnt work that well.
    >>
    >> Is there a better way to delete records between dates?
    >> What is the right way to do it?
    >>
    >>
    >> Thanks a lot,
    >>
    >> Tom

    >
    >
     
  5. Tom

    Tom
    Expand Collapse
    Guest

    How can I insert those parametersn into a massage box?
    I want to create a Mgsbox that say:
    Are sure you want to delete all records beteen textbox1 and textboxs2?

    Thanks,

    Tom

    "Tom" <partner1973@yahoo.com> wrote in message
    news:u7HUTg%23cGHA.4312@TK2MSFTNGP05.phx.gbl...
    > I`m traying to create a query that delete records by dates.
    > I created a form with 2 unbound text boxes to enter dates.
    > I created a delete query that the cratiria is under date. between textbox1
    > and textbox2. it didnt work that well.
    >
    > Is there a better way to delete records between dates?
    > What is the right way to do it?
    >
    >
    > Thanks a lot,
    >
    > Tom
    >
    >
     
  6. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Since the values are in the text boxes on the form, you could read them from
    there.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Tom" <partner1973@yahoo.com> wrote in message
    news:O9Erd4CdGHA.3352@TK2MSFTNGP03.phx.gbl...
    > How can I insert those parametersn into a massage box?
    > I want to create a Mgsbox that say:
    > Are sure you want to delete all records beteen textbox1 and textboxs2?
    >
    > Thanks,
    >
    > Tom
    >
    > "Tom" <partner1973@yahoo.com> wrote in message
    > news:u7HUTg%23cGHA.4312@TK2MSFTNGP05.phx.gbl...
    >> I`m traying to create a query that delete records by dates.
    >> I created a form with 2 unbound text boxes to enter dates.
    >> I created a delete query that the cratiria is under date. between
    >> textbox1 and textbox2. it didnt work that well.
    >>
    >> Is there a better way to delete records between dates?
    >> What is the right way to do it?
     
  7. Tom

    Tom
    Expand Collapse
    Guest

    Thank you, but whay is the right syntax for that?
    How do I put Value of a text box in the sring of the massage?

    Thanks again,

    Tom

    "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    news:OARUcFFdGHA.4720@TK2MSFTNGP03.phx.gbl...
    > Since the values are in the text boxes on the form, you could read them
    > from there.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Tom" <partner1973@yahoo.com> wrote in message
    > news:O9Erd4CdGHA.3352@TK2MSFTNGP03.phx.gbl...
    >> How can I insert those parametersn into a massage box?
    >> I want to create a Mgsbox that say:
    >> Are sure you want to delete all records beteen textbox1 and textboxs2?
    >>
    >> Thanks,
    >>
    >> Tom
    >>
    >> "Tom" <partner1973@yahoo.com> wrote in message
    >> news:u7HUTg%23cGHA.4312@TK2MSFTNGP05.phx.gbl...
    >>> I`m traying to create a query that delete records by dates.
    >>> I created a form with 2 unbound text boxes to enter dates.
    >>> I created a delete query that the cratiria is under date. between
    >>> textbox1 and textbox2. it didnt work that well.
    >>>
    >>> Is there a better way to delete records between dates?
    >>> What is the right way to do it?

    >
    >
     
  8. schasteen

    schasteen
    Expand Collapse
    Guest

    msgbox ("Are sure you want to delete all records beteen " &
    Forms![Formname]![textbox1] &" and " & Forms![Formname]![textboxs2] & "?")

    "Tom" wrote:

    > Thank you, but whay is the right syntax for that?
    > How do I put Value of a text box in the sring of the massage?
    >
    > Thanks again,
    >
    > Tom
    >
    > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    > news:OARUcFFdGHA.4720@TK2MSFTNGP03.phx.gbl...
    > > Since the values are in the text boxes on the form, you could read them
    > > from there.
    > >
    > > --
    > > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > > Tips for Access users - http://allenbrowne.com/tips.html
    > > Reply to group, rather than allenbrowne at mvps dot org.
    > >
    > > "Tom" <partner1973@yahoo.com> wrote in message
    > > news:O9Erd4CdGHA.3352@TK2MSFTNGP03.phx.gbl...
    > >> How can I insert those parametersn into a massage box?
    > >> I want to create a Mgsbox that say:
    > >> Are sure you want to delete all records beteen textbox1 and textboxs2?
    > >>
    > >> Thanks,
    > >>
    > >> Tom
    > >>
    > >> "Tom" <partner1973@yahoo.com> wrote in message
    > >> news:u7HUTg%23cGHA.4312@TK2MSFTNGP05.phx.gbl...
    > >>> I`m traying to create a query that delete records by dates.
    > >>> I created a form with 2 unbound text boxes to enter dates.
    > >>> I created a delete query that the cratiria is under date. between
    > >>> textbox1 and textbox2. it didnt work that well.
    > >>>
    > >>> Is there a better way to delete records between dates?
    > >>> What is the right way to do it?

    > >
    > >

    >
    >
    >
     
  9. Tom

    Tom
    Expand Collapse
    Guest

    Thank you so much.
    Few more issues, please
    When I run my Delete query, there 2 conformation from access, one about
    commit a change and second about how many rows were deleted.
    I would like to eliminate them in the code( I know I can go to "options" and
    remove the checkmarks). If I choose "No" in one of these masssages I get run
    time error because of my code.
    second, I would like to create my own massage that notify about how many
    records were deleted

    Thanks for the help,

    Tom

    "schasteen" <schasteen@discussions.microsoft.com> wrote in message
    news:D196C61C-3E27-484F-8622-302CDA784187@microsoft.com...
    > msgbox ("Are sure you want to delete all records beteen " &
    > Forms![Formname]![textbox1] &" and " & Forms![Formname]![textboxs2] &
    > "?")
    >
    > "Tom" wrote:
    >
    >> Thank you, but whay is the right syntax for that?
    >> How do I put Value of a text box in the sring of the massage?
    >>
    >> Thanks again,
    >>
    >> Tom
    >>
    >> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    >> news:OARUcFFdGHA.4720@TK2MSFTNGP03.phx.gbl...
    >> > Since the values are in the text boxes on the form, you could read them
    >> > from there.
    >> >
    >> > --
    >> > Allen Browne - Microsoft MVP. Perth, Western Australia.
    >> > Tips for Access users - http://allenbrowne.com/tips.html
    >> > Reply to group, rather than allenbrowne at mvps dot org.
    >> >
    >> > "Tom" <partner1973@yahoo.com> wrote in message
    >> > news:O9Erd4CdGHA.3352@TK2MSFTNGP03.phx.gbl...
    >> >> How can I insert those parametersn into a massage box?
    >> >> I want to create a Mgsbox that say:
    >> >> Are sure you want to delete all records beteen textbox1 and textboxs2?
    >> >>
    >> >> Thanks,
    >> >>
    >> >> Tom
    >> >>
    >> >> "Tom" <partner1973@yahoo.com> wrote in message
    >> >> news:u7HUTg%23cGHA.4312@TK2MSFTNGP05.phx.gbl...
    >> >>> I`m traying to create a query that delete records by dates.
    >> >>> I created a form with 2 unbound text boxes to enter dates.
    >> >>> I created a delete query that the cratiria is under date. between
    >> >>> textbox1 and textbox2. it didnt work that well.
    >> >>>
    >> >>> Is there a better way to delete records between dates?
    >> >>> What is the right way to do it?
    >> >
    >> >

    >>
    >>
    >>
     
  10. schasteen

    schasteen
    Expand Collapse
    Guest

    You can in your code turn off the wrning messages and turn them back on when
    complete.
    DoCmd.SetWarnings False
    .....
    DoCmd.SetWarnings true
    and just use a messagebox to show how many records were deleted.


    "Tom" wrote:

    > Thank you so much.
    > Few more issues, please
    > When I run my Delete query, there 2 conformation from access, one about
    > commit a change and second about how many rows were deleted.
    > I would like to eliminate them in the code( I know I can go to "options" and
    > remove the checkmarks). If I choose "No" in one of these masssages I get run
    > time error because of my code.
    > second, I would like to create my own massage that notify about how many
    > records were deleted
    >
    > Thanks for the help,
    >
    > Tom
    >
    > "schasteen" <schasteen@discussions.microsoft.com> wrote in message
    > news:D196C61C-3E27-484F-8622-302CDA784187@microsoft.com...
    > > msgbox ("Are sure you want to delete all records beteen " &
    > > Forms![Formname]![textbox1] &" and " & Forms![Formname]![textboxs2] &
    > > "?")
    > >
    > > "Tom" wrote:
    > >
    > >> Thank you, but whay is the right syntax for that?
    > >> How do I put Value of a text box in the sring of the massage?
    > >>
    > >> Thanks again,
    > >>
    > >> Tom
    > >>
    > >> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    > >> news:OARUcFFdGHA.4720@TK2MSFTNGP03.phx.gbl...
    > >> > Since the values are in the text boxes on the form, you could read them
    > >> > from there.
    > >> >
    > >> > --
    > >> > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > >> > Tips for Access users - http://allenbrowne.com/tips.html
    > >> > Reply to group, rather than allenbrowne at mvps dot org.
    > >> >
    > >> > "Tom" <partner1973@yahoo.com> wrote in message
    > >> > news:O9Erd4CdGHA.3352@TK2MSFTNGP03.phx.gbl...
    > >> >> How can I insert those parametersn into a massage box?
    > >> >> I want to create a Mgsbox that say:
    > >> >> Are sure you want to delete all records beteen textbox1 and textboxs2?
    > >> >>
    > >> >> Thanks,
    > >> >>
    > >> >> Tom
    > >> >>
    > >> >> "Tom" <partner1973@yahoo.com> wrote in message
    > >> >> news:u7HUTg%23cGHA.4312@TK2MSFTNGP05.phx.gbl...
    > >> >>> I`m traying to create a query that delete records by dates.
    > >> >>> I created a form with 2 unbound text boxes to enter dates.
    > >> >>> I created a delete query that the cratiria is under date. between
    > >> >>> textbox1 and textbox2. it didnt work that well.
    > >> >>>
    > >> >>> Is there a better way to delete records between dates?
    > >> >>> What is the right way to do it?
    > >> >
    > >> >
    > >>
    > >>
    > >>

    >
    >
    >
     
  11. Tom

    Tom
    Expand Collapse
    Guest

    Thanks for your response,
    I tried to use docmd.setwarnings=false, usually it eliminate the massages
    but not in this case.
    can tou help me with the syntax for the msgbox that show how many were
    deleted?

    Thanks a lot,
    Tom
    "schasteen" <schasteen@discussions.microsoft.com> wrote in message
    news:FE724347-2930-47F2-A8F2-9D2A29C424A6@microsoft.com...
    > You can in your code turn off the wrning messages and turn them back on
    > when
    > complete.
    > DoCmd.SetWarnings False
    > ....
    > DoCmd.SetWarnings true
    > and just use a messagebox to show how many records were deleted.
    >
    >
    > "Tom" wrote:
    >
    >> Thank you so much.
    >> Few more issues, please
    >> When I run my Delete query, there 2 conformation from access, one about
    >> commit a change and second about how many rows were deleted.
    >> I would like to eliminate them in the code( I know I can go to "options"
    >> and
    >> remove the checkmarks). If I choose "No" in one of these masssages I get
    >> run
    >> time error because of my code.
    >> second, I would like to create my own massage that notify about how many
    >> records were deleted
    >>
    >> Thanks for the help,
    >>
    >> Tom
    >>
    >> "schasteen" <schasteen@discussions.microsoft.com> wrote in message
    >> news:D196C61C-3E27-484F-8622-302CDA784187@microsoft.com...
    >> > msgbox ("Are sure you want to delete all records beteen " &
    >> > Forms![Formname]![textbox1] &" and " & Forms![Formname]![textboxs2] &
    >> > "?")
    >> >
    >> > "Tom" wrote:
    >> >
    >> >> Thank you, but whay is the right syntax for that?
    >> >> How do I put Value of a text box in the sring of the massage?
    >> >>
    >> >> Thanks again,
    >> >>
    >> >> Tom
    >> >>
    >> >> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    >> >> news:OARUcFFdGHA.4720@TK2MSFTNGP03.phx.gbl...
    >> >> > Since the values are in the text boxes on the form, you could read
    >> >> > them
    >> >> > from there.
    >> >> >
    >> >> > --
    >> >> > Allen Browne - Microsoft MVP. Perth, Western Australia.
    >> >> > Tips for Access users - http://allenbrowne.com/tips.html
    >> >> > Reply to group, rather than allenbrowne at mvps dot org.
    >> >> >
    >> >> > "Tom" <partner1973@yahoo.com> wrote in message
    >> >> > news:O9Erd4CdGHA.3352@TK2MSFTNGP03.phx.gbl...
    >> >> >> How can I insert those parametersn into a massage box?
    >> >> >> I want to create a Mgsbox that say:
    >> >> >> Are sure you want to delete all records beteen textbox1 and
    >> >> >> textboxs2?
    >> >> >>
    >> >> >> Thanks,
    >> >> >>
    >> >> >> Tom
    >> >> >>
    >> >> >> "Tom" <partner1973@yahoo.com> wrote in message
    >> >> >> news:u7HUTg%23cGHA.4312@TK2MSFTNGP05.phx.gbl...
    >> >> >>> I`m traying to create a query that delete records by dates.
    >> >> >>> I created a form with 2 unbound text boxes to enter dates.
    >> >> >>> I created a delete query that the cratiria is under date. between
    >> >> >>> textbox1 and textbox2. it didnt work that well.
    >> >> >>>
    >> >> >>> Is there a better way to delete records between dates?
    >> >> >>> What is the right way to do it?
    >> >> >
    >> >> >
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
     
  12. schasteen

    schasteen
    Expand Collapse
    Guest

    There is no "=" sign in the expression. Setting the warnings to false will
    not show any confirmation or error messages. To show how many records are
    deleted, you will have to first know how many records meet the criteria.
    Something like:

    Dim sqlst As String
    Dim con As Object
    Dim rs As Object
    Dim RecordDelete as integer

    sqlst = "Select Count([Primary Key from your table] as CountToDelete " _
    &"From YourTable " _
    &" Where yourDate between #" & [text1] & "# and #" & [Text2] &"#"

    Set con = Application.CurrentProject.Connection
    Set rs = CreateObject("ADODB.recordset")

    rs.Open sqlst, con, 1

    If not rs.eof then
    RecordDelete = rs![CountToDelete]
    else
    RecordDelete =0
    end if

    Runyour delete queary

    msgbox("You deleted " & RecordDelete & " records")

    "Tom" wrote:

    > Thanks for your response,
    > I tried to use docmd.setwarnings=false, usually it eliminate the massages
    > but not in this case.
    > can tou help me with the syntax for the msgbox that show how many were
    > deleted?
    >
    > Thanks a lot,
    > Tom
     
  13. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Tom, you really need to Execute the query statement instead of using RunSQL.
    There is no confirmation message to annoy you, but you can still determine
    if an error occurred. You can also determine the number of records deleted.

    This example assumes you want to delete from Table1, where the field named
    MyDate is between the values in the 2 text boxes on the same form. It builds
    the delete query string, executes it, generates a trappable error if the
    deletion does not complete successfully, and reports how many records were
    deleted.

    Dim db As DAO.Database
    Dim strSql As String
    Const strcJetDate = "\#mm\/dd\/yyyy\#"

    If IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate) Then
    Set db = dbEngine(0)(0)
    strSql = "DELETE FROM Table1 WHERE [MyDate] Between " & _
    Format(Me.txtStartDate, strcJetDate) & " And " & _
    Format(Me.txtEndDate, strcJetDate) & ";"
    db.Execute strSql, dbFailOnError
    MsgBox db.RecordsAffected & " record(s) deleted."
    Else
    MsgBox "Both dates required."
    End If

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Tom" <partner1973@yahoo.com> wrote in message
    news:%23tfhELQdGHA.4276@TK2MSFTNGP03.phx.gbl...
    > Thanks for your response,
    > I tried to use docmd.setwarnings=false, usually it eliminate the massages
    > but not in this case.
    > can tou help me with the syntax for the msgbox that show how many were
    > deleted?
    >
    > Thanks a lot,
    > Tom
    > "schasteen" <schasteen@discussions.microsoft.com> wrote in message
    > news:FE724347-2930-47F2-A8F2-9D2A29C424A6@microsoft.com...
    >> You can in your code turn off the wrning messages and turn them back on
    >> when
    >> complete.
    >> DoCmd.SetWarnings False
    >> ....
    >> DoCmd.SetWarnings true
    >> and just use a messagebox to show how many records were deleted.
    >>
    >>
    >> "Tom" wrote:
    >>
    >>> Thank you so much.
    >>> Few more issues, please
    >>> When I run my Delete query, there 2 conformation from access, one about
    >>> commit a change and second about how many rows were deleted.
    >>> I would like to eliminate them in the code( I know I can go to "options"
    >>> and
    >>> remove the checkmarks). If I choose "No" in one of these masssages I get
    >>> run
    >>> time error because of my code.
    >>> second, I would like to create my own massage that notify about how many
    >>> records were deleted
    >>>
    >>> Thanks for the help,
    >>>
    >>> Tom
    >>>
    >>> "schasteen" <schasteen@discussions.microsoft.com> wrote in message
    >>> news:D196C61C-3E27-484F-8622-302CDA784187@microsoft.com...
    >>> > msgbox ("Are sure you want to delete all records beteen " &
    >>> > Forms![Formname]![textbox1] &" and " & Forms![Formname]![textboxs2] &
    >>> > "?")
    >>> >
    >>> > "Tom" wrote:
    >>> >
    >>> >> Thank you, but whay is the right syntax for that?
    >>> >> How do I put Value of a text box in the sring of the massage?
    >>> >>
    >>> >> Thanks again,
    >>> >>
    >>> >> Tom
    >>> >>
    >>> >> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    >>> >> news:OARUcFFdGHA.4720@TK2MSFTNGP03.phx.gbl...
    >>> >> > Since the values are in the text boxes on the form, you could read
    >>> >> > them
    >>> >> > from there.
    >>> >> >
    >>> >> > "Tom" <partner1973@yahoo.com> wrote in message
    >>> >> > news:O9Erd4CdGHA.3352@TK2MSFTNGP03.phx.gbl...
    >>> >> >> How can I insert those parametersn into a massage box?
    >>> >> >> I want to create a Mgsbox that say:
    >>> >> >> Are sure you want to delete all records beteen textbox1 and
    >>> >> >> textboxs2?
    >>> >> >>
    >>> >> >> Thanks,
    >>> >> >>
    >>> >> >> Tom
    >>> >> >>
    >>> >> >> "Tom" <partner1973@yahoo.com> wrote in message
    >>> >> >> news:u7HUTg%23cGHA.4312@TK2MSFTNGP05.phx.gbl...
    >>> >> >>> I`m traying to create a query that delete records by dates.
    >>> >> >>> I created a form with 2 unbound text boxes to enter dates.
    >>> >> >>> I created a delete query that the cratiria is under date. between
    >>> >> >>> textbox1 and textbox2. it didnt work that well.
    >>> >> >>>
    >>> >> >>> Is there a better way to delete records between dates?
    >>> >> >>> What is the right way to do it?
     
  14. Tom

    Tom
    Expand Collapse
    Guest

    Thank you both.
    Allen, it works great.
    "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    news:eEkdqqQdGHA.1856@TK2MSFTNGP03.phx.gbl...
    > Tom, you really need to Execute the query statement instead of using
    > RunSQL. There is no confirmation message to annoy you, but you can still
    > determine if an error occurred. You can also determine the number of
    > records deleted.
    >
    > This example assumes you want to delete from Table1, where the field named
    > MyDate is between the values in the 2 text boxes on the same form. It
    > builds the delete query string, executes it, generates a trappable error
    > if the deletion does not complete successfully, and reports how many
    > records were deleted.
    >
    > Dim db As DAO.Database
    > Dim strSql As String
    > Const strcJetDate = "\#mm\/dd\/yyyy\#"
    >
    > If IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate) Then
    > Set db = dbEngine(0)(0)
    > strSql = "DELETE FROM Table1 WHERE [MyDate] Between " & _
    > Format(Me.txtStartDate, strcJetDate) & " And " & _
    > Format(Me.txtEndDate, strcJetDate) & ";"
    > db.Execute strSql, dbFailOnError
    > MsgBox db.RecordsAffected & " record(s) deleted."
    > Else
    > MsgBox "Both dates required."
    > End If
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Tom" <partner1973@yahoo.com> wrote in message
    > news:%23tfhELQdGHA.4276@TK2MSFTNGP03.phx.gbl...
    >> Thanks for your response,
    >> I tried to use docmd.setwarnings=false, usually it eliminate the massages
    >> but not in this case.
    >> can tou help me with the syntax for the msgbox that show how many were
    >> deleted?
    >>
    >> Thanks a lot,
    >> Tom
    >> "schasteen" <schasteen@discussions.microsoft.com> wrote in message
    >> news:FE724347-2930-47F2-A8F2-9D2A29C424A6@microsoft.com...
    >>> You can in your code turn off the wrning messages and turn them back on
    >>> when
    >>> complete.
    >>> DoCmd.SetWarnings False
    >>> ....
    >>> DoCmd.SetWarnings true
    >>> and just use a messagebox to show how many records were deleted.
    >>>
    >>>
    >>> "Tom" wrote:
    >>>
    >>>> Thank you so much.
    >>>> Few more issues, please
    >>>> When I run my Delete query, there 2 conformation from access, one about
    >>>> commit a change and second about how many rows were deleted.
    >>>> I would like to eliminate them in the code( I know I can go to
    >>>> "options" and
    >>>> remove the checkmarks). If I choose "No" in one of these masssages I
    >>>> get run
    >>>> time error because of my code.
    >>>> second, I would like to create my own massage that notify about how
    >>>> many
    >>>> records were deleted
    >>>>
    >>>> Thanks for the help,
    >>>>
    >>>> Tom
    >>>>
    >>>> "schasteen" <schasteen@discussions.microsoft.com> wrote in message
    >>>> news:D196C61C-3E27-484F-8622-302CDA784187@microsoft.com...
    >>>> > msgbox ("Are sure you want to delete all records beteen " &
    >>>> > Forms![Formname]![textbox1] &" and " & Forms![Formname]![textboxs2]
    >>>> > &
    >>>> > "?")
    >>>> >
    >>>> > "Tom" wrote:
    >>>> >
    >>>> >> Thank you, but whay is the right syntax for that?
    >>>> >> How do I put Value of a text box in the sring of the massage?
    >>>> >>
    >>>> >> Thanks again,
    >>>> >>
    >>>> >> Tom
    >>>> >>
    >>>> >> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    >>>> >> news:OARUcFFdGHA.4720@TK2MSFTNGP03.phx.gbl...
    >>>> >> > Since the values are in the text boxes on the form, you could read
    >>>> >> > them
    >>>> >> > from there.
    >>>> >> >
    >>>> >> > "Tom" <partner1973@yahoo.com> wrote in message
    >>>> >> > news:O9Erd4CdGHA.3352@TK2MSFTNGP03.phx.gbl...
    >>>> >> >> How can I insert those parametersn into a massage box?
    >>>> >> >> I want to create a Mgsbox that say:
    >>>> >> >> Are sure you want to delete all records beteen textbox1 and
    >>>> >> >> textboxs2?
    >>>> >> >>
    >>>> >> >> Thanks,
    >>>> >> >>
    >>>> >> >> Tom
    >>>> >> >>
    >>>> >> >> "Tom" <partner1973@yahoo.com> wrote in message
    >>>> >> >> news:u7HUTg%23cGHA.4312@TK2MSFTNGP05.phx.gbl...
    >>>> >> >>> I`m traying to create a query that delete records by dates.
    >>>> >> >>> I created a form with 2 unbound text boxes to enter dates.
    >>>> >> >>> I created a delete query that the cratiria is under date.
    >>>> >> >>> between
    >>>> >> >>> textbox1 and textbox2. it didnt work that well.
    >>>> >> >>>
    >>>> >> >>> Is there a better way to delete records between dates?
    >>>> >> >>> What is the right way to do it?

    >
    >
     

Share This Page