Filtering Fields Of Table By Query
Sep 3, 2015
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...
View Replies
ADVERTISEMENT
Nov 17, 2007
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
View 14 Replies
View Related
Jan 24, 2015
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 ....
View 1 Replies
View Related
Oct 1, 2014
I have a form with two fields in the header meant for filtering the form data - SelectAtty and SelectClient. The query under the form is restricted based on the values selected. I also have a "button" that if clicked should filter the records (probably unnecessary but I thought it would be clearer for the user). I'm seeing two issues. Sometimes when I select a value - it doesn't appear to apply it to the query at all. When I add msgbox statements to try to see what is happening, the problem goes away (weird right). Also I run into a "no current record" error when it requeries. Mostly this seems to happen when I have two events that fire in a row and both requery. The event procedures are below and you can see where I put my debugging msgbox statements.
1. No current record fires consistently on the filter image click and (I think) always when it requeries twice (like I changed the value and then clicked on the filter).
2. The fact that sometimes the selections are picked up in the query and sometimes aren't I have no good pattern for other than msgboxes seem to actually fix it - as if it forces access to look at the values and they they show up.
Private Sub FilterImage_Click()
MsgBox "filter: " & Form.CurrentRecord
MsgBox Form.RecordSource
[code]....
View 2 Replies
View Related
Aug 6, 2014
I'm using 4 combo-boxes in order to filter a tabular form in access.
One combo box is for a company name the second is for a report type and the third and forth is for choosing years. I used VBA in order to apply the filter:
Code:
Me.Filter = "[Company].Value Like ""*"" & '" & Combo108.Value & "' & ""*"""
Me.Filter = "[Report_Type] =" & "'" & Me.Combo123.Value & "'" & ""
Me.Filter = "[Rep_Year] Between " & Combo125.Value & " and " & Combo127.Value
Each line works by itself but I cant figure out how to combine them together, or how to use "AND" between each filter so the filtering will refer all of them when I run the code.
View 7 Replies
View Related
Nov 21, 2013
I have a query that carries all the properties in our database, and data for when our company 'worked on' properties. Each property falls within a certain province and municipality (this is slightly irrelevant info) , we worked on properties in a period from 2009 - 2013/06/30 this period is called '2009', and we have again worked on properties during a period 2013/07/01 onward - this period is called '2013'.
Now I require this query to display all the properties, but only the entries in certain fields, that relate to '2013'.
I have about 7 fields where this date (either 2009 or 2013) can be displayed, I tried to filter them all simultaneously by using the criteria: Is Null Or "2013", in every one of these fields, but this criteria results in me losing entire records that contained 2009 data, instead of the data simply not being displayed. Even in cases where some of the 'date' fields contained 2013.
How I can keep all records but simply display info relating to work we did in the 2013 period?
View 8 Replies
View Related
Jun 24, 2015
I'm trying to make a form to filter a table and open a data entry form with the filtered data ready for editing. I've got it working when filtering a 1 field but I'd like to filter the data by 3 fields and i can't get it right. the code I'm trying to use is :
Code:
DoCmd.OpenForm "Main", , , "[Admin District]='Corby'" And "[AgeRange]='31 - 50'" And "[Gender]='Male'"
it works fine when just filtering one field like this:-
Code:
DoCmd.OpenForm "Entry75+", , , "Ward='Beddington South'"
View 2 Replies
View Related
Mar 27, 2014
I filter my subform (datasheet) by fields, everything works great except "date" column. MS Access shows that record does not exist but it isn't true.
Code:
Me![frmAktPD].Form.Filter = "[Data przyjęcia] = " & Me.DataPrzyjecia
Me![frmAktPD].Form.FilterOn = True
Where is the problem?
View 1 Replies
View Related
Dec 14, 2004
I have two questions.
I have a form with 2 combo boxes. The first pulls from a simple list. Once you make a choice, the second combo box is then filtered by the first. This works correct, except that once a choice is made in the first, the filter is locked. If you change the first combo box's value a second time it does not effect the second box. The query that I am using for the second combo box has two columns that pull the first 50 characters of a pair of memo fields. Quote: SELECT MSSS.SS_ID, Left([Application_Name],50) AS Expr1, Left([Description],50) AS Expr2, MSSS.Site_Code
FROM MSSS
WHERE (((MSSS.Site_Code)=[Forms]![Edit or Delete Requests]![Combo6])); These fields are then used to populate 2 text fields using an event procedure. Quote: Private Sub Combo10_AfterUpdate()
' Display Partial Application Name and Description based on choice
Me!txtApplication_Name = Me!Combo10.Column(1)
Me!txtDescription = Me!Combo10.Column(2)
End Sub This is working fine, but it starts the next problem.
I need to change the form to include the entire memo field into a text box. I was using the columns of the second combo box to fill them in. Since the combo box is limited to only 50 characters, i could only grab part of it. I am guessing there is a much better way to accomplish what I need, but I am too inexperienced with forms and vb to come up with it. Below are the fields I need from table MSSS
Business_Name
Application_Name
Description
Acronym
Level_1_Support
Level_2_Support
Escalation_process
Troubleshooting
Priority
Links
Modified
Disabled
Thanks in advance to any help!
View 5 Replies
View Related
Jun 20, 2013
I have this code which filters a form with the command button ("Command 82"), but it only searches in the "TASK_NUMBER" field.
How can I make it search multiple fields at once? I feel like you can just use the "Or" operator, but where do I put it?
Private Sub Command82_Click()
Me.Filter = "TASK_NUMBER Like '" & "*" & Me.Text80 & "*'"
Me.FilterOn = True
Me.Requery
End Sub
View 9 Replies
View Related
Nov 21, 2014
I have a table that is basically a survey form. The same series of options was available for 35 questions, and the table used to have a text string written for each answer. Because of all the repetitive data, I created a second table that assigned a number value to each of the nine possible options in these 35 separate fields. What happened is that, instead of the same text strings repeated over and over (and taking up real estate), now each of the 35 columns had a single number in them.
Now comes the day of reckoning and TPTB want a query with the raw data and the original text strings back in instead of the numbers. I was thinking doing something along the lines of a DLookup, but I can't seem to make that work in a query correctly. Apart from calling the same table and linking it over and over to the different fields in the original data table (see photo for how insane that is).
View 2 Replies
View Related
Mar 25, 2008
Hello All,
I am new here and was wondering if anyone has ever had the following happen:
When you 'Filter by Selection' in a table for NULL values the number of records shown are less than the actual number of records with NULL values.
For example, I tried this with a particular tables looking for NULLL records in a particular field and while I could clearly see that the number of NULL values was x, the number returned upon filtering on NULL in that field was less than x.
Any idea? Your help is very much appreciated.
Thanks!
View 6 Replies
View Related
Apr 19, 2006
Hello,
I have a project table that I track the progress of each job in by entering dates when certain portions are done. What I would like to do is when I fill in the "Project Completed" or "Cancelled" columns, to have that record automatically filtered out of the table. I still want to be able to turn off the filter at the end of the year to see all the projects we did, but I don't want this project cluttering up my table once it is finished or cancelled. Right now, I copy and move these completed projects to a separate table, but others say this is confusing and they can't figure out which table to use.
I input everything in the table format and don't have any forms that I use.
Thanks for any help.
Matt
View 1 Replies
View Related
Jan 15, 2006
I have a table that lists all of my available seating for different events.
My table columns look like this:
EventID - Section - Row - Seat - Available
Sample data in columns:
1 - A - A - 1 - Y
1 - A - A - 2 - Y
1 - B - A - 1 - N
1 - B - A - 2 - Y
2 - A - A - 1 - Y
2 - A - A - 2 - Y
2 - B - A - 1 - Y
2 - B - A - 2 - Y
EventID actually goes from 1-6
Section actually goes from A-F
Row actually goes from A-J
Seat actually goes from 1-36
When the user selects an event (or events) in the first box - I would like the query to eliminate the available seating, from the other events, in the results.
When the user selects a section (or sections) - I would like the query to further eliminate the available seating, from sections not chosen, in the results.
When the user selects a row (or rows) - I would like the query to further eliminate the available seating, from rows not chosen, in the results. And finally display whatever available seating is available - based on all three of the above criteria.
I purchased every access book I could find.. and I am slowly learning this. I'm a complete novice..
I'm not looking for any of you to do the work for me.. I'm more looking for guidence. I'm still getting to know all of the controls and their limitations. I have not yet written any VBA code. (It's still very confusing) I'm trying to get by on using the toolbox controls, using queries, macros and form controls.
View 2 Replies
View Related
Jan 7, 2006
Hi, based on previous advice I had merged my customers and owners into one table and selected whether a client was a customer, owner or both via a lookup table.
This is working fine, however I must assign a rep to each owner, I'm not quite sure how to do this, I need to be able to assign a rep to an owner in the clients table, I must also make sure than a rep can not be assigned to someone who is just a customer obviously.
I have attached the database (http://jonroberts.redirectme.net/database.zip), hopefully that will make things more clear.
Thanks in advance.
View 2 Replies
View Related
May 30, 2014
How best to go about designing a new table that shows any records matching a specific user name field from a parent table (without changing any records in the parent table).
For example: Any record in the column "user name" matching "Craig" in the parent table should be displayed in the new table.
I also would like for the new table to update automatically anytime a new record meeting the user name criteria ("Craig" for example) is added to the parent table.
View 6 Replies
View Related
Nov 12, 2014
My database is centered on a main form where users select pieces of information from ComboBoxes (primarily) and submit.
The first part of the form, they must choose from a list of our clients, which is a table with with roughly 5000 rows.
Later on in the form, they must choose a contact person from that client. While Client's may have several contact names (which need to be stored in a table), only one name is necessary for the form portion.
How can I make this Contact Person ComboBox only load names attached to the specific Client selected above?
As it stands right now, the Client List has its own table (along with additional information), and Contact Name is a separate Table with a look up field of Client List.
View 1 Replies
View Related
Jun 6, 2006
My table looks like this:
Sales(a)||Date(a)||Sales(b)||Date(b)||Sales(c)||Da te(c)
1) 50 05/06 75 06/15 100 08/15
So I want to show all sales with the criteria - Date >05/01 <07/01.
The final query totals will be
Sales||Date
50 05/06
7506/15
So the issue is that there are 38 rows with similar data in each column
I have the table in this format because I also have a form that feeds off this table and it needs to be in this format-but I would also like to run a query/report from this table that summaries sales. I also know that since there is more than 1 date value in the row, this is going to make the query difficult to run (each date is linked to a certain sales total).
I guess what I want to do is transfer the horizontal data to vertical data (with certain criteria)
Please help!!!
View 1 Replies
View Related
Dec 2, 2006
Hi,
I am trying to make a query from fields out of 3 tables.
All tables must include following fields:
Table 1 fields:
WR04 (year 2004)
Date
Reporting Person
Table 2 fields:
WR05
Date
Reporting Person
Table 3 fields:
WR06
Date
Reporting Person
I am trying to pull together in the query any given individual (Reporting Person) who may be included in all above tables but believe I may have a relationship problem as I am only getting results that match all tables.
I hope this is clear.
Any suggestions please.
View 4 Replies
View Related
Sep 12, 2014
In query design view, I have added a query with column (nameA) of names and a table with a column (nameB) of names. I am linking these two fields (nameA and nameB) which contain names. The names are spelled exactly the same so I should get results. Both fields are of the same data type.
But even when I add only the field with names from the query (the field called nameA), I don't get any results.
What is wrong here? Are there restrictions about linking a query to a table?
View 3 Replies
View Related
Jan 19, 2014
I have a table having column as date, format as mm/dd/yyyy and i update the column as now() using VBA.
However some places the date is in mm/dd/yyyy and in some mm/dd/yyyy nn:mm:ss are appearing which is making the filtering of the data ambiuge.
View 2 Replies
View Related
Aug 10, 2014
I've created a report which has 4 subreports linked to it via a ClientID. The problem is it prints 2000 reports for the 2000 different ClientIDs. I want, went the button is pressed, for it to ask what ClientID the users wants to print the report for.
I know how to do this in a query based report via criteria, buthow to get this to work when the reports been directly made from a table.
View 14 Replies
View Related
Jan 4, 2006
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
View 2 Replies
View Related
Feb 27, 2007
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
View 2 Replies
View Related
Jan 11, 2008
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
View 3 Replies
View Related
Oct 4, 2007
If I have a make table query where I want to add in some blank fields, say: Field1: “” , Field2: “”, etc…. is there a way I can make these fields a yes/no type instead of the default text?
I know I can manually go into the table in design view after I run the query, but I was hoping I could do it beforehand. :)
Thanks!
View 1 Replies
View Related
