Modules & VBA :: How To Filter Imported Records
Jun 3, 2015
I am importing about 1.5 million records from a .txt file into an Access database. I have the code that splits the records and adds them to the database. However, the database has a 'specialty' table that lists certain specialty codes. The incoming record has a field that is a specialty code. Any way to search the 'specialty' table for the code that is coming in on the record to be added, and then, if the record 'code' is not found, skip it, and if it is found, add it to the database. There are about 70 codes in the 'specialty' table.
View Replies
ADVERTISEMENT
Feb 10, 2014
I'm trying to hash two scripts I've found into 1 functioning filter, however I'm still relatively new to vba and can't figure out how to get this working.
I'm trying to use Allen Browne's Search Criteria:
with another snippete of code I found here:
Code:
'Purpose: This module illustrates how to create a search form, _
where the user can enter as many or few criteria as they wish, _
and results are shown one per line.
[Code]....
It's the date part I'm having trouble with, the rest of the search criteria work fine without the date, but I can't get it working when I try to modify and merge the date sections of each code.
Also I'm using a listbox for the "Yesterday";"Last 4 days";"Last 9 days" and not a combo box.
View 2 Replies
View Related
Sep 12, 2014
vba code for filtering the records of the form made out of a table. The form contains combos that contain the lists of unique records in several colomns of the source tale. So i need to filter the records of the form using the combination of the values of that combos. But can't even apply a single filter using this code:
Me.Filter = "Business = " & list59.Value
Me.FilterOn = True
"Business" is the name of the column in the source table, and the "list59" is the name of the combo which contains the unique records of the "Business"
the code takes place as a button "apply" is pressed
View 4 Replies
View Related
Oct 1, 2014
I am using combo box to filter records from a form. The data source for the form is from a query.
I use the combo box within the query to filter the data, I would like the form to refresh/ run the query again every time a new selection is made from the combo box.
View 2 Replies
View Related
Jul 16, 2014
I am trying to set a date filter that filters between two dates (Start/End) after I have selected filters from other combo boxes. This is what I Have so far and is a bit of a mess.
I have two text boxes - txtStartDate and txtEndDate
If Nz(Me.txtStartDate.Text) = "" Then
Me.Form.Filter = ""
Me.FilterOn = False
ElseIf Me.cboCity.ListIndex <> -1 Then
Me.Form.Filter = Me.Form.Filter = "NextCallDate
[Code] .....
So the idea is I will filter all records by my name then by status then by city and then by start date and end date.
View 2 Replies
View Related
Jul 30, 2013
I have a scenario that I tried on an Excel forum but didn't have much luck. Conditions for my assignment:
1) Long description short, I have to randomly select 2 records for each client each month.
2) The catch is that these records have to be different states until the process is done for all 50 states, so the process isn't done for 4 years and 2 months (50 months).
I attached a sample database.
View 3 Replies
View Related
Jun 6, 2007
Hi
I have a table that I am importing from FOXPRO DATABASE. When someone goes into the system and deletes a record, Foxpro does not really delete the records but marks it for deletion. There is no field to query on that I can see in the table, but when I go to Foxpro the record is "marked" for deletion.
Anyway to exclude these records when doing a query or importing the table?
Thanks
View 1 Replies
View Related
Jan 10, 2008
Hi all,
I have the following issue:
In my job we work with several raw data .txt files exported from Oracle ERP system. These data include information about: inventory, sales, backorders, purchase orders, forecasts, product line.
My goal is to put information from all of these imported txt files together to create an easy to use snapshot file.
The common field between al of these files is the item description. I have an issue where records are repeated for several fields every time the same item description is showed. For example for the same product description the january sales forecast QTY is repeated on several rows because there are several orders for this product description. Is there a way to make forecast QTY appear only once but keep the multiple orders and their information?
Example of current result
http://img178.imageshack.us/img178/5659/currentsu7.th.png (http://img178.imageshack.us/my.php?image=currentsu7.png)
to achieve result
http://img179.imageshack.us/img179/5100/toachievefk4.th.png (http://img179.imageshack.us/my.php?image=toachievefk4.png)
thank you for any comments
View 4 Replies
View Related
Jan 15, 2014
No records are being imported. I am getting a validation rule violation but I don't have any rules. The forename and surname are straightforward text boxes and the ID is an autonumber.
Code:
INSERT INTO table_candidate ( Cand_ID, Cand_forename, Cand_surname )
SELECT candidates.Cand_ID, candidates.Cand_forename, candidates.Cand_surname
FROM candidates;
1. Is your append query trying to assign values to the primary key field? Could that be the source of the duplicate?
Yes but there are no duplicates
2. Do you have any other fields that are "Indexed: No Duplicates"? Any compound indexes?
No
3. Is the query assigning values to a field that is a foreign key to another table? Is it possible that these values do not match the values in that other table?
No. All child tables are empty.
4. Is there a validation rule on the table itself?
What this means.
5. Does the query attempt to assign a string with no characters in it (as opposed to a Null value) to a text field that has its Allow Zero Length property set to No?
Both fields are text and all records contain information
Required = No
Zero length = Yes
6. Is there a Default Value in one of the fields that is NOT being assigned by the query? For example, a foreign key with zero as the Default Value?
No
7. Is there data that is outside the range a field can accept, e.g. an integer larger than 32767, or a Null to a Yes/No field?
The length of the text in each record is not greater than the set limit
8. Is one of the query fields arriving in a format that Access is not understanding, e.g. for a date, or for a currency?
No
View 8 Replies
View Related
Mar 1, 2012
Access Database 2010 is used to capture progress on accounts. We are able to perform remedies on multiple accounts in the field and would like to update the records in Access all at once (by batch) rather than one-by-one.
I would like to export specific records from Access into Excel, make the updates to the records in Excel, then import the changes back into Access. I am looking for the updated Excel spreadsheet to overwrite the existing data in Access for that particular record.
View 5 Replies
View Related
Sep 11, 2013
Custom filter I'm trying to set up. I have a data full of records with multiple columns and a form linked up to it. The form has a search box that works as well as navigation buttons.
Currently I am trying to get a filter to work. The filter will use up to five combo foxes to narrow down the fields, then return the filtered records after clicking a button. The button itself is where my code is. My issue now though is I cannot get the filter to work if I try to use more than one combo box.
The code I have at the moment is as follows:
'------------------------------------------------------------
' cmdApplyFilter_Click
'
'------------------------------------------------------------
Private Sub cmdApplyFilter_Click()
Me.Filter = "[Asset Group] = " & Me.cmbFilter1 & "" And "[Location] = " & Me.cmbFilter4 & ""
Me.FilterOn = True
End SubThe current error I am getting is Error 13: Type mismatch. I also know that the And might be the cause but I cannot change it to AND: it keeps changing back.
View 9 Replies
View Related
Nov 7, 2013
I am trying to provide the user a custom search feature. They want to enter a keyword or phrase and search 3 memo fields and filter the form base on the records found. they also want to be able to search the whole phrase or any part of the phrase.
I have a like expression for any part of the phrase but I when I set it up for whole phrase it doesn't work. Even if I run a simple query and use
For example: There's an acronym the user is looking for : ACA
If I set my query up like this: [field1] like "*ACA*" or [field2] like "*ACA*" or [field3] like "*ACA*"
it not only finds records with that acronym but it also finds records where that combination is found in a word, for instance vacate.
How can I set up my query to find the whole word?
View 3 Replies
View Related
Sep 23, 2014
I am importing .txt files into Access table via VBA code (i.e., not via Saved Import Spec). Is there a way to trap the error if a particular field does not get imported due to incorrect format? When you import via Saved Import Spec and there are errors in formatting, Access generates an 'ImportErrors' table, which tells you which fields could not be updated.
Is there a way to generate a similar 'ImportErrors' table with VBA error checking?
View 4 Replies
View Related
May 26, 2015
I'm currently looking to change the ending of all filepaths of imported files whenever I run the import. I want to change the path ending from .txt to .doc. So basically, the link/path to the file should be got, the ending changed from .txt to .doc and then the path (with the .doc ending) put into the table.
Code:
Private Sub bimportinternal_Click()
On Error Resume Next
strFolderPath = "S:Foo reportsSearchable"
strFolderPathSave = "S:Foo reportsSearchableArchiveword" & objF1.Name
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files
[code]...
View 4 Replies
View Related
Nov 26, 2012
How can I create a "Filter Button" on a form and filter my records? I create a textbox on a form and a filter button on the right. Then I click the filter futton, the filter function will search/match the content in the box through the datasheet. And then the results of the filtering will be pop up on the split form datasheet.
View 3 Replies
View Related
Oct 24, 2013
I want to filter my subform data, to only show records where field A is a higher value than field B.
Code:
Me.MySubform.Form.Filter = "A > B"
Me.MySubform.Form.FilterOn = True
This way it doesn't find field B.
Code:
Me.MySubform.Form.Filter = "A > " & MySubform.Form!B
Me.MySubform.Form.FilterOn = True
This way it seems to filter all record to the field B value of the first record.
View 5 Replies
View Related
Jul 17, 2015
I have the following piece of code for importing the .csv files from a selected folder and then renaming them into 'imported & filename' if succesful and 'failed & filename' if import failes for whatever reason (bad formating, etc.).
The problem is that the first time it encounters a 'bad' file after another, instead of going again to the Error handler, the command
Code:
DoCmd.TransferText acImportDelim, , "Activitate", filename, True
gives me the default MS Access error, namely 2391. I would like it to go the the Error_handler again ang follow the course of actions and rename the files into 'failed' and 'imported'.
Here's the code:
Sub ImportActivitate()
Dim strFile As String 'Filename's
Dim strFileList() As String ' File Array
Dim intFile As Integer 'Number of files
Dim filename As String, sFullName As String, sFilename As String
DoCmd.SetWarnings False
[code]....
Also, how can I make the routine stop cycling through the files after I renamed them all?
View 3 Replies
View Related
Dec 9, 2005
Hi, I have this table with job numbers stored by days of the week e.g. mon job, tue job, wed job etc. You can have the same job number everyday of the week or different ones. I have to get a report that shows JOB # ??? and all of the fields associated with it. I'm trying with a query, but can't get it! How can I accomplish this?
View 10 Replies
View Related
Feb 21, 2006
I need help.
I have set a query to carry out a list of companies from a table based on two criteria.
However, the same company could appear two times in the results .
Is there a way to requery results to filter records that appears two times and leave them only one time in the final list?
Thanks in advance
View 2 Replies
View Related
Nov 6, 2004
I Have a table that was not made in a proper relational way, there many records with the same name
Ie
ID Name
1 Smith
2 Jones
3 Smith
4 Adams
5 Jones
6 Smith
Smith is listed 3 times Jones listed 2 and Adams =1
For a mail out report I only want one for each person.
To get a result as below
Smith
Jones
Adams
If possible I would like to know how to do this with a normal recordset and with SQL
If you can help it would be very much appreciated
jabez
View 2 Replies
View Related
Aug 30, 2005
i have a table with 20,000 records. one column includes names. another column includes multiple dates the person visited a doctor, and another column includes dates the person visited a hospital. i am only interested in the last date the person visited a doctor and the last date the person visited a hospital (if they had any visits to the doctor or hospital). I am also interested in members who have had no visits to the doctor or hospital. any suggestions on how i can filter the data to only return these records? thanks
Name Date Visited DoctorDate Visited Hospital
Nigel01/01/1980012/31/1995
05/20/199906/06/2005
03//02/200405/06/2004
For example, I only want to return
Nigel, 03/02/2004 (Date Visited Doctor), and 06/06/2005 (Date Visited Hospital). thank you. i really appreciate any help you can offer.
View 2 Replies
View Related
May 16, 2006
setting up a database for my department at work and have a few questions
a.) i need to set my form to have a search or filter based on user criteria
1.) users need to be able to select themselves from a drop down list
that's based on a table in order to filter their cases from everyone
elses
2.) instead of using the drop down menu i'd like them to be able to enter
an ID number that pulls up a specific record rather than first selecting
all their cases and then navigating those until they come across the
case they're looking for
View 7 Replies
View Related
Nov 17, 2006
Delete Reference: Trying combinations I found one that works!!!
The second line should read: Me.Filter = "AdminDateCompleted Is Null"
Hi,
I want to filter records from a form with a button to isolate all incompleted tasks.
Incomplete tasks are identified with an empty field [AdminDateCompleted] which is formatted as a 'Medium Date'.
I am having trouble with the following code (created after several hours searching this forum and experimenting).
First click returns all the records allegedly filtered, 2nd click turns the filter off and the third click returns 1 (Filtered) blank form.
If Form.FilterOn = False Then
Me.Filter = IsNull(Me.AdminDateCompleted)
Me.FilterOn = True
cmdFilterActive.Caption = "Remove filter"
Else
Me.FilterOn = False
cmdFilterActive.Caption = "Filter"
End If
I would appreciate any advice please.
Allan
View 4 Replies
View Related
Dec 9, 2004
I want to write a code that upon opening the report it looks at the sumofamount field and only shows me the records that the sum is greater than $50. It currently shows me all the records and it they are less than $50, it comes up as a negative. I only want to see the one which have amounts over $50. Would I write this in code and have an even procedure upon opening of the report? I should also add that I have the amounts listed out the bottom of the page, so I can't just go into the query and put >50.
Thanks.
="" & [first name] & ", for the month of " & Format([date],"mmmm") & " your DHL bill came to " & Format([sumofamount],"$#,##0.00") & ", the company allows you $50.00 per month, this leaves you with an overage of " & Format([overage],"$#,##0.00") & ". This amount will be deducted from future commissions. Below is an itemized list of your charges. If you have any questions, please contact me at extension 4491."
View 1 Replies
View Related
Apr 27, 2008
In my database I want to create a report that identifies if some is available or not every day. I have a general table(Tbl_General) with names and contact info, in a second table(Tbl_Recall) I have 3 fields Location StartDate EndDate. I want to show every person from the general table and only records from the Recall table where the start date is before today and the end date is after today.
The queries I write exclude some records if I limit the dates and the reports I build show all records in the recall table.
View 9 Replies
View Related
Jun 23, 2014
In access, I have the following: 2 sub forms located in an overall form. I would like when I select a record in the first subform, that the second subform would automatically filter for all records that contain the same ID Value.
View 1 Replies
View Related
