General :: Automatically Delete Data That Meets Criteria?
Mar 15, 2013
I'm an amateur (is there such a thing as having databases as a hobby?) with little training. I volunteered to organize a growing volunteer group I'm a part of by making a simple database for the volunteer office staff. One of the things I want it to do is make name tags for our weekly meetings. I don't want to print one for each name on our list because the list is over 400 names long with only around 35 regular attendees. I made two tables--one is the MakeNameTag table that lists regular attendees (it is a lookup field on the Names table).
The other is the Absences table. It has two fields; DateOfAbsence and MakeNameTag. If someone misses a meeting (does not pick up their name tag), their name is added to the Absences table. A query then filters the table for dates in the last 28 days. If their names shows up four times in the last 28 days I want for their name to be deleted from the MakeNameTag table. How to do that automatically? Our office volunteers have minimal PC skills, so the solution needs to be very user-friendly.
View Replies
ADVERTISEMENT
Jul 17, 2014
I have a Access table that stores BatchNo,Scandate,NewBatchNo . As I can't attach Access database here so I have exported data from Access to Excel in Sheet1 to show how the data is stored in Access table.
Now I have to write code in VBA that will check the last NewBatchNo in the table . In the attached workbook its 194389. Now vba code should check the NewBatchNo which are blank before 194389 . Now in the records where NewBatchNo is blank , it should add the corresponding BatchNo,ScanDate in the listbox1 in form1.
e.g Sheet2 in the attached workbook stores Bathcno and Scandate of those records where NewBatchNo is null and before the Last used NewBatchNo which is 194389 in our example.
View 4 Replies
View Related
Jan 16, 2007
Hi,
I have one field, for this example, FieldA and another FieldB.
I would like the following:
If fieldA =1 then FieldB must = 1 or 3 or 4. If not then I would like a message box saying it needs to check. Otherwise I don't want anything to happen.
How can I do this? and where do I put any expressions?
thanks
Sue
View 7 Replies
View Related
May 23, 2005
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 Related
Jul 29, 2015
Windows XP
MS Access 2007
VBA experience is very limited
So in my forms 'BeforeUpdate' property I want to validate whether a condition is met. The condition is whether 'any one of 4 fields contains something (text or number)'. I have written this code for validating a single condition and it alerts if the field is left blank. This code returns a warning message when the field is blank.
If Me.[txtObjective] & "" = "" Then
MsgBox "A 'Problem Objective' is required, please complete this field.", vbOKOnly
Me.[txtObjective].SetFocus
Cancel = True
[code]...
how to write the SetFocus part. And I dont know if this is correct anyway.
View 6 Replies
View Related
Nov 4, 2005
Hey, I know this is probably a simple thing to do, but I couldn't find it anywhere on the web so hopefully someone here can help.
I have a column of UPC codes which are a bunch of #'s. I need to find the ones that have more than or less than 12 characters.
Thanks
View 2 Replies
View Related
Aug 8, 2014
I have a table [PickData] in a WMS (Warehouse Management System) database, that records details of each item picked. The key fields are;
[Movement] - a unique ID for a collection of items to be picked.
[ToAssignRef] - the order ID
[Product] - the product!
[Picked] - the date/time the item was scanned
[Pick Actioned] - the date/time the [ToAssignRef] was completed
[Reason] - A code to indicate why an item could not be picked - AKA F3'd
An operative would be allocated a movement, connected to the [ToAssign Ref], containing a number of products to pick. If an item can not be picked for some reason the operative presses F3 and selects a reason (no stock, damaged etc). These F3'd items (other stock allowing) will later be picked on a different [Movement]. I need a query to identify the subsequent [Movements] and the associated fields following the occurance of an F3'd [ToAssignRef] & [Product].
I have a query, but it runs very slowly (perhaps due to the DB size currently 780K records). Is this the right approach, is there a better (faster) way to do this?
Code:
SELECT PickData.ToAssignRef, PickData.Product, PickData.Picked
FROM PickData
GROUP BY PickData.ToAssignRef, PickData.Product, PickData.Picked
HAVING (((DCount("[Movement]","[PickData]","([ToAssignRef]='" & [ToAssignRef] & "') AND ([Product]='" & [Product] & "')"))<>0));
View 2 Replies
View Related
Mar 5, 2013
I am trying to create a report which basically includes the following:
Company, Wages, Contribution.
Each company reports wages for each employee every month. Then they also contribute to a general fund based on a percentage of the wages. For instance:
Company---Employee---Wages---Contribution
CompanyA---EmployeeA---$4000---$40
CompanyA---EmployeeB---$3800---$38
CompanyA---EmployeeC---$3800---$38
CompanyB---EmployeeA---$4200---$42
CompanyB---EmployeeB---$4200---$42
...and so on.
Each employee is required to contribute, in this example, 1% of gross wages to the general fund.
On occasion, the company does not pay in the required 1% of gross, say, for CompanyA EmployeeA, they only paid in $35.
Here is what I need to do. If any contribution amount for any employee is incorrect, I want to display all the records for that company, not just the incorrect ones. The report is grouped by Company, and may contain dozens of companies.
I am already passing a number of criteria to the report using a filter, including the date range and other fields which are informational.
View 6 Replies
View Related
Jul 12, 2013
Is it possible to insert text from a textbox on a form (Data) when a combobox on (Data) meets a criteria?
Example: Test: IIf([Results]="Positive" text207)
So if the Results combobox is Positive then the text from Text207 is inserted.
View 9 Replies
View Related
Nov 23, 2014
I have 2tables,table1 id,name, table2 id,name,date.
How can i write a delete query that will delete everything in table2,if the data date is today?
View 2 Replies
View Related
Jul 3, 2014
I have a module that is in Excel that is connecting to a back end database. I am trying to pull data from a table that meets a number of conditions. With the following statement, it is not returning any records,even though I know there are records that meet all of the conditions. I suspect the problem is with the last condition. In that condition I am trying to say that pull in records where it has been at least 14 days since the last review.
Code:
strSQL = "SELECT tblsojrol_oc.* FROM tblsojrol_oc WHERE tblsojrol_oc.[Status] = 'Pending' AND tblsojrol_oc.[1st Review Date] IS NOT NULL AND " _
& "tblsojrol_oc.[3rd Review Date] IS NULL AND (DateADD(Day,14,tblsojrol_oc.[2nd Review Date])) >= #" & dt & "#;"
objRs.Open strSQL, objConn, adLockReadOnly
View 14 Replies
View Related
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
Jun 30, 2014
I have data in all sorts of tables and databases that are linked together through different systems. I'd like to update some of the information automatically in the evenings or in the middle of the night without needing someone to push a button. Is there a way to do this?
View 9 Replies
View Related
Dec 8, 2012
I have a form with one control field that I paste a 17 digit alpha numeric value into. I have a command button that then runs a query based on the pasted value.
Is there a way to automatically change the data I paste to include the first eight characters, replace the 10th and 12th position with wild cards, and delete the remaining 6 characters ?
Example:
Copy: 1GTEM14M0WZ526688
View 3 Replies
View Related
Jun 30, 2015
I just created a database and need to connect it to the data source. The data comes from a http website (intranet from work). When I open the link using firefox, I can view the website with the data in it, but when I open it from Internet Explorer, I get a save as pop-up message to save a csv file which contains all the data. The extension of the http website ends with csv. So it is something like http (slash slash...) Intranetname/referral_dbase.csv
Currently, I am opening the file using firefox, copying all the data manually, and pasting it in a text file using notepad. After that, I import the file into access. The delimiter of the data is this symbol: |
I am trying to find a way to link my database to the website where the data is located so that I can skip the manual process of opening the website and copying the data and saving it into a text file and then importing that file into access. I was thinking to have like a form in access with a bottom that will automatically import that data from this link and paste it into a table in access using the delimiter symbol mentioned above.
Is this too complicated? Is it even possible in access 2010?
View 1 Replies
View Related
Mar 28, 2006
I have 2 forms:
one called basic the other called advance
how can i automatically delete records from the basic table, when students are advance.
thanks 4 helping
View 1 Replies
View Related
Jul 26, 2006
How do you have Access delete records automatically from a temp table?
View 1 Replies
View Related
Oct 14, 2004
Here is one that I could use some help with.
I have an Updates field in all of my tables. It, of course, holds my audit trail. Im tryiing to figure out a way that I could run a single command and clear the contents of all the Updates fields on all the tables in my db. Ideally, I'd like to auto generate a report for all the update fields on all the tables, then do a purge.
Any ideas?
View 4 Replies
View Related
Dec 24, 2013
Im trying to make a basic customer database and pos system. The part iam having trouble with is, I have two tables as follows:
Inventory: orders(invoices):
productId ProductId
amount (in stock) amount(sold)
pricePerUnit CashTendered
I want the amount(in stock) sold in each order to be deducted from the total in the amount(sold) automatically.
View 1 Replies
View Related
Oct 30, 2013
my database just records end of day sales figures which are entered though a form, after they are entered the total for the day is archived. However the problem i have is that the i need some way of clearing the input table with a command in the form.
View 2 Replies
View Related
Nov 7, 2014
I have a query criteria where the data in the query field are numbers.
The following works as long as I have a value in the textbox otherwise I get an error message stating, this expression is typed incorrectly.
Code:
Like [FORMS]![FONMain]![txtTest15] Or [FORMS]![FONMain]![txtTest15] Is Null
Also tried
Code:
[FORMS]![FONMain]![txtTest15] Or [FORMS]![FONMain]![txtTest15] Is Null
Yet I use the following script for another column which has identical data and it works fine either way and I can use it in the other column and it works with or without data in the textbox
Code:
Like [Forms]![FONMain]![txtFact1] Or [Forms]![FONMain]![txtFact2]
Or [Forms]![FONMain]![txtFact3] Or [Forms]![FONMain]![txtFact4] Or [Forms]![FONMain]![txtFact5]
Or [Forms]![FONMain]![txtFact6] Or [Forms]![FONMain]![txtFact7] Or [Forms]![FONMain]![txtFact8]
[Code] .....
View 5 Replies
View Related
Aug 17, 2012
I have one DB that is used for creating/storing customer ID's, and another DB that is used for creating/storing job information for customers.I have linked the table from the customer DB to the job DB.
There is a table in the job database that holds customer name and ID, and some VBA that generates unique job codes.
Is it possible to have the data from the linked table automatically update into the existing table?
View 1 Replies
View Related
Mar 16, 2013
Is there any way of making data that is inputted in lower case to automatically change to the first letter of each word being a capital ...
View 4 Replies
View Related
Apr 17, 2013
I'm trying to use the following expression:
=IIf(ELookUp("EventDate","qsfrmEvents","[EventID] = 8 And [fkClaimID] = ' & [Forms].[frmClaim].[txtClaimID]'")>0,True,False)
But I'm getting a data type mismatch. txtClaimID is numeric, but if I take out the single quotes I get a syntax error.
View 2 Replies
View Related
Jun 23, 2012
"Data type mismatch in criteria expression" Error 3464
I get this on the <DoCmd.OpenForm "frmWrittenStatements"> in my code.
How on earth are any criteria attached to this?
View 2 Replies
View Related
Nov 3, 2005
Hello to all the xperts out there,
I have a query with this criteria for Date field:
Where (([tblList].Date)>=#11/1/2005# And ([tblList].Date)<#12/1/2005#))
Is there any quick way, besides creating a form and run the query based on it, to have this criteria updated itself when it's a new month? I've gotten tired of changing it every month.
Thanks in advance
View 2 Replies
View Related