Hi
Is there a automatic way i can delete Fields from a Table? This table gets input into the database very frequently as updates come in and i'd like something like a Query or Macro, where i can define which fields i dont want, to delete the 30+ columns of useless data.
Can't see how to do it with a Query or macro though
Anyone have any ideas?
Thanks
Hi
Does anyone know how to delete the extra blank fields you get at the end of a query. I am converting the query to a handheld database with a field limit and the extra fields push my field count over the limit. i have tried just 'deleting' them but they reapear when I reopen the query. they definitely count as fields because if I delete some real fields i can do the conversion. i dont want to alter the structure of the original database as it has been in use some time and works well. any ideas advice
Thanks
supateach
Hi again
I am still having problems with too many fields in my query so it will not convert to a handheld database. I have tried making a test query (with the wizard)from the same table and sure enough there are extra fields at the end, which do not come from the table, which reappear after they have been deleted, when you reopen the query. I have attatched the query to illustrate that I have deleted the fields and then they reappear. It does appear to be impossible to get rid of them, in which case I shall have to decrease the number of 'real' fields to get below the max the handheld can convert. Any advice gratefully received
Thanks
supateach
I have a table in my Database that uses the feilds Location and partnum. I use a barcode scanner to generate each of these fields. The problem I am having is that when our part number is scanned it contains data such as "des--. 123456 --aa". I have been doing a find/replace on this data to remove the "des--." and "--aa" but I was wondering if access has a function to do this for me so that I can get a macro to check automatically for this type of problem. Any ideas?
View 1 Replies View RelatedI need to delete data in a table that named Cap One Table. I can not use a delete query since database located on company server and it is being used by many people. So, I have created a form and put on there two text boxes - txtStartDate and txtEndDate and command button cmdDelete. An idea behind this is that a user types date in txtStartDate and txtEndDate and click command button and delition happend in table Cap One based on that criteria. I put the below code in command button click event , but it doesn't work.
Dim startDate As String
Dim EndDate As String
Dim strSql As String
startDate = txtStartDate.Value
EndDate = txtEndtDate.Value
[code]....
How can I create a criteria to delete the entire record in a query if the LnPreApprConvDt and LnPreApprFg are both blank?
View 1 Replies View RelatedI need to trim a lot of tables. Is it possible to delete table fields with reference to the field number instead of field name?
The command 'tblMyTable.Fields.Delete ("FieldName")' takes a string (field name) as input. I rather like to - in a loop - delete say,
Sub DeleteFields()
For i = 40 To 60
tblMyTable.Fields.Delete Field(i) ' Not correct syntax
Next i
End Sub
thh
Hi
I need to know if it is possible to create a delete query which will target particular fields in a row based on certain criteria in a seperate combo box(on same table).
The few ways that I have tried to do this have been unsuccesful so if anyone can shed some light on the subject it would be greatly appreciated.
I have recently gotten a handle on ASP and found it to be a very powerful tool.
I am wondering if there is a way to design a page where I can list my database tables, and by using a command, I can use asp to do the following.
1. Add or delete table
2. Add, delete, or update fields within the tables
for instance: rename a field 'PersonName' to 'MyName' without change the original values in the field 'PersonName'
I have searched all over the net and could not find information on this subject.
Thanks in advance.
I have a form with a sub form. when a record is choosen in a combo box the sub form is filled out with a record.
what I am trying to do is have a button that will copy that record to a history table then delete it off the the main table.
I cheated by using the wizard to get the code to delete the record but I am having troubles modifying the code to copy that record to the history table. Here is the code below. I have tried to insert code in several places but it just errors out.
'------------------------------------------------------------
' Master_tbl_sub_fm
'
'------------------------------------------------------------
Function Master_tbl_sub_fm()
On Error GoTo Master_tbl_sub_fm_Err
With CodeContextObject
On Error Resume Next
[Code] ....
i have two data tables, one is depending on the other. now i need to delete the main table row depending on the subtable row if it is null.
View 3 Replies View RelatedA SELECT works:
SELECT *
from mastertable
INNER JOIN secondarytable
ON mastertable.Identicalfield = secondarytable.Identicalfield
But none of these do:
DELETE
FROM mastertable
INNER JOIN secondarytable
ON mastertable.Identicalfield = secondarytable.Identicalfield
**Incorrect syntax near the keyword 'INNER'.**
DELETE
FROM mastertable JOIN secondarytable
ON (mastertable.Identicalfield = secondarytable.Identicalfield)
**Incorrect syntax near the keyword 'JOIN'.**
DELETE
FROM mastertable
WHERE (mastertable.Identicalfield = secondarytable.Identicalfield)
**The column prefix 'secondarytable' does not match with a table name or alias name used in the query.**
DELETE
FROM mastertable
WHERE IN (SELECT *
FROM secondarytable)
**Incorrect syntax near the keyword 'IN'.**
Can anyone help? This should be easy, shouldn't it?!
Thank you.
I have a table which lists amendments (amendments history file) which need to take effect of another table (M0070), I can select the records fine and so the sub query looks to be okay but I'm missing something (probably obvious) in the deletion query where by it does only delete the records selected but everything (I know that the asterisk is wrong in the code below but I'm not sure what to replace it with).
At this point I'm trying to delete all records for a specific employee on a specific contract (they can be 1 or more employee records for that individual on a contract - and they could exist on multiple contracts).
DELETE *
FROM M0070
WHERE EXISTS
(SELECT M0070.[Contract No], M0070.[Contract Name], M0070.[Employee No], M0070.Forename, M0070.Surname, M0070.Branch, M0070.[Long Desc], M0070.[Emp Post Start Date], M0070.[Days per week],
[Code] .....
I have an employee and asset database. If an employee gets fired, I need remove them from the general employee records, but I want to save a record of that employee. Is there a way to delete an employee from one table and have it automatically added to another table?
View 4 Replies View RelatedWell I have two tables, one is the workorder register and the other is the closed work orders. This is for maintenance. I have a form for creating and veiwing workorders. I need to link a command button to the form that will delete the record being viewed in the WO REG table and paste it in the Closed WO table. I think I need to use two append queries but have no idea what I am doing. Here is what I have so far.
INSERT INTO [CLOSED WO] ( WO, MMCN, TECH, NOMIN, FUALTS, TYPE, [SECTION], CLOSEDATE, OPENDATE )
SELECT [WO REG].[WO], [WO REG].[MMCN], [WO REG].[TECH], [WO REG].[NOMIN], [WO REG].[FUALTS], [WO REG].[TYPE], [WO REG].[SECTION], [WO REG].[CLOSEDATE], [WO REG].[OPENDATE]
FROM [CLOSED WO], [EQUIP REG] INNER JOIN [WO REG] ON [EQUIP REG].[MMCN]=[WO REG].[MMCN]
WHERE ((([WO REG].[WO])=[WHAT WO TO CLOSE?]));
Like I said I have no idea what I am doing, this is out of the SQL view for my "move to closed WO" query. the fields I need to move are WO, MMCN, TECH, NOMIN, FUALTS, TYPE, SECTION, CLOSEDATE, and OPENDATE.
Is this even remotly right? I know nothing of VBA an all these symbols mean nothing to me.
I need urgent help, I am required at my job to come up with a command that will check to see if a table exists, and if so, delete the table. The whole process goes like this:
There's a form, and a listbox. 2 buttons...1 is import a file and 2nd is generate report. In the listbox theres 3 excel files. You select one excel file then click on Import, followed by clicking on Generate Report button.
The whole technical process is this:
1. A csv file is imported into a temp table
2. A temp table is created and named "_ImportedSKUS".
3. An append query is exectued to add the data from the "_ImportedSKUS" temp table to the final table, "Imported SKUs".
4. Then the temp table, "_ImportedSKUs" is closed and deleted.
I have a delete command at the end of the sub, which goes like this:
DoCmd.DeleteObject acTable, "_ImportedSKUS".
But I'm also need to generate an If statement before the loop that will check to see if the "_ImportedSKUs" table exists, if so, delete it (using same code listed above) and end if.
Can anyone help me out here???
little question here:
1) table deleting
i have a table in which temporary data is stored;
With a command button i want to delete the table (or maybe just the data in it);
2) information from table in textbox
In a form i have a textbox in which the data is mentioned from the table, after clicking the delete table button, i want also the texbox to be empty
First i tried to put the following code behind the onclick event but doesn't seem to work(?)
DoCmd.DeleteObject acTable, tTable
It should be quite simple (i think?); please advise,
Thanks
if I had tblOrders OrderNo, CustomerNo, Date, OrderTotal
tblCustomers CustomerNo, Name, Street, Country, Postcode
and I was asked to delete orders placed by customer Smith, would I be correct if I put this:
DELETE tblOrders*
FROM tblOrders INNER JOIN tblCustomers ON tblOrders.CustomerNo = tblCustomers.CustomerNo
WHERE Name = "Smith"
I wasnt sure about the DELETE tblOrders to have a '*' after it or not . .
Hi,
Is it possible to delete a record from a table whilst viewing that table through a form. The awkward part though is because I have another table linked to it.
Basically the main table is Orders and the other table is Items (these items make up an order). So when I go to simply click on delete an error message appears saying that I cannot delete the record but there are records attached to it in another table. This is fair enough but is there a smooth way off deleting off the items?
Many thanks,
Paul.
Hi all, whats the simplest way to delete the last record in a table? based on
DoCmd.RunSQL "DELETE * FROM
Trying to use a button to do the above
Cheers
is there any way to get rid of deletion confirmation when you run a make-table query. though I unchecked all boxes under Confirm on Edit/Find tab, still popping up.
thanks in advance
Hi
I'm trying to deleterecords from one table. In the query there are 2 tables both joined. I get an error message come on saying
"Specify The Table Containing The Records you want to Delete"
Below is the query in SQL
DELETE tblStockRequired.StockCode, tblStockRequired.StockQTY, tblStockRequired.OrderNumber
FROM tblOrderCompleted INNER JOIN tblStockRequired ON (tblOrderCompleted.OrderNumber = tblStockRequired.OrderNumber) AND (tblOrderCompleted.StockCode = tblStockRequired.StockCode) AND (tblOrderCompleted.QTY = tblStockRequired.StockQTY);
I want ot delete all records from tblStockRequired which match up with tblOrderCompleted.
Thanks in Advanced
Hi all.
I kind of got stuck with this one.
I'm trying to use a Delete query to delete some records from Table A.
Let's say Table A has three fields: A1, A2 and A3
Then there's another table, Table B, with two fields: B1 and B2
Within one Delete Query, i need to delete all the records from Table A for which: A1=B1
Thanks for your help.
Alex
Hi.
How I can delete all rows from my table whit on query? Also I want that when I delete rows from my table index and other settings will stay in table.
How to delete table if exist using VBA. I know this was in forum but I can't find the answer. Please help its urgent.
View 8 Replies View RelatedHow do I DELETE the FIRST RECORD FROM A TABLE ?? I am using MS Access 2003
View 4 Replies View RelatedThis is my code:
my pro is string
prID is autonumber field
Code:
CurrentDb.Execute "delete from protbl where prID='" & mypro & "'"
shows this error:
Data type mismatch in criteria expression