Delete File Based On Query.

May 2, 2005

Ok, I have kind of a confusing thing.

Every employee has an ID, and a start date.

Some employees have pictures (stored in a folder).

When someone is terminated, the picture does not get erased (as it shoud).

So, I need to run a query that deletes any pictures older than the start date of the employee.

Any help on this? Not sure where to begin. I suppose I should create a query with employees who have pictures. But how do I compare a files date to an entry in a table?



Example table:


ID Emp Start File
101 John Smith 5/1/2005 \file_serveremppic101.jpg

View Replies


ADVERTISEMENT

Delete Records Based On Query

Feb 2, 2006

Hi,

I'm getting really mixed up here, Im trying to delete all records in table products based on the following query;

SELECT products.*, tbl_stage_product_update.[Catalogue No]

FROM tbl_stage_product_update INNER JOIN products ON
tbl_stage_product_update.[Product Code] = products.pID

WHERE (((tbl_stage_product_update.[Catalogue No])="0"));

I've tried using the exists keyword with the previous query as a sub query, but it seems to delete all records form table products.

can anyone offer any advice.

TIA.

Mark

View 2 Replies View Related

Delete Query Based On Another Table

Jul 15, 2006

I am trying to run the following Delete Query

DELETE Master.*
FROM Master
WHERE (((Master.glm_series)=506) AND ((Master.glm_account) In (SELECT glm_account from glj )) AND ((Master.glm_prft_ctr) In (SELECT glm_prft_ctr from glj)));


The problem is it is trying to delete all the records with the account numbers = and ALL the records with profit center equal. I am getting 2564 records instead on 147 records.

The two tables are related by glm_account AND glm_prft_ctr - BOTH must be equal in order to delete

If I do an INNER Join or add the other table to the Query it gives me "Cannot Delete Records from Specified Table"

Help

TIA
Nique

View 1 Replies View Related

Queries :: Create Query To Find Certain Records Based On Data In CSV File

Aug 14, 2014

This may not even be possible, but I am looking to create a Query that can locate records in an Access Table based on 2 columns of data that I have stored in a CSV file.

My table contains several fields, 2 of which are "Dept" and "SKU" and has over a million records.

My CSV file contains 3 fields: "Dept", "SKU" and "Total" - total being the number of times that particular Dept/SKU combination is used.

I need to be able to parse the dept/sku values from each row in the CSV to the query and locate only the records that contain the same values in the Access table.

The plan being to delete out those that are identified by the query.

View 6 Replies View Related

How To Use Data From Uploaded Excel File And Display Chart Based On Query

Aug 12, 2015

I have a graph chart and my row source type is a Query. So I retrieved data from the Query that I built.

However , I want to get the data from the uploaded excel file.

I have a form that will prompt user in the beginning to upload the excel file and replaced the old table in Access- "CrossSystemData"

Basically , I want to pull data from Excel file , Run the query(Data from excel file) then excute to "display a chart based on my query" ... Is it possible ?

Query SQL: "PolyWrongRegInsCount"

Code:
SELECT GROUPING.INSTITUTION, Count(*) AS NO_OF_GROUP
FROM (SELECT DISTINCT CATEGORY, GROUP_NO, INSTITUTION, IIF(Left(INSTITUTION,1)="P","POLYCLINIC","HOSPITAL") AS INS_TYPE FROM CrossSystemData WHERE INSTITUTION NOT LIKE "*UNKNOWN*") AS GROUPING
WHERE (((GROUPING.CATEGORY)="WRONG REG") AND ((GROUPING.INS_TYPE)="POLYCLINIC"))
GROUP BY GROUPING.INSTITUTION;

My Chart SQL :

Code:
SELECT PolyWrongRegInsCount.[INSTITUTION], PolyWrongRegInsCount.[NO_OF_GROUP]
FROM PolyWrongRegInsCount;

View 3 Replies View Related

General :: Delete File From FTP Server

Nov 2, 2014

