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'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.
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.
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
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:
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.
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?
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:-
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
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).
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!
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.
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.
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.
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.
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.
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!!!
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.
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?
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.
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!
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?
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
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.
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. :)
