SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Sep 2013
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy problem on access with the deletion code.

    I have following code to insert in access table
    insertstring = "INSERT INTO Attendance (School_ID, Grade, Years, School_Open_Days) VALUES ('" & School.Value & "','" & Grade.Value & "','" & Year.Value & "'-1,'" & School_Open.Value & "');"
    DoCmd.RunSQL insertstring

    And with different variables for the same command, so i could insert data in two table at once from a single form, where in one table the Year would be actual and in this Attendance table , it will be Year.Value & "'-1 ( It will be less than a year that is entered in the textbox for year in the table.)

    I can insert in but while i like to delete suppose if the year is 2013 i can delete from table
    DoCmd.RunSQL "DELETE FROM School WHERE School_ID = '' & School.value & '' AND Year = '' & Year.Value & ''"
    But i cant from the table where the year is 2012 which is previously made 2012 by the code ,'" & Year.Value & "'-1, on inserting.
    DoCmd.RunSQL "DELETE FROM Attendance WHERE School_ID = '' & School.value & '' AND Year = '' & Year.Value & ' ' -1 "
    Pls Help

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Anuanusha View Post
    But i cant from the table where the year is 2012
    Why, what happens? Do you get an error?

  3. #3
    SitePoint Member
    Join Date
    Sep 2013
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes it give runtime error 3464
    Dta type mismach in criteria expression

    It works while inserting data '' & Year.Value & ' ' -1 " make the entered value in textbox 2013 to 2012 in the table, while i like to delete the record with year 2012 by using the code with '' & Year.Value & ' ' -1 ", it gives the above error.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Check the quotes you use before the - 1 (it looks like two single quotes ' ' instead of a double quote ")
    I thought you just made a mistake writing the query here in your post, but maybe you pasted the actual query you're running.

  5. #5
    SitePoint Member
    Join Date
    Sep 2013
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Check the quotes you use before the - 1 (it looks like two single quotes ' ' instead of a double quote ")
    I thought you just made a mistake writing the query here in your post, but maybe you pasted the actual query you're running.
    the single quotes are for; the year and school code is in numbers (Data type) , if i make it the data type Text then I cant do (-1) from the year, and it doesn't function well if I use double quotes for the Number value. It worlks well for
    DoCmd.RunSQL "DELETE FROM School WHERE School_ID = '' & School.value & '' AND Year = ' ' & Year.Value & ' ' " but for ' ' & Year.Value & ' ' -1 " it creates error, may be there is mistake in coding in this part ' ' & Year.Value & ' '-1 "

    Thanks


Bookmarks

Posting Permissions

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