URGENT !!! Delete Query
Sep 1, 2005
Hi,
Can some one please tell me how I can use a delete query. I have the following code, but it does not work. Please let me know where I am going wrong
---------------------------------
Dim StrSQL As String
StrSQL = "Delete FROM PLANT WHERE PLANT_CODE = ' " & PLANT_CODE_txt & " ' "
DoCmd.RunSQL StrSQL
----------------------------------
In the above and below code PLANT_CODE_txt is the form field that I am taking values from to delete from the table
(or)
----------------------------------------
Dim StrSQL As String
dim pt_code as string
pt_code = me.PLANT_CODE_txt
StrSQL = "Delete FROM PLANT WHERE PLANT_CODE = ' " & pt_code & " ' "
DoCmd.RunSQL StrSQL
--------------------------------------------------------------
And if we are using multiple delete queries should we have the DoCmd.RunSQL <variable> after every delete statement or is it after all the statements
Thanks
View Replies
ADVERTISEMENT
Apr 2, 2007
I have a query based on two tables, joined on one field (this field is the primary key in Table A. Table B doesn't have a primary key). When working inside this query, it gives me the option to delete records. However, when I try, the row disappears from the query but then if I save and close and either re-open table A or the resulting query, that record is back. It doesn't actually delete. If I go to table A directly and delete it, it's gone for good.
How can I make it so that when I delete the record inside the query it actually deletes? And if that's not an option, can I make it so that it doesn't seem like I can delete records inside the query so that people don't make the mistake?
View 2 Replies
View Related
Sep 18, 2006
Hi,
I had a query which used to work perfectly fine, then I changed one of the field names that the query was returning. And then, I rechanged the same field back to what it was and I save it. Later, whenever I run that query, it asks for a parameter with the same field name I changed even though I don't have it anymore.
Any suggestions why this is happening please!!!!
Regards,
B
:mad:
View 7 Replies
View Related
Mar 13, 2007
I have written 2 SQL queries which I wanted to combine using IF-Then statement, if possible.
the first one:
SELECT NewNumber as [Main_number]
FRom try_SQL
Where Type='A' OR type ='V'
The second one is:
SELECT OldNumber as [Main_number]
FRom try_SQL
Where Type<>'A' AND type <>'V'
I want to output the Main_number as a text file on my D drive.
At the moment I have to to output 2 different text files, then combine them.
can someone please help me?
View 4 Replies
View Related
May 19, 2011
I have two simple tables. I want to delete the records from Table1 that are on Table2. I've created a select query that gives me what I want but when I change to a delete query, I get this message: "Unable to delete from specified tables"
I think my problem has something to do with security but I can't figure out what to change.
View 13 Replies
View Related
Jun 5, 2006
Hi All,
Can anyone help me count postcode instances. I have a query that looks at my customers table (tbl_Customer_Details) post code field (PostCode), at the moment I have got it to strip out the right side of the postcode leaving me the left district side eg. HG12 8EN becomes HG12. I would then like to count how many times each postcode instance occurs so I can create a report on the result so I can track which district the customers are coming from. I hope this explains the problem. Any help would be much appreciated as I promised the Boss this for tomorrow....:confused:
this is my SQL so far, it strips out the left of the postcode:
SELECT tbl_Customer_Details.PostCode, Left([Postcode],4) AS Code
FROM tbl_Customer_Details
GROUP BY tbl_Customer_Details.PostCode;
What do I add to do the count?
View 5 Replies
View Related
Feb 25, 2008
Original Table
ozip dzip shipdate wgt Transport cost
A B 12/1/07 20 450
C D 4/1/08 15 890
E F 9/1/08 78 750
A B 12/1/07 42 789
X Y 1/1/08 17 900
C D 4/1/08 34 90
Needed Output
ozip dzip shipdate wgt Transport cost
A B 12/1/07 20 450
A B 12/1/07 42 789
E F 9/1/08 78 750
I need to create a query which extracts rows with wgt >60 and also WHEN the fields ozip, dzip and shipdate are same, the query should add the wgts and then sum of wgts should be >60- for example the first and fifth rows should be extracted since sum is 62.
I have attached the output that is required for the above example. please help
View 1 Replies
View Related
Oct 31, 2005
Hi,
I have an indexed table (unique autonumber) in Access. I have in this table an other column with every X rows a number (X varies) and the rest of the rows below are empty. This situation repeats again with another number and again all the fields under it are empty. Is there a possibility to run an update query to update all fields below to that certain number? Can you define a range (by using a range in the autonumber) to update all the fields in that range to that specific number ??
PLEASE HELP!! :(
Thanks!!!
Cheers,
View 2 Replies
View Related
Jan 5, 2006
Hi Folks,
I have a table that has Student_ID, Course, and Grade. So each student has multiple records, one for each course they took. But what I want to return is only those people who have All F's. If I just put "F" as a criteria on the grade field then it will pump out those records that have an F, including those who maybe only have 1 F, but all I want is a list of Student_ID's of all of those people who have nothing but F's. Any ideas? HELP..i'm really in a bind with this one.
View 1 Replies
View Related
Mar 29, 2005
How can I get the record with a date field that is the earliest and the latest in a table.
tblSample(ID, Name, Type, ItemDate)
The ItemDate can be any date entered by the user, so the ID will not give me the earliest and the latest record. How do I make a query that will give me the earliest ItemDate and the latest ItemDate. I need to do this in Access. Thank you.
View 1 Replies
View Related
Oct 8, 2006
Why is this querry giving me an error in syntax??
I am doing this...
set db=Currentdb
strSQL="insert into processus id= '" & txt_id.Value & "', libelle = '" & txt_libelle.Value & "',version= '" & txt_version.Value & "',type= '" & cmb_type.Value & "',cle_tri= '" & txt_cle_tri.Value & "' "
db.execute strSQL
I think this is right, but it gives me a message saying "error in INSERT syntax"
Thanks in advance
View 2 Replies
View Related
Jan 5, 2006
I am trying to create a sequential number field in a query. I cant use an auto number for the fact that i am combining multiple tables. Any help with this would be greatly appreciated.
View 14 Replies
View Related
Feb 7, 2006
Hi,
Not able to add more column heading in cross tab query.
I tried to change the query properties to add more column headings as given below.
In the query's Design view, right-click up in the area where your tables are shown and choose "Properties" from the right-click menu. The 3rd line down is for Column names. Enter what you need there.
Evn after doing it. i am not able to .
Whn I try to save or view the datasheet it says. to create a crosstab query u need to have one or more row headin one column headin and one value.
please help. its ver urgent.
thanks in adv..
View 1 Replies
View Related
Aug 6, 2007
Hi all, i need some help constructing a query that will create a table for me that does not include the Type value of 'promotion' if the same ID value exists elsewhere within the query.For instance, in the example below, i would want to delete the first entry containing 'Promotion' because ID2001 exists in two other places. However, if only one instance of ID2001 'Promotion' existed in my query, i would want to keep that entry. I would prefer to write all my data to a new table called "Test", This query has to sort out about 1000 entries when executed, otherwise i'd do it manually.I'm pulling my hair out with this one, so could someone help me?ID Company Name TypeID2001 Disc plc John PromotionID2001 Disc plc Paul AgentID2001 Disc plc George Agent
View 3 Replies
View Related
Nov 20, 2013
I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)
I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.
I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.
View 5 Replies
View Related
May 6, 2014
All I am trying to do is insert to have a form with a "Delete Record" button on it. The problem is I don't want anyone to be able to delete a record, I would like someone to have to insert a password to confirm the delete.
View 13 Replies
View Related
Apr 4, 2006
Can anyone help?
i have set up the delete query:
DELETE dbo_MP_Audit.*, dbo_CAD_SINV_HEADER.SIN_CANCELLED_YNF
FROM dbo_MP_Audit INNER JOIN dbo_CAD_SINV_HEADER ON (dbo_MP_Audit.Usr = dbo_CAD_SINV_HEADER.USR_CODE) AND (dbo_MP_Audit.Sin_DocNum = dbo_CAD_SINV_HEADER.SIN_NUMBER) AND (dbo_MP_Audit.company = dbo_CAD_SINV_HEADER.CMP_CODE)
WHERE (((dbo_CAD_SINV_HEADER.SIN_CANCELLED_YNF)=1));
but receiving error message: "could not delete from specified tables".
View 3 Replies
View Related
Aug 24, 2007
I have a table that has records listed more than one time. Some of these multiple records have in the score field a zero for the score. I would like to remove all records that are in the table more than once that have a zero for the score in the score field. Any help is greatly appreciated!
View 1 Replies
View Related
Feb 21, 2008
Hi,
i have set up a delete query to delete items ticked on a query. the form related to the query only shows the items with a "False" value in the criteria. anything set as "true" ( ticked ) is reserved for deleting. the delete query looks at the query and selects all items set to true. it works ok but i dont know how to get this work automatically without going trough the access prompts of deleting.
can this be done with code behind a button whereas if clicked, it runs the query and deletes all of the true items?
thanks,
NS
View 2 Replies
View Related
May 20, 2005
I have created a query that append all the records to another table3 that are older than two years. But at the same time delete from the original table1.
If i was doing it from a form i would
DoCmd.OpenQuery "MoveQuery"
DoCmd.RunCommand acCmdDeleteRecord
However it will be moving several records at a time any ideas?
Secondly Table1 has a 1toMany Relationship with the table2, how can i move the records from table2 to table 4 at the same time. Will the data keep its relationship?
Please help.
Many THanks
View 1 Replies
View Related
Jul 16, 2005
Quick question:
How can I automatically bypass the messages "You are about to run a delete query that will modify data in your table."? I don't want an end user to see that message, I just want the delete query to run. Is there a way to get rid of that message without supressing all the messages in the entire database?
View 2 Replies
View Related
Aug 22, 2005
Dim db As DAO.Database
Set db = currentdb
db.execute delete master.* FROM master WHERE workitem=" " and corpid=" "
Please tell me how should i write this code( whcih should execute when a form is loaded)... i want to delete all records where workitem is blank and corpid is blank.
View 2 Replies
View Related
Sep 26, 2005
I need help!
I'm trying to delete records from Table1 where the field "Name" is equal to the field "Name" in Table2. Please see below what I tried and is not working, any ideas?
DELETE table1.*
FROM table1 INNER JOIN table2 ON table1.Name = table2.Name;
Thanks! :confused:
View 2 Replies
View Related
Oct 5, 2005
Hi,
I'm trying to delete some records out of a table. I have a query that acts as a filter and is linked to two fields of the table that I want to delete from. I have put the * field from the table in the query and set the "Unique Values" property to "Yes". Access tells me "You cannot delete from the specified table".
The filter query is not an aggregate one, and if I remove it and simply have the table I want to delete from (i.e. delete all records) it will let me. I have done this loads of times before but this time it won't budge.
Any ideas?
Thanks, Tom
View 1 Replies
View Related
Mar 13, 2006
I have two tables..... one table is my "master" table, and the other table is a table of all of the records I want to delete from the "master" table.
I tried setting up a query where I match up the records and then running the query to delete records, but it did not work.
Does anyone out there possibly have a query they could toss my way that uses one table to delete from another table?
Thank you so much!!!!
View 2 Replies
View Related
Apr 17, 2006
Hi all.
Below are the info tat i am providing so that u can advice me on how to solve this problem of mine.
Table name = ImageTable
Fields = 1. Code
2. Pic
I want to pop up a MsgBox If the table is empty, i.e. there are no records in the table. How can i achieve that? Thks..
View 2 Replies
View Related