I need deleting a file from a ftp server. I have the code for uploading but I can't seem to figure out how to delete a file. This is the FTP module I'm using

Code:

Public Sub UploadFileToFTPServer()
On Error GoTo ErrHandler
Dim lngRet As Long
Dim abytData() As Long
Dim lngBytesWritten As Long

[Code] ....

And this is the code for uploading i use in the form when i click a button

Code:
Dim objFTP As FTP
Const conTARGET = "ftpserver"
Set objFTP = New FTP
With objFTP

[Code] ....

All that works like a charm.

Code to delete a file from the ftp server, i'm kind of stuck.

View 3 Replies View Related

Delete User Information In System.mdw File?

Jun 15, 2006

Is it possible to delete user information in workgroup information (System.mdw) file from command line or programmatically?

Thanks

View 1 Replies View Related

Modules & VBA :: How To Delete File On Http Server

Sep 13, 2013

This is a follow-on to my post here: [URL]....

How can I change the code to delete the file if it exists?

View 1 Replies View Related

Query Lets Me Delete But Doesn't Actually Delete...

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

Modules & VBA :: Deleting Record And Then Delete Listed File?

Jan 7, 2015

What is the correct syntax that would delete a file when the record is deleted. The file's path is listed in a record field, MailLocation. Every time I try this code, I receive an error!

I've tried the below, and number of iterations, including calling the killfile differently (me![MailLocation], me.MailLocation).

Code:
Private Sub Form_AfterDelConfirm(Status As Integer)
Dim KillFile As String
KillFile = me!MailLocation
Kill KillFile
End Sub

View 6 Replies View Related

Delete Records Based On Date Criteria

Aug 16, 2005

I have a table that stores records by "return date". I would like to create a delete query that removes all records that fall outside of the date range entered on a specific form (frmMainMenu).

I can deleted the records that match the form criteria, however that is not what I need. The criteria used to delete the records was:

Between [Forms]![frmMainMenu]![from date] And [Forms]![frmMainMenu]![to date]

Let's say that the frmMainMenu contain the date range of 8/1/05 to 8/10/05, however the table contained data from 6/1/05-8/14/05, I would like for the query to delete all records that have a date 6/1/05-07/31/05 and 08/11/05-08/14/05.

Any assistance would be greatly appreciated. Thanks in advance for your help![/SIZE]

View 1 Replies View Related

Forms :: Delete Records Based On Inputs

Nov 23, 2013

I have a form Delete which contains four fields i.e. Date, City, Depots and Vendor, which has combobox.

I am trying to delete a record from table "Sheet1" based on the combobox, for which i have written the following code, but getting an error at the lines highlighted in red :

Private Sub Command30_Click()
On Error GoTo Err_delete_Click
Dim stDocName As String
Dim intResponse As Integer
intResponse = MsgBox("Are you sure you want to delete this record?", vbYesNo + vbExclamation, "Cash Management Team")
Select Case intResponse
Case vbYes
CurrentDb.Execute _

[Code]...

View 9 Replies View Related

Delete Records From One Table Based On Record From Another

Oct 10, 2014

I want to Delete all records from the Table Named "CustomerWiseRotaryTillerRates" (Given Below) if the State in the State Filed in found in the "Customer_Master" Table (attached Herewith). What will be the correct procedure ?

I followed the steps referred in page no. 67 & 68 in the Book "Microsoft Access 2007 Data Analysis" by Mr. Michel Alexender.

Customer Name
State
Product_Code
Product_Name
Price_perunit

TSI Engineering Indutries (P) Ltd.
Assam
P_05
1500_Rotary_Tiller_220_RPM
Rs. 63,722.00

Kishan Krishi Engineering Works
Jharkhand
P_05
1500_Rotary_Tiller_220_RPM
Rs. 62,997.00

TSI Engineering Indutries (P) Ltd.
Assam
P_06
1750_Rotary_Tiller_220_RPM
Rs. 66,663.00

