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 Replies I have a table with the following 5 fields. (Service Type), (Valve Name),(Size),(Rating),(Description).
I want to do two thing:
First: I want to select the required information from the first 4 fields using combo boxes and get the last field (description) based on the selected 4 fields. In other words, i want the record to be filtered using first 4 fields to give me the last field info.
Second: I want to store the filtered record (all 5 fields) in another table.
I am having trouble with a datasheet its ran from a macro button using BrowseTo command.
I am having trouble with the where condition; I would like to show records where the [FittingDate] is blank...
I have tried isnull() and [FittingDate]=""
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.
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 RelatedHi, 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 RelatedI 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
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
I have at present 2 combo box's that filter, now....... the 1st combo box is a drop down value list that will filter the OPOwner (persons name) for example there are 3000 records and 5 OPOwners, I can for now filter my name in cboOPOwner combo box.
The 2nd box (drop down list) is Status which would be "New";"Open";Pipeline";"Lead" ect...
If I filter my name it brings up all records related to my name and the when I want to filter status it will bring up all records relating to say 'Pipeline' however it will bring everyone's ;Pipeline and not just mine.
What I want is to filter my name in the OPOwner combo box and once all records are filtered in my name then in the status combo box filter what ever I want to look at. example what I want to filter - 'Filter Neil' then 'Filter Pipeline, or filter Lead or New'.
This is the code I have in both combo's....
Private Sub cboOPOwner_AfterUpdate()
If Nz(Me.cboOPOwner.Text) = "" Then
Me.Form.Filter = ""
Me.FilterOn = False
ElseIf Me.cboOPOwner.ListIndex <> -1 Then
Me.Form.Filter = "[OPOwner] = '" & _
Replace(Me.cboOPOwner.Text, "'", "''") & "'"
Me.FilterOn = True
[Code] ....
I'm trying to set a filter with a command button. I've tried to do it like the examples I've read on numerous sites, but I can't seem to get the filter set. The code that seems most like the examples to me is:
Me.Filter = "[chkPaid] = 0"
Me.FilterOn = True
When I run this I get a box telling me to enter a parameter value for chkPaid. chkPaid is definitely the correct name for the checkbox control on the form. I've tried many variations with and without the square brackets, but as long as I have [chkPaid] inside of quotes I get the Enter Parameter box. If I leave the quotes off I get no parameter value request, but it doesn't set the filter either.
So I have a Form with 3 fields. 2 Date fields (Beginning & Ending) as well as a combo box (CName). There is a button (Filter).I want to fill in both dates and select from the dropdown box. Onceeach field has a value then it will send a msgbox saying there were "# of records" between the 2 dates by CName
View 14 Replies View RelatedI have a database that opens into a continuous form ... which has records and a user name in each, which is taken from a table "ASSIGNED". At the foot of the continuous form I put a filter button to show only the selected user records.That when opening the form, Access asks why I want to filter user (do this only once) ... So each user sees only their records.It is clear that the "Front End" is used in a network and is linked to another database with multiple related tables.
View 1 Replies View Relatedi 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.
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
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
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."
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.
I have a query based report Linked to a form. That is, in my form, i have a combo box. Selecting this combo box pulls out a certain field values in list box in the same form. (Cascading Combo/ List Boxes).
So after i select a certain value in combo box, it shows a certain field values in the list box. Now, i want to open a report and view only the records that has the selected combo box and list box values.
Note: I have seen a guy made an access report based on a query. He made a form with a combo box and a list box.
The report is based on the combo box value selected by the user. Once, the user selected a value in the combo box, the list box updates automatically. ( I have done till here successfully). Then he used a toggle button on the same form.
This toggle button opens the report with the combo box value as the criteria but filters the report based on the field value in the list box.
How to place a toggle button with a filter command to open a query based report?
I have a form that has many fields with the same part number but with a different PO number
Example
part number po number
1 3
1 10
1 15
What I want to do is select the part number and the po number together
Say part number I po number 15
At present my combo box only will select one of these IE THE part number ...
I need to filter a subform based on the values on the mainform.
Criteria are Multiple. The idea is to show the user the records that are already existing in the table.
First two criteria works, stuck with the third one
I.Criteria - Creative(text type)
Private Sub Creative_AfterUpdate()
Me.frmCommercialWithDetails_subform.Form.Filter = _
"Creative ='" & Creative & "'"
Me.frmCommercialWithDetails_subform.Form.FilterOn = True
End Sub
[Code] ....
Also, how to split the code into two lines
"Creative ='" & Creative & "' And Act_ID = " & Act_ID
when broken into:
"Creative ='" & Creative & "' _
And Act_ID = " & Act_ID
is automatically saved as:
"Creative ='" & Creative & "'" _
And Act_ID = " & Act_ID " and this generates an error on execution.
I have a function which I want to return the value of a field.
Public Function fieldValue(tblName As String, fldName As String)
Dim drs As Recordset
Set db = CurrentDb
Set drs = db.OpenRecordset(tblName)
fieldValue = drs.Fields(fldName).Value
drs.Close
Set drs = Nothing
End Function
I need to return the value obtained, for a specified User identified with a numeric variable.What is the best way forward? - Some sort of filter or DLookup and how to code this?
I am trying to build an expression in a query that will filter specific numbers when the value is entered. I would like the value box to read something like "Please enter the file number" and when entered, see example of file # in brackets (001-200-001), it will show those records with that file number only. In addition, because I will be printing it from a report, the filtered file numbers should also show up in the form.
Thanks.
Hello,
I need to navigate through all records for a table where a <field> = a certain "string". Ideally I would have a next record button that would know to ignore records that do not meet my criteria?
Simply put,
Next available record where <field> = "string"?
Is there anyway to do this?
Regards,
Chris
Hi everyone
I use Access 2003 and saw Ken Higg's sample database to filter records in a form using a combobox. I managed to replicate his settings and wondered about an extension to the principle, but I don't know if it's possible.
In the sample, a value is selected using a combobox and all matching records are filtered so it's possible to scroll through them. Is it possible to have a second (or more) combobox on the form so it is possible to filter on one or another criterion? This would mean one combobox filling the other one and I realise that the one that Ken provided is unbound.
The reason that I'd like to do this is, for instance, if there are records with Surname, 1st, 2nd and 3rd lines of the Address and Postcode, I may wish to filter records by entering the Surname or Postcode. Each of these fields would have to be displayed in a combobox. As the sample is set up at present, only one of the Surname or Postcode has to be selected via the combobox and the other is a textbox.
It may be that what I'd like to do isn't possible - or someone may have come up with some other idea. I'd prefer to have the search/filtering on the form, rather than using the buttons on the Form View toolbar.
Thanks for your patience.
Juan
A form I created contains buttons for the user to restrict the display- showing records that are tasked to one specific employee.
So their choices are Employee a, employee b or all employees.
This is a process I use a lot :)
This last form opens with NO records displayed. When a button is clicked the records show correctly.
Any idea what to check?
Thanks,
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 9 Replies View Related