In design view, how do I filter on my data so that it only shows data that beings with "PI"? Yes I know I can do this by the main screen and say select text that beings with PI but I would like to know how to do it with sql.
View RepliesI am trying to filter data in a table using vba where I have a split form setup. My goal is to filter data where the user can input a character such as "a" for the "FirstName" field and have results from the table filtered with persons first name that starts with "a". Here is my code so far one of the text boxes.
Private Sub txtFirstName_DblClick(Cancel As Integer)
If Me.Filter = "" Then
'Compares the values that begin with the input values in txtFirstName
'text box from the table field name FirstName
Me.Filter = FirstName & " LIKE '" & txtFirstName & "*'"
[Code] .....
I get a error in the else statement and please note that I am linking this form to an sql server so I can not delete or modify existing data in the table.
I have a tabular form that lists all the records (address book). I'm trying to have a combo box filter the records by selecting a contact type (employee, supplier, customer). So far I have the contacts displaying in the combo box.I've tried to create a macro that begins "after update". Its an ApplyFilter macro.
Where Condition = [Contact Type]=[Contact Type Select]
[Contact Type Select] is the combo box.
When I do this, a popup box appears. When I type in the item I want, it works. I want this done automatically.
I would like to filter data from a table using a query (from an data input form). The objective is to output all results if input form field is empty and to output results higher or equal to the type in the field if field is not Null. The query code is as follows:
IIf(IsNull([Forms]![Form2]![MaxDiffInput]);[Maximum operational pressure (bar)];[Maximum operational pressure (bar)]>=[Forms]![Form2]![MaxDiffInput])
However, is not providing any result when the input field (MaxDiffInput) as a value.
There is a filter in Excel that is called begins with and I was wondering if there is anyway to use this in a query to remove only certain items, namely our equipment is labeled, AB000 or something similar where each piece of equipment in that category starts with the same two letters and then they get numbered accordingly. I'm trying to filter them so that I can tally all the usage time for a year.
View 2 Replies View RelatedAll,
Is it possible to filter parent records by child records (or subdata) without showing multiple instances of the parent data in the form/query?
Cheers,
Bobadopolis
Friends
I am using access to keep farming data of 300 farmers. I have a edit screen, where the farmer's name are in a drop down combo. After update, I want the consequent data field to be changed according to the farmer's name. I am using the following code in after update of the combo field farmer:
Private Sub farmer_AfterUpdate()
DoCmd.ApplyFilter , "Id = Forms!basedata_edit!farmer"
Me!farmer.Requery
Me!farmer.Enabled = True
End Sub
ID is the reference of the farmer which is stored in the master data. basedata_edit is the form name. My problem is, for the first selection, the filter is showing the proper data. But if I change farmer's name, the other fields are not changing.
What do I need to do?
Anshuman :confused:
I am trying to filter information in a table like you can filter information in columns in Excel. For example, if I have a table with data in it like dates and times and I wanted to filter to find all records with the date of 2/5/2008, how could I do that easily? Can it be a drop down box like in Excel. I'm making a program for someone, and I don't want for them to be able to change anything in the program so I have disabled viewing of some toolbars, besides, the filter button in the toolbar never does what I want it to do.
Thanks!
Hi there!
I am pretty new at this so please bear with me :confused: (like that isn't said a thousand times!). I am creating a small database to track task data (manpower tracking basically) where I have the following fields:
Customer
Functional Group
Task Status
Task Name
Task Description
WO #
Funded Hours
Notes
I would like to create a combo box where the user can select the customer (I have 124 records in total, 15 unique customer names) and all tasks relating to that customer are filtered directly in the Form by clicking a single button to do the filter.
I don't want to create dozens of tables and forms to flip through and such (users need it as simple as possible). I have created queries but the only way I have successfully filtered each customer is via buttons...but 15 buttons to filter each individual customer is a pain in the butt, so any help would be REALLY appreciated.
Please let me know if I haven't explained this clearly enough! :)
Thanks!
every month i compile a Customer Spend Report for my sales manager.
I currently do this by:
1) Taking my database into table view.
2) Setting the date to Asend.
3) Then copy all jobs from the first day of the month to the last day
eg: June 1st - 29th.
4) Copy the data
5) paste into excel
6) Set auto filter on excel
7) Copy & Paste each customers spend to an individual sheet.
This is very painful! Esp when i know that since all the data i need is in the database and there is a way to get my DB to do this for me.
I have created a query to pull the data out of the database.
Then made a report using the query as the "location of data"
Right i have the report now pulling out the data and showin how i want it.
It shows the groupin of jobs by "Customer Name"
I now want to set a filter to it so that i can tell it to give me the report for all customers for just one month.
EG: June 07.
That way i wil have the data shown as:
Customer:
Month: June07
Cost For Doin Job:
Total Charge Out:
Profit:
How do i do this??? I know it has to be done within the query but i cant work out how to set it.
If any one can help that would b great!!!
thanks in advance!
gary
Hi there,
I was wondering when I am filtering out data from a table, is there a way for me to filter out specific lines (as in, if I have a 100 line datasheet, can i choose to filter out lines 5, 10 and 20 out of the query)?
For instance:
Line Name Address Phone Number
1 A1 fjlafs 453453454
3 A2 fsdfsd 343534534
4 A3 gsdgsdg 354543534
5 A4 gsdgsdgsd 345345345
I want to make it so that lines 2 and 4 are NOT included in the query when I click "RUN".
Thanks in advance!
I have a subform in a form that displays all members of staffs Name and employee no. I am trying to find a way to search the subform on the main form it's on using textboxes to filter data, here is the code I have that searches the employee no.
Code:
Private Sub Command178_Click()
Dim dbs As Database
Dim qdf As QueryDef
Dim sqlstr As String
Dim sqlstrwhat As String
Set dbs = CurrentDb
[code]....
I do not get any errors and I haver used the same code structure for other subform filters but when I search for an existing employee no. the subform only displays a blank record with the employee no. 1?
I have large dataset(more then 15,00,000 rows) In one column I have Amount stored as text eg: $15,000.00 $ 12,000.00 $ 17,520.00
Data is imported from various small files and is updated regularly. Now I need to filter dataset based on this column criteria like > 15000, <= 5000
How can I configure query to filter data.For example data are:
Id
Detail
Value
Part
25252
Some text here
$17,294.00
1
[Code]...
I have set up a query that will pull data from table1. There are two fields in my query to which I will filter by entering certain basic criteria. In the criteria line of my query field, I have entered "800" to only return this type of data. If I run the query, it returns only those fields, which is exactly what I want. However, I also need an additional filter in another field. I have entered "4", to return only those data matches.
So, my entire query is based on returning only the data from table1 that matches the two criteria ("800" and "4"). Problem is the query will not return any data when I enter the "4". Any ideas why this would happen? There does appear to be an extra space in table 1 for the field containing "4". I have tried to set the criteria to match, but it still does not return any values.
Any advice greatly appreciated.
Thanks,
Todd
I want to append data to the FC_TEMP table with the condition that the time_period is same on FC_TEMP and Scrap_Sales_Forecast table.Somehow it asks me to type in a parameter for Time_Period on FC_TEMP.
Code:
'Append Scrap Sales forecast to actual FC_TEMP table
Public Sub Append_Scrap()
DoCmd.RunSQL "INSERT INTO [FC_TEMP] SELECT Scrap_Sales_Forecast.* FROM Scrap_Sales_Forecast " & _
" WHERE FC_TEMP.[Time_Period] = Scrap_Sales_Forecast.[Time_Period]"
End Sub
I have a sub form dragged into a main form that shows all existing diary entries. I have already got code for various different filters but I am struggling to wrap my head around this one. Just want the user to input two dates and then the sub form to show all the diary entries between those 2 dates. Or maybe a drop down box listing various lengths of time from today's date and showing entries within that time scale. Whatever is easiest tbh. Would I have to use date diff for this?
View 7 Replies View RelatedI have a (simplified) table with productnr., period (year and month) of sales, and sales (in currency). Now I want to know the contribution of the products that have their period of sales after a certain number. This will calculate the contribution to sales of new products. Simple stuff.. (i hope), but I was not able to do so...
I have managed to separate the table with multiple queries and I have managed to have now to queries that can show the sales of products that have been launched before the date and after the date, but combining the two tables is impossible for me..
First off I apoligise if this is a clear answer. I have looked on the internet for the last two days and can't seem to find this anywhere, either that or I am just entereing the search parameters in wrong :confused:
I have filtered selection in a form which I want to export to excel.
Simple enough ;) : Created a macro with the export to command. This dus everything I want to do.
Well not quite. :rolleyes:
How do I select the fields I want to export.Something like Select Id, name, adres from query soandso
Hopefully there is a simple solution to this. If there any existing posts. Could you post the link for me.
Thank your for your time,
Kind regards,
I have a form which is linked to a query. The main form which calls this form sets the filter for the query. This works fine and I can navigate all the filtered records.
The problem is when I go to add a new record the filter information goes away.
For backround this is DB for project information. The user clicks a button to view notes on the project. The notes are stored in a different table and the project_id is the common field which links the two together. I want this to function where the user can open the subform read the notes and add a new one if needed.
Hi all, I’m trying to filter the records a subform shows based on the employee number (in a combo box) that the user selects in the main form. At the moment im getting the following message when I select an employee number in the combo box: “Compile error, method or data member not found”:confused:
Ive searched the forums on here and tried to make use of the information other people have posted but im still having trouble getting this to work
Below is the code I have on the combo box of the main form:
Private Sub Cbo_Emp_Filter_AfterUpdate()
Dim strSQl As String
strSQl = " Select * from DATA-EMPLOYEE_MASTER where DATA-EMPLOYEE_MASTER.EMPLOYEE_NUMBER=" & Form![SCREEN-ABSENCE_TRACKING_MAIN]![EMPLOYEE_NUMBER]
Me.SUBFORM_ABSENCE_TRACKING.RecordSource = strSQl
Me.SUBFORM_ABSENCE_TRACKING.Requery
End Sub
Ive also posted the database if any one is willing to have a look for me (ive removed irrelevant tables/forms/reports). Im using access 97
Thanks
Jim
I would like to filter a data from a table using a combo box in a form.
The field I would like to filter is called ManufacturersID in MainTable and consist of text and wildcard characters [e.g., Teledyne O&G (ODI), TE Connectivity (Deutsch), etc...], so are the combo strings which are extracted from the table ManufacturersTable. It is not filtering anything. I suspect that the wildcards and spaces are the problem. I also tried to get the combo string to a text box in the form and use it in the query with no success. Additionally, I also tried Like [Forms]![FormName]![textbox] in query criteria but still not filtering...
How can I overcome this?
I use Me.Filter & Me.Filteron to filter the data from the table to display in the form, i used this in On Open event & This is working fine.
But in the filtered form when the user right click and filter some value and unfilter the search (in the bottom of the form next to search (Navigation Button) ) then this shows all the data from the table i.e its not taking the on open filter condition.
problem when I am trying to use Form to filter data from a cross tab query though I have already defined the parameters. The SQL is as under :
//Code start
PARAMETERS [FORM]![START_MONTH] DateTime, [FORM]![END_MONTH] DateTime;
TRANSFORM Round(Sum(CLng([A_GAS_m3]+[NA_GAS_m3])/1000000),3) AS GAS_MMSCM
SELECT maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block
FROM maindata RIGHT JOIN PRODUCTION ON maindata.field_block = PRODUCTION.FIELD_BLOCK
WHERE (((maindata.field_block)<>"Panna" And (maindata.field_block)<>"Mukta" And (maindata.field_block)<>"CB-ONN-2000/2(NSA)" And (maindata.field_block)<>"CB-ONN-2000/2(BHEEMA)") AND ((PRODUCTION.MONTH) Between Format([FORM]![START_MONTH],"dd-mm-yyyy") And Format([FORM]![END_MONTH],"dd-mm-yyyy")))
GROUP BY maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block
ORDER BY maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block
PIVOT PRODUCTION.ACTIVITY;
//Code End
When I save it prompts for the Start and End Month and When I run the query it pops up the form twice...
I have a form in access where i need to select a record between two different dates. For example i have a "valid_from" and "valid_to" field. I have an unbound text box with short date format and calendar control inserted for users to select a date. This is named "drpdate".
I have a bunch of other filters also in the same form. Now my issue is that i have not been able to figure out how to put in a SQL statement which would give me the data which is between the "Valid_from" and "valid_to" fields based on the date selected in the unbound text box.
If the selected date does not meet the criteria, then it needs to be give the results from another table (which i have already done).
While applying filter in forms, i have 6 fields in the form and i want to display the data even one of the filed is with 0.
I used the below code, but this one display the data where all the fields are 0. I tried or in place of and but still its not working.
Private Sub Form_Open(Cancel As Integer)
Me.Filter = "[Placed]=0 and [receievd]=0 and [Ordered]=0 and [processed]=0 and [delivered]=0 and [closed]=0"
Me.FilterOn = True
End Sub
Running Access 2010 and developed this query to filter out data from a table between times on 2 dates (day before report run and day of report). Covers data from a shift that carries over to the next day.Trouble is, the PC i developed on still operates the query as expected. However, on the PC the database resides (not networked just stored) and operates, the query brings up no data at all unless I remove the Time filtering.
This PC used to operate correctly up until early this year (about 18 months operated correctly) when the PC was replaced due to failure. Access version is the same and I am at wits end to what the cause is. Here is what my query looks like:
Quote:
SELECT Breakdowns.BreakdownDate, Breakdowns.Time, Breakdowns.Shift, Breakdowns.Downtime, Breakdowns.Equipment, Breakdowns.Conveyor, Breakdowns.Fault, Breakdowns.Stopper, Breakdowns.Gate, Breakdowns.Dolly, Breakdowns.Carrier, Breakdowns.FaultType, Breakdowns.Comments, Breakdowns.Tradesman
FROM Breakdowns
WHERE (((Breakdowns.BreakdownDate)=Date()) AND ((Breakdowns.Time) Between #00:00:00# And #6:29:00#) AND ((Breakdowns.Shift)="Night")) OR (((Breakdowns.BreakdownDate)=Date()-1) AND ((Breakdowns.Time) Between #22:30:00# And #23:59:00#) AND ((Breakdowns.Shift)="Night"));