I have a query filtering question. Please note attach pic for further details. I am trying to put together a search in a form to seach for a certain color or color series from a Qery from two two different sources (from a Combo Box and from a Text Box). I am not sure how to set this up in the query.
Can someone please point me in the right direction?
Thanks,
Kilch
i have about 14 fields that relate to subjects, basically i want to be able to run a query that searches for all the people who have choosen the module and report bak who they are..
now i know i can do this 14 times over but is there a way that the user can choose the subject first and the query will then run hence meaning only one query/report.. i thought of using a list/combo box but have absolutely no idea...
im very much a novice but any help would be most apreciated!
cheers
si
OK, new problem to solve. I don't know if I am going about this the right way, so please feel free to make suggestions on better ways to acheive an outcome!
I have a query "qryBuildsPrinted" which searches a specific table for appropriate records, feeds these selected records into a report "rptBuildSlips" and prints them.
I am now trying to allow the user to print these build slips selectively, instead of printing out the whole lot pulled in by the query every time. My approach is to use the existing query, but filter the query based upon some selections made by the user in one of my forms. I am having trouble getting the query to filter successfully.
I realise that you can filter both the query and the report independently, so I probably only need to filter the report, but that is not working for me either!
Here is my code:
Private Sub cmdPrint_Build_Slip_Click()
Dim intBikeID As Integer, intBuildID As Integer
Dim varItm As Variant
Dim ctl As Control
Dim intCounter As Integer
Dim intRecords As Integer
Dim varArray() As Long
Dim strFilter As String
Dim blnPrinted As Boolean
Dim msgMessage As Variant
'Print Build Slip for selected bike
'Set Control
Set ctl = Me.lstResults
intRecords = 0
intCounter = 0
'check if a selection has been made (kinda dodgy)
For Each varItm In ctl.ItemsSelected
GoTo Selection_Made
Next
GoTo CleanUp
Selection_Made:
'Count how many records have been selected
For Each varItm In ctl.ItemsSelected
intRecords = intRecords + 1
Next
'Clear Array and Re-allocate Upper Bound of array (Array starts at 0)
ReDim varArray(intRecords - 1)
For Each varItm In ctl.ItemsSelected
'determine BuildID of selected record
intBikeID = ctl.ItemData(varItm)
intBuildID = DLookup("[BuildID]", "tblBuilds", "[BikeID] = " & intBikeID)
blnPrinted = DLookup("[PrintedSlip]", "tblBuilds", "[BikeID] = " & intBikeID)
If (blnPrinted = True) Then
msgMessage = MsgBox("One of the bikes selected has already had a Build Slip printed. Please adjust your selection", vbOKOnly, "Build Slip Already Printed")
GoTo CleanUp
End If
'Fill array with values, given how many values are present
varArray(intCounter) = intBuildID
'Adjust counter to go to next array value
intCounter = intCounter + 1
Next
'Select Filter String
Select Case intRecords
Case 1
strFilter = "[BuildID] = " & varArray(0)
Case 2
strFilter = "[BuildID] = " & varArray(0) & " Or " & varArray(1)
End Select
'Open query which displays the bike selected
DoCmd.OpenQuery ("qryBuildsPrinted")
'DoCmd.ApplyFilter , strFilter
'Print report of query with filter applied
DoCmd.OpenReport "rptBuildSlips", , , strFilter
'Close query, remove filter
DoCmd.SetWarnings False
DoCmd.Close acQuery, "qryBuildsPrinted"
DoCmd.SetWarnings True
ctl.Requery
GoTo CleanUp
CleanUp:
intBikeID = Empty
intBuildID = Empty
Set varItm = Nothing
ctl.Value = Empty
Set ctl = Nothing
intCounter = Empty
intRecords = Empty
strFilter = Empty
blnPrinted = False
End Sub
A further question that is probably on the same topic is that once this has been completed, I also need to modify my existing Update Query which makes a modification to the data in the table. Is it possible to apply a filter to an Update query also?
I realise this may not be the most effective use of Access, so as I said, please feel free to suggest alternate methods I can research.
Regards,
Jon
This query is to show aging. I have a field for processed date. I have a query that allows a date range to be selected using the Between function.
The query runs and pulls these variables from a form. It shows only those accounts which were processed between those dates.
However, it is also showing all of the accounts that have no date in this field. I need to set it up so that if the field has no date then it doesn't show those accounts.
I've tried Not (is null) for the criteria but that isn't working. I've tried a lot of stuff but nothing seems to work.
Thanks for the help :)
I have a query that when run asks for a client ID and then displays a total of funds for that client.
I also have a form for each client's persobal details and I would like to have a command button on that form, that when clicked runs the query automatically using the client ID being used.
I can get a command button to open another form based on client ID but not a query.
many thanks
Dear Experts,
I have created a database with queries that is working fine, however there are many records that have the same information that can not be removed easily, is there a method anyone knows of that can filter the records produced from a query to remove duplicates? i.e. 'select distinct' but for the whole query after the search parameters have been entered.
e.g. the search query is producing, after the refined search values are entered:
414353p - Fiat - 1.6 - MLS
414353p - Fiat - 1.6 - MLS
How can i then apply a filter to automatically reduce these duplicates to just one entry for the whole form?
Thanks
I have this form where there's mention of CompanyID
In a subform, I have the information about all the contacts of this Company. In order to see full detail information for a particular contact (of that particular Company) I have a Combobox with a query.
At least: that's the whole idea. Unfortunately it doesn't work in Access:
SELECT Contact.ContactID, Contact.Name
FROM Contact
WHERE ((Contact.CompanyID)=(Me!frmCompany![ContactID]));
Can any help me? I need to do more of the sorts of queries... and I can't figure it out how to use values from forms and parent-forms.
Any help is appreciated,
Jazz
I have a database with 2 tables. Table1:drawing number and drawing title and Table2: date, action, status. Table2 gives the complete history of our internal review process.
Example:
Number=001, Title=Drawing001
date=01/01/07, action=drawing received, status=
date=02/01/07, action=drawing checked, status=approved
date=03/01/07, action=approval sheet issued.
Number=002, Title=Drawing002
date=04/01/07, action=drawing received, status=
date=05/01/07, action=drawing checked, status=not approved
date=06/01/07, action=approval sheet issued.
I need a report which lists just the 'not approved' drawings. I've been able to add a query which lists the drawings but I loose the complete history, i.e. drawing recieved date.
Is there anyway I can display all the fields but only for those drawings which are 'not approved'
hi there,
Im trying to develop a query which will show me players with the same 'player number' in a team.
I have two tables; players and teams.
Each player in a team should have a unique player number, so I want it to show records if eg Jim and Bob are both in Team X and they are both number 6.
Any tips would be great -
I've been generating a report using two forms for data, and everything has worked well until recently.
My report contains a field which tracks Therapist visits by date. Since the subform now contains multiple dates for each patient, the report prints a line for each date, resulting in two or three lines for each patient.
I would like the report to print a single line for each paient with the most recent date only. I've tried a fix suggested by another user in the Forum...(Top Value) in the query, but this resulted in only a single record being printed for the entire database. I then tried to add MAX(date) to the CRITERIA line for the DATE field, but now I get the message
"Cannot have aggregate function in WHERE clause". I'm stumped at this point, so any help would be greatly appreciated.
Doug
I currently have this form (combobox) that filters using one piece of criteria.
Private Sub cboSelectPress_AfterUpdate()
'Moves to Press field and
'finds records where "press" matches whatever is selected in the combo box
DoCmd.ShowAllRecords
Me!Press.SetFocus
DoCmd.FindRecord Me!cboSelectPress
End Sub
"Press" is a machine number. How can I add more combo boxes to filter additional information from my query. Like "room" or "product" or "date?" What happens if the user leaves one of the boxes blank?
My fields are
"Date"- date the product was ran
"press"- the press it was ran on
"product"- the name of the product
"shift"- the shift it was ran on
I want the user to be able to select ALL or none of these fields for results.
I am using Access 2013
I am a student and I've just started to work with the MS Access and databases.
I am wondering if it is possible to filter the fields of table and display only specific information from that specific field. For example: The database (*.mdb file) is created automatically by the software (EPLAN Electric).
There are columns with fields like "de_DE@Verbindungsleitung;pl_PL@Kabel laczacy;en_EN@Connecting cable;en_US@Connecting cable;" But I would like to display in query/table filed only text starting from "en_EN@*" or "??_??@*" or display nothing...
I know that it is possible to display only rows which contain this text, but how can I get rid of fields from unwanted words...?
I cannot change original table of database because the parent-program (EPLAN Electric) will not recognize this database... And additionally data base is updated via EPLAN so every new field should be filtered in this way...
I have a query that just shows all the records in a table. It is used by the end user for filtering primarily. Now the user would like to see a total for the amount filtered.
For example; the table is for repairs. The query just shows ALL the repairs. The user filters the client field to find all repairs for one client. He then wants to see what the total charges are for that query.
I cant create a new field and sum the records because it is not a totals query. Is there any way to embed the query in a form and use the form portion to sum the filtered results?
1 table contains the records I needs to be queried.
The result Im looking for is to view all the clients that have a different advisor assigned to a duplicated ssn. - not same advisors assigned to a duplicated ssn.
Example of desired outcome.
ssn name advisor plan
111223333 joe smith john doe b
111223333 joe smith jane sony c
111223333 joe smith unknown a
I am getting all data.
ssn name advisor plan
111223330 Jack Jack john doe b
111223330 Jack Jack john doe c
111223330 Jack Jack john doe a
Ive tried query with using 'first' for the ssn and the advisor still give me all data.
Please help me....
Thanks
I have a form named form1. In the form I have two unbound text boxes formated as general date; startdate and enddate are the text box names. In my query criteria for the ContactDateTime field I put the following code
Code:
[Forms]![Form1]![StartDate] And [Forms]![Form1]![EndDate]
When I run it I don't get any results. So it runs but no records come up. I have about five queries that run when I click a button and I want to be able to just select start and end dates once on the form and have all the quieries run.
I have a crosstab query. There is a field for row headings that specifies QUARTER (Q1,Q2, Q3,Q4) and another field for row headings that specifies YEAR(2015,2016,...etc)
I want to be able to have the user input the criteria in to a box and filter the crosstab query to then display the filtered results.
Right now on my column headings I have MONTH.
When I try to reference the form in the criteria, I always get the error
"The Microsoft Database Engine does not recognize ..... as a valid field name or expression"
Does this mean that you can not filter crosstab queries?
I am trying to use a criteria to filter out nulls in concatenated variable, which slows the query considerably.The part that makes it slow looks like this -
WHERE ... AND (([tbl1].[x] & [tbl1].[y]) <> "") AND ...
If I work them individually, it's not a problem, the query is fast, but I have to combine them first in order to produce the correct output.
I am creating a query that should filter records of events based on multiple fields. The filters should work with any combination of field criteria, but only two of the four field are working properly, as follows:
1. a text box for searching with event name (free text) - this is working;
2. a combo box to filter events by country name - this is working;
3. a combo box to filter events by event's keyword (category) - this is NOT working;
4. a combo box to filter events by year - this is NOT working
I have made a couple of posts about this and had no response as yet. Maybe i didn't explain it well.
I have a form that logs emails. The emails are either "to" or "from" a person. I want to have two queries that pick out either to or from. Now each person i know sends me several emails over time obviously but i am only concerned with their last email or MY last email to THEM because that determines if i should email them back or if i am waiting for them to contact me. At the moment i am having problems filtering out the last record for each person which determines whether i need to email them or they need to email me. I also use the record to log when the last mobile text was sent, phone call etc so it is not just emails. The following is what my formsfields look like
CommunicationID
Communication type
TO or From
Date
Day (automatically taken from the date)
Subject
I cannot for the life of me get the filter to work properly. Currently i am either getting more than one record for each person or the results are jumbled up. I really need a better understanding of how to use the totals thingy in the querys design view. I'd really appreciate some help on this. Thanks....Ross
:confused: :confused: :confused:
Tried to find things but being a mix of a couple of different actions havn't been able to actually find it
So basically I found a awesome form somewhere that builds the SQL query based on all the users selections.
When you hit the create button it will save the query and update the sub data sheet below with your query results.
From here the part I'm lost on is getting the TOP x number of records and moving them to a new table, I wanted it to work on the 2 drop down boxes that are on the form. select your values and hit the button.
I'd imagine it would be a sub query that I'd use for the top x like I have in the past but I just can't seem to get it to work
Quick steps
1. create your query
2. check your data in the sub datasheet
3. decide the number of records to move to the new table
4. select the name
5. hit the button
This would copy the ban, xcv & dfs fields from the importeddata table to the moverecordshere table & also update persname in the moverecordshere table with the name selected in the form.
Sample db attached ....
I just wanted to know how can i set a parameter on the following cross tab query to filter dates. Date field is [pdate By Day].
TRANSFORM Sum([PettyMaster Query2].Amount) AS SumOfAmount
SELECT [PettyMaster Query2].[Petty Cat].Field2, [PettyMaster Query2].[pdate By Day], [PettyMaster Query2].ProjLoc, [PettyMaster Query2].descriptionofpay, [PettyMaster Query2].projno
FROM PettyMaster, [PettyMaster Query2]
GROUP BY [PettyMaster Query2].[Petty Cat].Field2, [PettyMaster Query2].[pdate By Day], [PettyMaster Query2].ProjLoc, [PettyMaster Query2].descriptionofpay, [PettyMaster Query2].projno
PIVOT [PettyMaster Query2].PettyCOA.Field2;
I have a form (DropDown form) that has 3 drop down fields, you select your values from the drop downs and you would push a command button that runs an event procedure which runs a query (DropDown qry test). The user should have the option of picking any combination of fields to filter by. Or no combination, which would return all values in all fields. So I am basically using the form as parameter's for the query.
The problem I'm having is that my query is returning values for one field AND values for another field. Even if the other values selected are not in the same record. It's not combining the fields together to filter. For example: you pick a Project name and Supplier name, the query will return records that have the project name you selected but it will also return records with the supplier name you selected that have a different project name.
I've attached screen shots of the form and the design view of the query (the screen shot cut off the last column name. It is meant to say "Expr3: [Forms]![DropDown form]![Combo7]").
Using Windows 7,
Access 2010
Is there a way to select multiple values from the drop downs?
I have a text box (txtInput), is there a way that I can filter results on a form based on txtInput.text?
View 1 Replies View RelatedHey guys im a bit stuck and would really appreciate any suggestions...
I have 3 tables:
Table 1: Products
Product_Name
Table 2: Suppliers
Supplier_Name
Table 3: Prices
Product_Name
Supplier_Name
Supplier_Price
Table 1 & 2 have a relationship to table 3, with a many to many relationship. i.e. many products can have many suppliers.
This part works well and i have a form that shows each product with a subtable that shows that products suppliers.
However, i want to be able to filter to prices table to show each individual supplier and what products they supply.
I appreciate they time anyone took to read this and hope i made it clear, and if any one can help like i siad i would be very gratefully :)
When using the MS Works database the "Find" command makes a new table of all records that include the chosen words.
The "Find" command on Access 2002 does not do this, nor does any filter command. Is there an easy way to do this in Access?
Thanks
I want to filter the information displayed on a form using the values I select in text or combo boxes.
I want to filter the form using one or more of the following options.
Call Ref - text box
Location No - Combo box
Area - combo box
Status - combo box
The combo boxes are taking their values from other tables within the database.
Can anyone help me develop the code needed to filter the form.
Thanks