I posted this in the table section however it is better suited here I think.
I have a field in a table that I imported in. This field has the first/last/ and Middle initial all in one. So name=Frank H. Urtz.
So I want to set my criteria to say something like give me everything up to the first " " as expr1. Give me everything from first " " to second " " as expr2. Give me everything thing from second " " to the end as expr3.
I am trying to create a Form in which a user is prompted for a road name and that a listing of all values having that road name are displayed. My problem is that Road Name in the database is a combination of the road name and road suffix such as Evergreen Street. I want the operatior to be able to enter just the road name, Evergreen, and get a listing of all Evergreen addresses; Evergreen St., Evergreen Rd., Evergreen Ln., etc. How do I create the prompt so that it ignores the road suffix and searches just on the name? I should add that I know how to use brackets in a form prompting the user to input a parameter. In a query I can use "*" to find all those records having a portion of the desired data. For example in my query I can use "Evergreen*" to find all records where the fisrt portion of the data record is Evergreen. How can I apply the "*" to a user entry prompt.
I have a subform containing a list of Funds and attributes such as Asset Type, Fund Manager, etc.
Currently, I have a textbox, where the the control source is set so that it will be updated with the Asset Type from the subform.
I also have an unbound combo box that contains a list of Asset Types queried from a table via row source, where user can select the Asset Type.
What I would like is when a record is selected from the subform, the Asset Type is selected on the combo box as a default value. User can select another Asset Type if required. How can I do this?
I probably have an easy question for one of you out there, but I just cannot get it myself.
I need to extract the data between two characters in a string.
The data will be similar to this:
T4454: Text Text-Text: $296.07: Text Text
I need to get the dollar amount between the dollar sign and the colon on the right of it.
So far, I have this for my query:
Amount1: Mid$([subject],InStr([subject],"$"))
Which gets the amount to the left side, but also has the $ in it.
In the example above, I would like to just end up with 296.07 as the result from the query. I should also add that the dollar amount may vary from 1.00 to #,###.00
I am adding another portion to my database and since this is a little different than the other parts I created, I wanted to get some input from the experts.
I am adding Certifications to my database. I am currently tracking skills, OSHA, & Licenses for all employees. My current count for the Certification is around 45. I'm wondering if I should set this up like I did my Skills (look up table for skills that is connected by a one to many relationship to another table) or have all the info on one table?
I would like to select a record from my combobox dropdown list and have that record populate in my subform. Currently, I am only able to select the 1st record at the top of the dropdown list to appear in my subform. But I would like to select any record from the dropdown list and have it populate my subform.
Table 1: Students that are registered this semester. The unique Identifier = full 9 digits of SS#'s
Table 2: Students and regular people current working for the college. Unique identifer = only the last 4 digits of their SS#'s
Objective: I need to know what registerd students or regular people are currently working in the college. In other words, compare table 1 and table 2. The problem is I do not have a common unique identifier in both tables.
Additional Info: Each table also has: * Last_Name * First_Name
I was thinking many do a DUAL comparison...Let's say compare the last four digits numbers and the last and first name from table 2 vs the last four digits and the last and first name from table 1.
trying to get something working on a form. When creating a new client, due to privacy, we have to create a Unique ID for each one. The ID consists of their initials, the year, a serial number and the location. I have it mostly working except for the year. Let me show you what I have and the results, then what I would like the results to be.
When data is input to my form and when i click on preview report, the preview report displays all results (my report source is from my query). However when I click on print preview a portion of data is missing. Basically is the check boxes I have in the report. They all are grayed out. These check boxes are unbounded and not related to the query. Basically I have an event procedure created upon clicking print preview as follows:
Private Sub PreviewMRO_Click() DoCmd.RunCommand acCmdSaveRecord DoCmd.OpenReport "rptMRODataEntry", acViewReport If MROReason = "Repair" Then
[Code] ....
The selections are all drop down boxes in the form and upon selection it will populate the text boxes in my report. It seems to work perfectly upon clicking the preview report button but when I click on print preview the check boxes are grayed out.
i have a table that holds Electronic Gift Card details (those plastic store gift voucher cards). it holds all details of every transaction for every card, so the card number, activations, redemptions, dates, times, transaction values etc. are all recorded in my table.
i have attached a small extraction in a spreadsheet.
a single gift card can have multiple transactions against it throughout its lifespan. there are sometimes error transactions, and i need to write a query that finds these error transactions (they are all flagged with a "1" in the [reversal flag] field, so that bit is easy) but then also then the next transaction that occurs on that card. this subsequent transaction will not be flagged with a "1", but is a reversal of the error and will have an "802" flag in the [transaction type] field.
each transaction is date stamped hh:mm:ss, but i don't know how to write a query that will
1) find gift card number that has a reversal code of "1" 2) find next transaction made on that gift card based on transaction time 3) check that subsequent transaction type is "802" 4) select both transactions 5) repeat for entire table
i can't seem to get the desired result just using a straightforward query, and i don't know much (or any!) VBA in access.
I am attempting to split my Access Database and will upload the back-end portion to a SharePoint site. No matter what I do, I continue to get a "Not a valid file name" error.
I have a form 'Players' which has a field called Contact. When adding a new record to the Players form, the user has to select an existing contact or add a new one (they cant just type in a name).
(There is a contacts table)
So, i want a button next to the contacts field.I want it to:
1. If there is already a contact selected on the form, the contacts form open at this contact, so can be edited.
2. If there is no contact selected, the button will take the user to the contacts form, but will display the first record in the contacts table. From here the user can select the contact they want.
This is what i have (code below). With this code, if there is a contact already entered, the contacts form loads with that contact select, which is correct. However, if no contact has been selected, the contacts form opens with a blank record; whereas i want it to open at first record!
Private Sub Command90_Click() On Error GoTo Err_Command90_Click
Dim stDocName As String Dim stLinkCriteria As String
what I want to be able to do is use the main form and search on postcode surname and display the records that match in the subform and then allow the user to select which record to add additional data to
I have designed an UserForm in Excel, the aim is to copy selected data from Access (tblIndex) to Excel. The form of the Access database is as following: Country Type Date Index ....... ...... ...... ......
The UserForm contains a ListBox "ListCountry" and a ComboBox "ListType" to select country and type, and a ListBox named "ListCT" with two columns for selected country and type, and two TextBox (TextDateSta and TextDateEnd) for the user to Type in start date and end date. What I want to do is to copy the records of the selected Country, Type, and Index from the start date to end date. I suppose I should use SQL like:
strWHERE, I do not know how to define the condition here. The clause below is what I mean, but it looks weird and it does not work.
Num = LiqForm.ListCT.ListCount For i = 0 To Num - 1 strWHERE = "WHERE tblIndex.Type = LiqForm.ListCT.List(i,0) AND _ tblIndex.Country = LiqForm.ListCT.List(i,1) AND _ tblIndex.Date > LiqForm.TextBoxSta.Text AND tblIndex.Date < LiqForm.TextBoxEnd.Text"
Hi All, I hope I can explain what I am trying to achieve.. I have a "Top 1" query (Qry_Avail_StockItem), which selects the next available record in a table. I have a main form (Frm_MasterStock) with a command button "Add Record". If the user clicks the "Add Record" button, I need the form to go to the record that the query has selected.
Hello All, In my table, I have some records that are duplicated (aside from the primary key). How can I select only one record from each set of duplicated entries? (There may be more than 2 of each of the duplicated records, but I only need to pull one of them...doesn't matter which one.)
i.e...Like a 'Find Duplicates' query, but only returning the top 1 primary key from each duplicated set.
The last version of Access I've used was 97 but I'm getting back into it. I've read a couple of things that recommend creating a form based on a query, not a table, especially if a calculated field is involved.
When I create a select query based on 1 table, I can change/add/delete records right in the results of the select query, which will carry over to the form just fine.
However, when I use an additional table and join them in my select query, I can no longer update any of the fields that show in the query result. The link I'm using is just a 1 to 1.
How can I get around this? I'm using the second table just for lookup purposes (use the value of one of the fields in a calculation), but I want to be able to update the fields from table 1 from the form.
I don't quite know if I am heading down a blind alley here. I want to filter a form to show a subset of the records via a toggle button. Click again and you go back to the full record set.
The form is based on a query and the filter is to be based on a copy of the query with several criteria and sorts added. This will result in a record set of active projects comprising about 20% of all records (65 out of 253). The sorting sets the record order to match the main management report and so the Planner can update the active records by navigating through the record set rather than having to search for each record using the Find button.
If what I am trying to do is not the way to achieve the desired result, I'd be grateful for any pointers elsewhere.
I want to try to restrict the user not always changing the record, so i need a Disable combo box command. And i did try it by using "Name.Enabled = False" after user select the value from the combo box, but the program won't let me do that because of the record is on focus. So what can i do? I only have an idea of after the user select the value from cbo then jump the cursor immediately to the second column, but anybody can tell me how to do that? Thanks alot!
