Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Constant (Access 2000)

    I have the following sql:

    Dim sql As String
    sql = " DELETE students.*, students.studentid FROM students WHERE ((students.studentid)=141259)"
    CurrentDb.Execute sql

    Is it possible to replace the figure 141259 with the constant StudentsTodelete
    And if so,how to do it?


    Dim StudentTodelete As Constant
    StudentsToDelete = 141250 or 141860 Or 141200

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Constant (Access 2000)

    Presumably you want to be able to decide which student you want to delete each time you run it.

    If you ran this code from a form , you could have a textbox (or a combobox) on the form that specifies who you want to delete,
    You can then get the value from that text box and store it in a variable and then use the variable in the sql as shown below.

    (This assumes that studentID values are stored as long integers. If they were actually textfields you would need to change this a bit.)

    Dim sql As String
    Dim lngStudent as long

    lngStudentID = me![txtStudentID]
    sql = " DELETE students.*, students.studentid FROM students WHERE ((students.studentid)=" & lngstudentID & ")"
    CurrentDb.Execute sql

    Another thought? are you actually wanting to delete more than one student at a time? This is more complicated because sql cannot say :
    Where students.studentid = 1234 or 1256

    instead it has to say

    Where (students.studentid = 1234 ) or (students.studentid =1256)
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Constant (Access 2000)

    I don't think you want a constant, which is declared as a fixed value and cannot be changed. I believe you want a variable, but there doesn't seem to be much point in repeatedly deleting the same student IDs. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> In any case, this should work:

    Dim StudentTodelete As String
    Dim sql As String

    StudentToDelete = "141250 , 141860 , 141200"

    sql = " DELETE students.*, students.studentid FROM students WHERE students.studentid <font color=blue>In (" & StudentToDelete & ")"</font color=blue>
    CurrentDb.Exec
    Charlotte

  4. #4
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Constant (Access 2000)

    Yeas,thank you.This is what i wanted and it works

Posting Permissions

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