I am trying to set up a user friendly front end to my database. I would like to have a form which allows the user to enter information into text boxes and then press a button which finds the relevant record(s).
I would like to use it to pin point an employee's record based on at least one piece of information, but possibly combined with up to four more by entering into these boxes. For example personnel number, surname, first name etc.
Is this possible and how do I go about setting it up?
I am aware of the filter function and queries but I can't seem to get either to operate in the way I have envisaged. They don't appear to offer the most user friendly solution, at least not in the format I have tried.
Apologies, but I am still getting to grips with access to be honest.
I have a form Delete which contains four fields i.e. Date, City, Depots and Vendor, which has combobox.
I am trying to delete a record from table "Sheet1" based on the combobox, for which i have written the following code, but getting an error at the lines highlighted in red :
Private Sub Command30_Click() On Error GoTo Err_delete_Click Dim stDocName As String Dim intResponse As Integer intResponse = MsgBox("Are you sure you want to delete this record?", vbYesNo + vbExclamation, "Cash Management Team") Select Case intResponse Case vbYes CurrentDb.Execute _
I have a dataset of about 70 columns but would like to remove any two rows that sum to zero (based on two columns) if the column "Units" are the same for the same "Bar code" thus to say "for the same 'Units' (for any two lines), check to see if their respective 'Bar Codes’ are the same and if so remove if their amounts sums to zero".
I have the following code which seems to be working but for relatively large data ( about 5k), it does not select all the data that meets the criteria:
Code: SELECT * FROM datatable WHERE ((([datatable].[Unit] & [datatable].[Bar Code] & Abs([datatable].[Amount])) In (SELECT datatable.Unit&datatable.[Bar Code]&abs(datatable.Amount) FROM datatable GROUP BY datatable.Unit&datatable.[Bar Code]&abs(datatable.Amount) HAVING count(*)>= 2 and sum(datatable.Amount)=0))) ORDER BY [datatable].Unit & [datatable].[Bar Code] & Abs([datatable].Amount);
identifying the bug in this code that makes it not pull all the data meeting the criteria.
I have a text box on my main form, that filters records on a subform, where its record source is a query.The text box works okay for filtering, however it is not doing something that I would like it to.Say we have these records
TM STEELS TM Steels
If I was to type in STEELS, then only the top record would be displayed, likewise, if I typed in Steels, only the bottom would be displayed.What I would like, is if you were to type steels, for example, both of the records would be displayed.In the criteria of the field this is based on, in the query, I have:
Code: Like "*" & [Forms]![MainWindow].[NameTxt] & "*"
As I say, this works for bringing in results that EXACTLY match the text entered in the text box.
I'm wondering what would be the best way to update a list of records based on today's date. Right now I have it to open the form based on the LAST record and then navigate to the record they would like to update, I would only like to show what was entered that day.
Was wondering if it is possible to create a query or another method that would calculate future dates based on inputted info ?
For example a person inputs on a form a date completed (06/14/07) and then also selects a frequency of when this has to be revisited....monthly, quarterly, semi-annually.
So based on the date completed that the person inputs I'm trying to get the date if they select monthly of 7/14/07 (using above date example).
I am looking for an elegant way to retrieve a number from a table and display it in a form, where the number is determined by two values that will be input by the user to the form.
In particular, I want to be able to enter into a form:
- a date; and - a class of asset
And have the relevant rate of depreciation for that asset class and that year appear in the form.
Sounds trivial and I guess it can be done in a number of ways. I have tried DLookup. It looks like it should work, but I have not been able to get the syntax such that it will accept a control value as a field name in the first argument of DLookup. (This would involve a table much like a spread sheet with a column for each year. I guess such tables are frowned upon and I have also heard negative comments about Lookups.)
I have experimented with concatenating the two fields into one such there is only one field to search upon. Seems to work. The table would only be updated annually and would only be a few thousand records.
I have experimented with having two joins between tables, but that did not seem ideal.
Perhaps a query of a query?
Perhaps a macro?
(Assuming I can get the form to work I would also like to create a report that lists assets and their depreciation.)
I have a form and a subform with a master/child relationship set based on the primary key of each underlying table. All good there.Now, I want to use VBA to create a filter based on a set of inputs via combo boxes. But the filter must filter both the Parent and Child records.Example. "Show me only records where both only the Parent.Field1 = "string" and Child.Field = "string".I can do this in a QRY as follows:
SELECT Projects.[Project Number], Lessons.[Actions Resolved] FROM Projects INNER JOIN Lessons ON Projects.ProjectsRecordID = Lessons.ProjectsRecordID WHERE (((Projects.[Project Number])="AU-2102421") AND ((Lessons.[Actions Resolved])=True));
But, if I make this as a record source for the Parent Form, then the records in the Parent Form are repeated for each individual record in the Child form.
I'm working on a table which has a country field, but this field may contain a text string consisting of more than one country, eg "France, Belgium, Spain"
I want to run a query against the table to select records for any one country, but not sure how to do this.
Table name: Items Field: ItemName Form name: Frmpayments
when I open the above form, it should create texts boxes on "frmpayments" based on how many data available in "Items" table under "Itemname" field. It'll be even better, if it can put those item's name in each text box it creates.
I have a file that I need to import on a regular basis. There are two different issues with the import:
1. the first digit in the file indicates if it is a record I want to keep. In this case a 1 indicates a repair record and an 8 indicates it is just information such as email, contact info etc.
2. I have a field in the file that may change the import specifications
For example:
If the record is a Non wheel repair then column 167 is a 50 character description If the record is Wheel repair then column 167 is a 28 character description and the remaining 22 characters are broken down into 10 different fields
So I need to do the following
1. Read the first character in the line and determine if it is a number 1 and if not discard it 2. Read a field in column 109 that is two characters long and if it says it is a wheel repair the import will break column 167 -216 down to the appropriate 11 fields and if it is a non wheel repair it will import columns 167 - 216 as one big description field.
I have used the import specifications with Access but it seems this will have to be done in code in a module or something and I am not very good with VB. (only know the basics)
One other issue is I have dates in the file that are 130225 and 1302 (so full date and then Year/Month) if i take out the / date separator in the import spec the full date works but the Year/Month doesn't.
I'm trying to create a database for a construction company, as an experiment more than anything, and my problem is this:
I need to create a set of identical records in the table "buildings" based on the information entered into a text box by a user on the form for registering a new project. I want each record to be identical except for the "plot number" field, which I want to start at one and increase to the number of buildings defined in the text box.
"Projects" is one table and "Plots" is in the other.
How to find the max value of a field in a query... How does a query display the max record based on the primary key... want to isolate the last record created, the one with the max primary key.
I have a table where there is information stored about certain workorders.. like location, description, etc.
The thing is that location is descripted as "03-LZ-.." where the 03 part stands for the departement. I have another table with all the numbers and departements and now I'm looking for a way to link the two. So I have to find a way to look at the first to digits of the "location" in my workorder table and link that number with the table where numbers and departements are stored.
I've had to export data from an old third party database to Access with the task of reporting on the data. I have a client table and work tables, one for each year going back to 1992. In the original database, the clients do not have a date of when they were set up. I need to find out how long clients have been with us, so I thought of going back through the work tables 2006 to 1992 and finding out where they first occurred, by looking for duplicate records in the tables. Is this a simple enough thing to do? The client table and work tables are linked through the field Client.
Can't think how I would do this. If I create a query with a certain date then it will need to be changed every time the query is run. How would I write a query that automatically looks for a date 3 months or more old.
I have a table, and there are many records, each with a unique value in the OrderNumber field
I have a text box on a form, and i want it to work so that the user types in a OrderNumber value, and it loads that record into the form (the form is bound to the table)
I already know how to load a record by record number, but how can i do it by OrderNumber value?
Hello, i need to have my database find records that were entered between two dates. To do this i made a query that included the dates the records needed to be in between....[date1] and [date2]. Then i had the record dates be called [sent date]. i made my query and in the criteria for [sent date] i wrote (Between [Date1] And [Date2]) when i enter the two dates [date1] and [date2] the query does not show the records that are between that date. why is this happening? is there and easier way? another way? For example, say that i have records with their [sent dates] equaling 12/30/2004, 1/1/2005, 10/1/2005 i want to make it so that when the user inputs a records with dates [date1] and [date2], that those records will show up if their [sent dates] are between [date1] and [date2]. if [date1] is 1/1/2003 and [date2] is 1/1/2006, all of the records will show up. if [date1] is 12/31/2004 and [date2] is 1/3/2005, then only the record with the date 1/1/2005 will show up. how can i do this?
I have patients and their details all listed in onetable. I want a message box to come up if a patient also has a family memeber somewhere else in this table. To do this i need to match surname and mother's christian name. Ani ideas?
fiscal_year acct_period loc_rc account afe description amount system 2015 6 01001028 745003 100050652John A Doe -69.72 E 2015 6 01001028 745003 100086599Jane Doe -33.37 E 2015 6 01001028 745003 100086599Jane Doe -587.69 E 2015 6 01001028 745003 994709 AP - EXPRPT050815SG - Jane Doe - 1 33.37 L 2015 6 01001028 745003 994709 AP - EXPRPT050815SG - Jane Doe - 2 587.69 L 2015 6 01001028 745003 994709 AP - EXPRPT05 2115PK - John A Doe - 1 69.72 L 2015 6 01001028 745006 100048910KROGER -389.74 E
It's combining data from 2 system Identified by E or L. The data is similar but not exact & I'm comparing it using the description. What I need Access to do is find the record that doesn't "zero-out". In the example above the record it should pull is the last one "Kroger $389.74". How to do this but I do have a query/report that will subtotal based on the LOC-RC field.
I supply data to our customer for their system, a single table listing 1.9 millon records. 1 of the 7 fields is unique but the rest in the table can contain duplicated data. My problem is caplial letters vs lower case. How can I query my table to find just the mixed and lower case versions of for example "BIRMINGHAM" ie "birmingham" or "Birmingham". My customers system is rejecting the Data due to some lower case entries.
I have two tables Table 1 and Table 2. Table 1 has 6 fields 175 records and Table 2 has 4 fields and 330 records. The first three fields in both the tables are the same. I need to find all records in the first table that have a matching record in the second table based on those first three fields. How can I accomplish this? Any help would be greatly appreciated.
The last field in my table is 'Comments'. I want to find if any record or records with 'Comments' field empty. I created a query and added all fields from my table. In the criteria section I typed Like "null". But the query is not returning records where 'Comments' field is empty. I have deliberately added (for testing) a few records leaving comments field empty. This can be viewed clearly in Table view. But the query I made is not displaying the records. Please help.
I have a table tbl_PolicyDetails with details of the policy the customers have with us.. In the tbl_PolicyDetails, I have two fields (these are the ones in question) called
"policyStarted" - Start Date of a Policy and "policyPeriod" - Term of Policy Monthly/Annually/Quarterly
So in a Form view I just get the Next installment for the customer based on this information for display.. For example..
Mr Butters Stotch's policy started on 26/04/2013 and is paying Quarterly; the next (i.e. 2nd) installment would be on 26/07/2013..
Ms Wendy Testaburger's policy started on 07/04/2013 and is paying Monthly; the next (i,e. 3rd) installment would be on 07/06/2013..
This is not hard to get.. I have that sorted.. But the problem is, my manager wants to be able to specify two dates and search all policies that will be 'paying in' that Date range should be picked up..
So if the search range happens to be.. 01/07/2013 and 31/07/2013 Then Mr Butters Stotch's policy should be picked up, as his next installment falls on 26/07/2013.. Since this information is not stored, I cannot run a direct Query on this.. Also as the Installment number varies for each customer, I would not be able to just add 1 - Month or Quarter or Annum, and see if the date falls in that range..
