I have form where some or more field exist. I want to compare two field as :
1. cboBatchID As Combo box
2. txtBillNum As Text Box
Private Sub cboBatchID_AfterUpdate()
If Me.cboBatchID.Column(4) <= 0 Then
Me.txtBillNum = 1
Else
Me.txtBillNum = CLng(Me.cboBatchID.Column(4)) + 1
End If
End Sub
I mean, if cboBatchID.Column(4) <=0 then txtBillNum start from 1 automatically or cboBatchID.Column(4) >=0 then txtBillNum = cboBatchID.Column(4)+1
I'm already trying with the code above. But does not work. Generate run time error.
I have a database with a pre-surgery field and a post-surgery field. The use must enter the current procedure in the pre-field and then the actual surgery in the post field. I need to check to see if the fields match verbatim. The field is a memo field.
The users know how to copy and paste from the pre to the post fields. 90% of the time it is the same but not always.
I am trying to compare two different fields in the same query and return the most recent date in some another field. Would I be able to do this and if so how?
Hello, I have 2 fields that are supposed to have the same information for each record. I got the information from different sources, which is the reason for having 2 fields. I wanted to make sure that the information was accurate. Turns out, that some of it isn't accurate--all the fields don't match.
As you can see, the ISSN numbers for Title 1 and 3 match. There's no problem there. I want a query that would list all those like Title 2, where the ISSN numbers don't match. Then I'd have a list of all of the problem Titles, and I could look up the real ISSN numbers.
The two tables are joined together by a primary and foreign key in the query, . I have not added the actual tables below , as data is senitive - i'm using an example. This join is made using the graphic relationship between the tables.
Table_1 with below column Mode 6 6 6 6 6 4 4
Primary KEY 0001 0002 0003
[code]....
Initially the pseudo code i'm trying to get to work is;
if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH"
Expr1: IIf(IsNumeric([TABLE_2]![TYPE])="06" And IsNumeric([TABLE_1]![MODE])=6,"MATCH","NO_MATCH") ,
However this calculated field returns incorrect results, i.e. the query returns MATCH for rows that do not match , e.g. mode=6 and type=GL , Previously , when it was returning #Error when trying to match mode=6 and type=GL when it should ideally return "NO_MATCH", which is why I added Isnumeric.
Once I have sorted this out, I would like to achieve this as the calculated field
if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH" or if table_1.mode = 4 and table_2.type = 04 , then "MATCH" , else "NO_MATCH
It has been a while since I last used Access, recently I've used mySQL and PHP.
First a little description of what I'm trying to accomplish: I have three tables...
tblImport - has the fields: TestID(PK), i001, i002, i003, i004 ... i025 Note: i00# field contains the multiple choice answer (i.e. 1,2,3,4,5) as imported from a CSV file.
tblStudentAsr - has the fields TestID(PK), StudentID(PK), 001, 002, 003 ... 025. Note: 00# containes the multiple choice answer (i.e. 1,2,3,4,5) as enterd by the student.
tblResult - TestID(PK), StudentID(PK), a001,a002, a003 ... a025. Note: the fields a00# have the datatype set to "yes/no"
What I'm trying to do is compare the answers in tblImport to the answers in tblStudentAsr then output the result to tblResult .
Here is some dirty pseudocode: if i001 = 001 then INSERT 1 INTO tblResult else INSERT 0 INTO tblResult
The above example gets a little repetitive since I would have to do that for each question. Is it possible to put the answer fields of each table into a recordset then compare them? (I have heard that using rs's can be a little slow. Although there is only a max of 25 questions the number of students can be quite large)
Alternativly, can I accomplish this using only SQL statements?
What would be the best way to attack this?
Any suggestions (or alternate suggestions) would be greatly appreciated.
Hi, I just started doing something in access and need your help. I have created a database and need to create a query which will sort all important fields within a certain time period. I have a date field and in the criteria field I wrote an expression:">Forms![Insert_date_form]![starting_date_field] and < Forms![insert_date_form]![ending_date_field]"
Its purpose is to show all fields which date is between this to dates. User will have to enter this dates in a separate form(Insert_date_form) which will store it in a small database containing only these two fields, e.g. generate report of all employees that are started working within a period between starting_date and a ending date. When I try to create and open a report based on this query I've been prompted with a small dialog which says: Enter parameter value.... for starting_date_field and same for ending_date_field. I don't need that. These dates are allready entered by user in a insert_date form and stored in its databese.
Is there a beter way to do this?
Thanks! P.S. Sorry for my poor english. It's not my native language.
I have a form control with the input mask on it for a phone number. I am using an append query to append the information entered in the form to a table. I need to pull two fields from a linked table from another database. What I need to do is be able to compare the entered phone number in the form with the phone number from the table. The problem I am running into is that it won't match the phone numbers. The same mask is used in the table I am pulling from. Why can I not just put =[Table].[Phone_No] in the Criteria of the field where the number from the form is placed so that I can get the right record from the linked table?
I have two fields in a table that have multiple values. Example:
Field A:
CT, CA, PA
Field B:
CT, CA
I want to compare the two fields and indicate that there is a match because in this example CT and CA are in both fields. I would like to create a function. I'm not sure if I would have to use something with like or create a loop.
I am trying to calculate a value based on comparing the current date to the dates in these fields. I am using the below formula. However, using 6/27/2013 as the current date, my formula keeps resulting in "Bronze" when it should result in "Standard" Am I doing something wrong?
=IIf(Date()<[Bronze],"Standard",IIf((Date()>=[Bronze]) And (Date()<[Silver]),"Bronze",IIf(Date()>=[Silver] And Date()<[Gold],"Silver",IIf(Date()>=[Gold] And Date()<[Platinum],"Gold","Platinum"))))
In Dec 2013 I created an Access table "Donations" that contains fields regarding charitable donations (Date, Donor, Amount). I use an Access form "Select Donor Form" to select a specific Donor (via combo box) from the table. Then an Access Query "Select Donor Query" uses the Donor selected from the combo box in the form to display all of the donations from that Donor for the year as an Access report.
Since I just created this Access table last month, it only had data from 2013. Now that we are in 2014, in preparation for when I run this report next December, I obviously need to modify "something" to clarify that I want the donations from a specified Donor for a particular year (i.e 2014, 2015, 2016, etc.).
I added a field to the Access table called "Year". I probably can derive the year from the already present "Date" field, but sadly, I don't know how to do that. I want to simply edit the existing combobox in the existing form to return Donors that have a record in the existing table where the Year = 2014. That way, the only changes I will need to make over time is to update which year I need and all of the Donors for that year will populate in the combo box.
Below is the Access macro belonging to the combo box in the form. I converted the macro to Visual Basic. What to add to select the Donor for a particular year. You can see that right now it is only selecting "Donor Name" without regard to which year the donor has a record.
I have a form "Admin" used to create new records in a table "RCJ"
I have a table called "Projects" that contains detailed information for projects.
I have a table called "Waterfall" that contains basic information and schedule information for each particular project.
I have a combo box "Contract" that selects information based on a query "Core" that gathers certain information I want to use of form "Admin"
I understand it's generally not a good idea to duplicate data in tables, but for specific reasons I am here. My combo button "Contract" has an event "On Click" that gathers information I want copied from table "Waterfall" via query "Core" is:
Private Sub Lookup1_Click()
Me![Project Description] = Lookup1.Column(1) =>goes to a text box Me![TContract] = Lookup1.Column(3) => goes to a text box Me![Requestor] = Lookup1.Column(5) => goes to a combo box Me![AEM] = Lookup1.Column(4) => goes to a combo box Me![AE] = Lookup1.Column(6) => goes to a combo box Me![Priority] = Lookup1.Column(12) => goes to a combo box End Sub
Now, the items listed above are sometimes just a text box and sometimes a combo box (because there are times, when items get entered that don't fit the original combo box.
Now, here's the rub. When I select the pull down on the combo box it lists all available projects. You find the one you want and select it. At that point, only Columns 1 (text box) and 6 (combo box) auto fill. The other information does not fill in .. BUT, if I more to the prior record and back, all information shows up OR if I click on any of the particular fields, the information shows up in that field. In theory, everything should show up right away.
I thought it might be because it doesn't like to autofill a combo box, but one of the combo boxes fills fine. And all of the information is acutally going to the various boxes, it just doesn't show up right away.
I have form with an unbound combo box which is populated from a query
It should populate a field in a table which is related to another - however it only works when i query by id number (primary key) and not the text value i want - is it possible to query both so it returns say 1,option 1 - then when selected it will place a 1 in the table - which is related to option 1 in another table instead of just having to select '1' which will mean nothing to a user?
I have created a form with combo box which shows certain fields I selected. By clicking that I want to open a NEW form which has all the fields so that I can edit the record.
I have a form bound to a table which has 20 (name)fields and 1 date per record. A user fills in this form first to indicate which people are present this day. I know it would be better to use 1 name per record but that would in this case not work since the form must show all names for that day before saving and closing.
I have a different form where I use 4 combo boxes and a date field. The values that can be selected in the combo boxes should come from the 20 names on the first form and with the same date as the other form. So only 4 people of the 20 indicated as present on that day can be selected to have performed some task.
I know how to select with a combo box from different records, but how would I do it from different fields in the same record? And then also for a particular date?
I have a Table with 6 fields, 2 are named Initial and Surname. Is it at all possible to have a combo box on a form with these to fields "joined" with the alphabetical order on the Surname. I understand that using a query will not work as the result is based on an expression
I have a combo box that pulls account name data from tblAcctInfo. the combo box has an OnChange event which updates a textbox, txtAcctAddr.
when i have a new account that i would like to be listed in the combo box, i use a form, frmAcctAdd, to add a record to tblAcctInfo.
what i would like to do, is: 1) when i type a new value in the combo box that isn't in the list, have that string value pre-populate in the frmAcctAdd. 2) when i have added the new account info into frmAcctAdd and then saved the record, i would like the new value to pre-populate in the combo box, with the txtAcctAddr textbox also updated via the OnChange event (or maybe a different event is more appropriate?).
I have created a long version of this which requires a lot more user interaction (1-user typing in a new value into the combo box, 2-user RE-typing the SAME value into a data entry form, frmAcctAdd, 3-user saving the new record, 4-user re-clicking the combo box and selecting the newly added value) but i am trying to streamline the data entry with auto-populated fields.
I have a combo box linked to a look-up table. There are 8 fields in the look-up table but only if one of three is chosen do I want additional combo boxes to become visible. Do I put the code in the "after update" or "on change" event of the combo box and how do I express the code-
If TechniqueCombo="caudal" or "spinal" or "epidural" then TextNeedleType.Visible=True
I have several comboboxes (6) on my form.How to populate these comboboxes with values depending on selected value in previous combobox.
Example.Lets say that you select value "Audi" in combobox 1, then available values in combobox 2 should be "A4","A6","TT" etc. and if you selected "BMW" in combobox 1, then available values in combobox 2 should be "3-series", "5-series" etc...
I have a form that currently uses a "catch all" table for listing available equipment to choose from for an equipment field. I call it tblEquipment. What I want to do is to make it so when I type a name in (1 of 35) in one field of the current record, the record source for the equipment field immediately looks at a different table that has equipment available only for that name. To do this I plan on making 35 different tables with limited data originally found in tblEquipment. I would call these tblEquipment1, tblEquipment2, etc. I do not use a sub form, nor do I want to.
So my questions are:
1) can this be done 2)If it can be done, how can I do it?
I have a Suppliers database which contains a form that will allow me to place orders with Suppliers.The Main form has a combo box that allows me to select the supplier. The combo box is called SupplierID with the following:
Row source: SELECT Suppliers.SupplierID, Suppliers.CompanyName FROM Suppliers ORDER BY Suppliers.CompanyName;
The subform is called Stock Subform witha combo box called ProductID with the following:
Row source: SELECT DISTINCT Products.ProductID, Products.ProductName, Suppliers.CompanyName, Products.Discontinued FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID=Products.SupplierID WHERE (((Products.Discontinued)=0)) ORDER BY Products.ProductName; Event Procedure - AfterUpdate: Private Sub ProductID_AfterUpdate() On Error GoTo Err_ProductID_AfterUpdate Dim strFilter As String ' Evaluate filter before it's passed to DLookup function. strFilter = "ProductID = " & Me!ProductID
[code]..
The Link fields are done on the Purchase Order ID (PONoID).What I want to achieve is to select the supplier from the combo box (SupplierID) on the main form and then the combo box (ProductID) on the subform to filter to only show products directly supplied by the Supplier selected on the Main Form.