Shri_Tata_Enterprises
Bihar
P_06
1750_Rotary_Tiller_220_RPM
Rs. 65,683.00

View 1 Replies View Related

How To Delete Record Using Delete Query

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

Reports :: Delete / Ignore A Page Based On Field Value

Oct 18, 2013

So I have a report with two pages. One page is about product exchange information and the other one payment. This report can either be printed or saved as a pdf (one pdf for both pages) and e-mailed.

This works fine, however if the item is under warranty then the exchange is for free and I do not want to print/e-mail the 2nd page (payment).My trigger field for this is "Warranty" which can be "Yes" or "No".How can I prevent that 2nd page from printing/e-mailing? Can it be simply skipped when the report is being created based on my field?

View 2 Replies View Related

Queries :: Delete Records Based On Criteria In Another Table

Jun 3, 2013

I am trying to create a delete query that, for a given person, deletes records in Table B that do not have a corresponding record in Table A.

Here are the relevant tables:

tblStates holds StateID, StateName, and RegionID (RegionID is a FK to tblRegions).
tblPeopleStates is a junction table between tblPeople and tblStates.

It lists states assigned to people. It has 3 fields: PersonStateID, PersonID, StateID.

tblPeopleRegions is a junction table between tblPeople and tblRegions.

It lists regions assigned to people. It has 3 fields: PersonRegionID, PersonID, RegionID.

For a given PersonID, I need to delete records (i.e., states) in tblPeopleStates whose RegionID is *not* in tblPeopleRegions.

For example, pretend that tblStates shows that State IDs 1, 5, and 6 are all in Region ID (i.e., all have a RegionID = 10).

If Joe (PersonID = 200) has StateIDs 1, 5, and 6 in tblPeopleStates, but doesn't have a record for RegionID = 10 in tblPeopleRegions, I need to delete his three records in tblPeopleStates (i.e., the ones where StateID = 1, 5, and 6).

PersonID will be found on [Forms]![frmMain]![subform1].[Form]![subform2].Form]![PersonID]

View 8 Replies View Related

Modules & VBA :: Delete From Table Based On List Box Selection

Nov 21, 2013

Upon the results which populate my list box what I am trying to do is allow the user to select various values from the list box and then hit a button which deletes the values from the table. My code is currently:

Code:
Private Sub Del_TsCorrection_tbox_Click()
Dim lngID As Long
Dim strSQL As String

[code]...

But it doesn't delete, infact it doesn't do anything. The list box values are still in the list box and if I look in the table they are still there also.

View 10 Replies View Related

Delete Duplicate Records Based On Multiple Criteria

Sep 21, 2015

I need developing a new delete query criteria to add onto existing delete duplicate queries for deleting duplicate values. I need the new delete duplicate query to take precedent over the other two so that those values retained in the first query are kept retained after going through the second and third delete queries. Attached is a document laying out the request.

View 1 Replies View Related

Forms :: Add / Edit And Delete Records From A Table Based On Combo

Dec 12, 2013

I have a form to add, edit, and delete Records from a table. I am using the following VBA

Private Sub cmdEdit_Click()
If Not (Me.frmlEmpDetailsSub.Form.Recordset.EOF And Me.frmlEmpDetailsSub.Form.Recordset.BOF) Then
With Me.frmlEmpDetailsSub.Form.Recordset
Me.txtAddEditname = .Fields("Name")
Me.cboRoster = .Fields("Roster")
Me.cboPermFctn = .Fields("PermFctn")
End With
End If
End Sub

Instead of referring to the Subform to load the data i would like to refer to a combobox:

cboSearchName

Its not an issue but This Combobox contains 5 columns...

View 2 Replies View Related

Forms :: Delete Records In Subform Based On Check Box In Parent Form

Mar 1, 2015

I have a parent form and connected to it is a subform. On the parent form I have a checkbox which enables and disables fields on the parent form and also hides the subform.

