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

Modules & VBA :: Code To Add Data In The Listbox That Meets Certain Criteria?

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

One Field Meets Criteria Of Another?!?!

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

Automatically Delete Data From Certain Fields

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

Validation Condition Where At Least One Of Four Fields Meets Criteria

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

Select Those In A Column That Meets Character Count Criteria

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

Queries :: Identify Subsequent Records Where Original Record Meets A Criteria

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

Reports :: Showing All Detail Records If One Record In Query Meets Criteria

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

Queries :: Insert Text From Textbox On A Form When Combo Box Meets Criteria?

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

General :: Delete Query With Date Criteria

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

Modules & VBA :: Pull Data From A Table That Meets Number Of Conditions?

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

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

General :: Automatically Update Data Inside Database

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

General :: Automatically Change Data Paste To Include First Eight Characters

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

General :: Connecting Access Database To Website To Import Data Automatically

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

Automatically Delete Records

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

Delete Records Automatically Frm Table

Jul 26, 2006

How do you have Access delete records automatically from a temp table?

View 1 Replies View Related

Delete Field Contents Automatically

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

Tables :: Delete Item Entered In One Table From Another Automatically

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

Modules & VBA :: How To Automatically Delete Information In A Table With A Command

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

General :: Query Criteria - Data In Fields Are Numbers

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

General :: Possible To Have Data From Linked Table Automatically Update Into Existing Table?

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

General :: Input Data In Lower Case And Automatically Change First Letter Of Word To Upper Case

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

General :: ELookup With Multiple Criteria - Data Type Mismatch

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

General :: Error 3464 / Data Type Mismatch In Criteria Expression

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

Automatically Change A Criteria

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







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