anuanusha — 2013-09-16T01:38:30-04:00 — #1
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 & "');"
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 "
guido2004 — 2013-09-16T02:32:11-04:00 — #2
Why, what happens? Do you get an error?
anuanusha — 2013-09-16T02:57:46-04:00 — #3
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.
guido2004 — 2013-09-16T04:18:04-04:00 — #4
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.
anuanusha — 2013-09-16T05:05:29-04:00 — #5
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 "