What I want to do is when the user unchecks the checkbox, this action also deletes the associated subform records, if there are any.I'm sure that this can be done with an SQL Delete query in VBA.

View 5 Replies View Related

Delete Certain Records Based On Selected Date - Data Type Mismatch In Criteria Expression

Aug 24, 2015

I want to delete certain records based on the selected date. However, I come across with this is error - Run time error '3464' (Data type mismatch in criteria expression).This part is highlighted in yellow. I even used the debug.print to test out if the sql statement is executed properly.

Code:
DoCmd.RunSQL DelSummarySQL

Here is my full code

Code:
Private Sub cmd_Delete_Click()
Dim DelSummarySQL As String
Dim StartRange As Date

[code]...

View 2 Replies View Related

Queries :: Differentiate A Query Based On All Group Records Or A Query Based On Only One Record

Dec 22, 2014

I have a combo named cbogroup. I have a tblGroup with several records (active, non-active, nursery, etc.). One of the records is *ALL*. Using the CboGroup the user can pick any of the records. Howeverr, if they pick the *ALL* record, I want the query to pull up animalID based on all records in the TblGroup. If another record is picked (i.e. nursery), then the query will pull up only animalIDs that are in the 'Nursery'.Can I put a (iff then) in a query in order to differentiate a query based on all group records or a query based on only one record?

View 14 Replies View Related

Import Certain Files Based On File Name

Mar 8, 2006

hi i am trying to import a fe files that have a certain portion of the file name tat is the same.

Since i don't know the syntax in VB this makes it much more harder. I understand the logic, but writing is a chore.

can someone please look at this code and tell me what i am missing?
Code:Private Sub upload_Click()Dim strfile As Stringwirds = "D:RLConverterupload"strfile = Dir("wirds & *.csv")ChDir (wirds) Do While Len(strfile) > 0 If Mid(strfile, 1, 5) = Z56203 Then DoCmd.TransferText acImportDelim, "import spec", "data1", strfile, True Kill strfile strfile = Dir End If Loop End Sub

View 1 Replies View Related

Modules & VBA :: Deleting File Based On Date

Feb 12, 2014

So I currently have a module which automatically backs up my database to a separate file everyday on open of my database.

If is it possible to create a function which will allow me to delete these backups after a certain amount of days (eg 30 days). They all have different file names based on the time that the backup was created but have the same extension of "*.accdb". I ask because it stores EVERY backup so the storage space required will soon build up unless manually deleting the files which is not what my client will want,

I have tried using the "Kill(pathname)" function but have had no luck yet!

If possible, I would like this to be done automatically either on open or on close of my database.

Would this be done with a module?

View 1 Replies View Related

Open Saved File Based On Form Textboxes

Feb 1, 2006

Hello all,

Do you have a code that would open a saved file based on the contents of 2 textboxes on my form.

The saved files would be in:
C:Documents and Settings100310257Desktop

I have a button on the form, but need a code that would pull up the savedfile using the name in textbox1 and the name in textbox2, like this:
textbox1-textbox2. e.g. textbox1 = Novo Group. textbox2 = AA
There is a saved file in the path above, Novo Group-AA

Thanks for helping!

View 4 Replies View Related

Modules & VBA :: Opening File Based On Directory Location

Feb 15, 2015

When creating an incoming inspection database, I want to set it up so when I am in a form to add data, I want to have a command button where I can pull up the associated CAD drawing based on part number and drawing number. I would like to enter the directory location into a table so I could change it if I need to.

So basically I want to go something like this:

.tblDirctoryLocation
txtDirectory

.tblProduct
txtProductName
txtProductNumber
txtProductCADNo

These tables would not be related to one another as there will only be one record for the directory location and multiple records for products.

Somehow I want the db to assemble file location like this:

“EQuality_DirectoryIncoming” & “ProductNumber_” & “CADDrawing” & “.pdf”
End result: EQuality_DirectoryIncomingProductNumber_CADDraw ing.pdf

Is there any way this could be done?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved