Lookup value from a table to input in a query


I am building a mortgage/amortization database and I need to look up the "points paid" from another table and also the "adjustment to margin" based on the points paid and the index, "MTA" or "COFI". I have no idea how or where to write this lookup. Any suggestions

Thank you!

KellyJo
Posted On: Monday 22nd of October 2012 05:30:38 AM Total Views:  483
View Complete with Replies

RELATED TOPICS OF Microsoft Access PROGRAMMING LANGUAGE




Vlookup

I have a huge Excel (manually compiled) and, access query results which I copied to Excel. When trying to do a vlookup, Excel does not recognize the like data which is imported from Access. I made sure the format was general and I tried copying and paste special the data, to no avail. The data I am trying to lookup looks something like 10054.A335. Please help.
VIEWS ON THIS POST

159

Posted on:

Wednesday 17th October 2012
View Replies!

Getting a product to lookup a price.

My problem is I have a form with [Service Ordered] [Quantity] [Price Each] [Postage] [Total Price] and a table with [service name] [price] [postage] I want to be able to select a 'service ordered' on the form and when i select a product i should then see the price that it costs (which is in the table) and the cost of postage in the form as well so that all the data corresponds. I don't know if i have explained that very well but if not please ask me.
VIEWS ON THIS POST

196

Posted on:

Wednesday 17th October 2012
View Replies!

MS Access lookup and compare a range of number...is this possible ?? please help !!!!

, I was wondering can MS Access do a range compair look up. I have 2 tables. One is the Info table and the other is Rate table. The Info table has 3 columns like this: FICO LTV Type 622 76 2 622 68 8 Rate table: FromFICOToFICO FromLTV ToLTV Type Rate 620 629 0.00 65.00 8 0.35 620 629 65.01 70.00 8 0.35 620 629 70.01 75.00 8 0.35 620 629 75.01 80.00 2 0.25 620 629 80.01 85.00 8 0.5 620 629 85.01 90.00 8 0.5 620 629 90.01 95.00 8 0.5 620 629 95.01 100.00 8 0.35 620 629 100.01 103.00 8 0 I want access to read the Info table and pick up the values in the FICO, LTV and Type and compair it. Like with the above example. In the Info table FICO = 622. So then I want it to carry the number 622 down into the Rate table and compare that the number 622 is in FromFICO and ToFICO column to find where does 622 falls in between. In this case there are nine rows in the Rate table that 622 is between 620 and 629. The next criteria is the LTV number in the Info table. It's 76. So back into the Rate table looking for the number 76. And 76 is happen to fall between 75.01 and 80.00 in the FromLTV column and ToLTV column. Next back to the Info table is the Type column which is 2. Now back to the Rate table to look up the Type column for Type = 2. So from FICO = 622 , LTV = 76 , Type = 2......So the rate I want to populate is 0.25..........So is this possible to do in Access \t
VIEWS ON THIS POST

162

Posted on:

Thursday 18th October 2012
View Replies!

Primary lookup vs. secondary lookup

solved the problem... silly me
VIEWS ON THIS POST

169

Posted on:

Monday 22nd October 2012
View Replies!

Failure in use of Dlookup in module

Within an Access Module I am searching a table "_cost" (defined as mytab in the declarations) for a cost using a weight value. 'Wt' and 'cost' are the 2 fields in the table. The field 'wt' is the Primary Key to the table. I am using the following code with a variable 'awt' which the program has by this stage filled with the correct value, as I can see from the Msgbox command. The code finds the table correctly but always gives me the value of 'cost' for the first entry in the table, never the correct one. I know that this would happen when duplicate values of 'wt' exist but as the field 'wt' is Primary Key there cannot be duplicates in this field. MsgBox "awt = " & awt cost1 = DLookup([mytab]![Cost], "all_cost", "[wt] = " & awt) MsgBox "cost1 = " & cost1 Even if I replace 'awt' in the DLookup line with a value different to that of the first record, I still get the first record returned. If I build a simple query to do exactly the same action outside of the Module, then it works perfectly and delivers the correct value. Does anyone have any ideas why this might be occurring. I am using Access 2000.
VIEWS ON THIS POST

300

Posted on:

Monday 22nd October 2012
View Replies!

Help needed using lookup

I've got a table with a few columns, and I want to be able to fill data to one of the columns (lets call this column "info") only according to the data found in another column (lets call this column "title") in that same row. I have another table that correlates between the "info" and "title" columns. Example: info title blabla Woof albalb Rabbit gittygoo Spoof In order to do that I chose to use the lookup feature. What I can do now, is have the optional data for the "info" column as aaaalll the data in the table (in the example it will be "blabla","albalb","gittygoo") What I want is to have only the right one. That is, if in the first table I filled the "title" cell with "Woof" I want the "info" cell to automagically fill itself with "blabla" because that's how it is in the other table. I hope I was clear enough, if not please tell me.. Is lookup right here If so, how can I query using the data that was used in a certain cell in that same row If not, is there another way of doing it
VIEWS ON THIS POST

218

Posted on:

Monday 22nd October 2012
View Replies!

Querying based on a lookup column

I'm fairly new to access so apologies if this is a stupid question! I have two tables. One is a list of organisations, and the other a list of individuals. In the 'inidividuals' table I have created a lookup column which allows each individual to be linked with their employee organisation. This link is based on the PKUniqueID of the organisation (although I have designed it so that in both query and form view, the name of the organisation is dsiplayed, not the ID number.) I am trying to create a popup search box which allows the end user to search through the inidividuals by entering all or part of their name or their employee organisation name. I am basing the search box on a query, and have managed to get it to search by their name, but can't seem to make it search by their organisation name, as the query is searching the ID number that is saved in the lookup column rather than the org name to which it applys. Is there something I need to change so that the search will run based on the org name rather than the org ID, even though the Org ID is what is actually linking the two tables toegther
VIEWS ON THIS POST

200

Posted on:

Monday 22nd October 2012
View Replies!

Testing values

I have two textboxes on a form. I want to test to see if they have values. If text1 has a value write it to a variableX If text1 does not have a variable then test text2 If text2 has a variable then write it to a variableX If text2 does not have a variable then clear the variable. Any thoughts on how to do this THanks
VIEWS ON THIS POST

179

Posted on:

Monday 22nd October 2012
View Replies!

Getting max value

I am trying to get he max value from a table that I will be updating in a form... I am using this max value as the unique identifer. I was doing this in Access 2003 but now in Access 2007...its not working... I am trying these two ways...maybe someone can shed some light on this\t\t The table is a LINKED SQL Table in SQL Server Hope someone can help here... THAnks EXAMPLE 1 In this example. Complaint Field Name dbo_Complaints_Table Linked SQL table name Complaint is the Textbox name in the form ERROR: Run time error '6' OVERFLOW Code: 'populate ID box with largest value + 1 Dim varID As Integer varID = Val(DMax("Complaint", "dbo_Complaints_Table")) + 1 Complaint = varID Me.Refresh EXAMPLE 2 I am getting errors on the ADODB Dim statement Code: Dim rs As ADODB.Recordset, MyVal As Currency Set rs = New ADODB.Recordset Open a Connection to the Recordset rs.Open "SELECT MAX(Complaint) from dbo_Complaints_Table", CurrentProject.Connection rs.MoveFirst MyVal = rs.Fields(0).Value Me.Text1.Value = Format(MyVal, "#,##0.00") MsgBox MyVal rs.Close Set rs = Nothing
VIEWS ON THIS POST

230

Posted on:

Monday 22nd October 2012
View Replies!

Cmb residual lookup info when switching records

Understand the basics of using a cascading combo box as follows: Table criteria for stblDiscipline set to [forms]![frmPersonContact].cmbDiscipline and SQL statement for cmbDiscipline_AfterUpdate() Me.cmbSubDiscipline.Requery When I switch to another record the cmbSubDiscipline displays the correct value from the table but, the selection values are left from the last Requery. Unfortunately, the combo box will also let the user select one of the incorrect values. Tried to add a _Current statement without success.
VIEWS ON THIS POST

151

Posted on:

Monday 22nd October 2012
View Replies!

AfterUpdate - values not calculating

Below is the code I have created to calculate the values "CalcValue" and "CalcWork". The values for AssignValue, AssignPriority, Complex, Effort, Goal are all assigned by the selection made in the referred to/related combo boxes. However, the caluclated values return the concatonation of the intergers and not the sum of the integers. Additionally, if I am to use the CDec() function how do I specify the number of decimal places what am I missing here thanks! Private Sub cboAssignPriority_AfterUpdate() Dim CalcValue, AssignValue, AssignPriority, Complex, Effort, Goal, CalcWork As Integer Value = 0 AssignValue = 0 AssignPriority = 0 CalcValue = 0 CalcWork = 0 AssignValue = Me!cboAssignValue.Column(3) AssignPriority = Me!cboAssignPriority.Column(2) Complex = cboDBObjectID.Column(2) Effort = cboTaskTypeID.Column(3) Goal = cboAgencyGoalID.Column(2) Value = AssignValue + AssignPriority CalcWork = Complex + Effort + Goal CalcValue = CDec(Value) CalcValue = Me!lngCalPriority Debug.Print "Complex="; cboDBObjectID.Column(2) Debug.Print "Effort="; cboTaskTypeID.Column(3) Debug.Print "Goal="; cboAgencyGoalID.Column(2) Debug.Print "AssignValue.Column(3)="; cboAssignValue.Column(3) Debug.Print "AssignPriority.Column(2)="; cboAssignPriority.Column(2) Debug.Print "Value ="; Value Debug.Print "CalcValue ="; CalcValue Debug.Print "CalcWork ="; CalcWork End Sub , thanks - I think that's what I've done (almost) but... CalcValue won't calculate correctly! I don't get it. jh Originally Posted by sbenj69 Try something like this: Code: Private Sub cboAssignPriority_AfterUpdate() Dim MyValue as Single Dim CalcValue as Single Dim AssignValue as Single Dim AssignPriority as Single Dim Complex as Single Dim Effort as Single Dim Goal as Single Dim CalcWork as Single MyValue = 0 AssignValue = 0 AssignPriority = 0 CalcValue = 0 CalcWork = 0 AssignValue = Val(Me!cboAssignValue.Column(3)) AssignPriority = Val(Me!cboAssignPriority.Column(2)) Complex = Val(cboDBObjectID.Column(2)) Effort = Val(cboTaskTypeID.Column(3)) Goal = Val(cboAgencyGoalID.Column(2)) MyValue = AssignValue + AssignPriority CalcWork = Complex + Effort + Goal CalcValue = Format(MyValue,"0.00") CalcValue = Val(Me!lngCalPriority) Debug.Print "Complex="; Val(cboDBObjectID.Column(2)) Debug.Print "Effort="; Val(cboTaskTypeID.Column(3)) Debug.Print "Goal="; Val(cboAgencyGoalID.Column(2)) Debug.Print "AssignValue.Column(3)="; Val(cboAssignValue.Column(3)) Debug.Print "AssignPriority.Column(2)="; Val(cboAssignPriority.Column(2)) Debug.Print "Value ="; MyValue Debug.Print "CalcValue ="; CalcValue Debug.Print "CalcWork ="; CalcWork End Sub Not sure if you meant for these lines to be like this: Code: CalcValue = Format(MyValue,"0.00") CalcValue = Val(Me!lngCalPriority) Not exactly sure if that's what you wanted, just went off the code you had posted.
VIEWS ON THIS POST

226

Posted on:

Monday 22nd October 2012
View Replies!

Maintain field value

I have a value in a form that has code behind it to calculate a value for that field when that field is "clicked." works as it should. However, if I go to look at a previous record in the form, and click on that cell, it calculates a new value, when it should maintain the value that is already there. Is there a way to "lock" in the values on a record so if a user goes back to look, the value cannot be changed
VIEWS ON THIS POST

173

Posted on:

Monday 22nd October 2012
View Replies!

Load record to form depending on value in combo box

, I have 2 combo boxes. The first one gives a lis of Prefix letters for railcars. The second uses the value of the 1st and finds railcars that start with the same letters. finally got the 2nd combo box to list the prefix and number of a railcar based on the 1st combo box and also got it to clear as each run-through starts. Now that the user can select a number (ex. dfcv1234 as the railcar), I need to find the record with the prefix dfcv and the car number 1234 and load the record to the form for view and editing. So far, I know it needs to be the after update event and have a select statement that finds the [key], [prefix], [car_number] that matches the value selected on the form but how do I use the key to make the record show on the form
VIEWS ON THIS POST

358

Posted on:

Monday 22nd October 2012
View Replies!

Text box value is null

I have a very simple question that I should now the answer to but I must be having a brain cramp.I have a text box on my form and I want to make sure the textbox is not null before running some code. How do I do that if Me.txtBatchID Is Null -I get an error 'Object Required' If Me.txtBatchID = "" -doesn't work.Even if there's nothing in the text box, apparently access thinks it is. Can anyone help me with this I can't believe I haven't been able to figure this one out! GEM
VIEWS ON THIS POST

161

Posted on:

Monday 22nd October 2012
View Replies!

Dlookup

the variable is only takes the first record in the table does not check the next one, Code: varuid = DLookup("[VehicleReg]", "VehicleInfo", "[VehicleReg]=txtCarReg") VarDsed = DLookup("[Destroyed]", "VehicleInfo", "[VehicleReg]=txtCarReg") VarRen = DLookup("[Remewed]", "VehicleInfo", "[VehicleReg]=txtCarReg") VarCity = DLookup("[City]", "VehicleInfo", "[VehicleReg]='" & txtCarReg & "'" And "[City]='" & txtCity & "'") If ((varuid = Me.txtCarReg) And (VarDsed = False) And (VarCity = Me.comCity)) Then MsgBox "The vehicle is allready regestered" Form.Requery Form.Refresh Me.txtCarReg.SetFocus Else
VIEWS ON THIS POST

113

Posted on:

Monday 22nd October 2012
View Replies!

IIf Statements and NULL values

Within my query I have a field that I need to do a nested IIf Statement that has a NULL value. The name of the field is "smokingcessation". The possible responses are a "1" = Yes, "2"= No, "3"=Unknown and there is also the possibility of a "no answer" at all (blank). I've got the following statement to work so far but need to include the possibility of a blank/no answer response. My statement so far is as follows and is working: IIf([smokingcessation]=1,"Yes",IIf([smokingcessation]=2,"No","Unknown")) How would I do a statement that would take into account the existing possibilities plus that of a blank (NULL) answer and leave the field blank in that case In otherwords, I need to see a "Yes", "No", "Unknown" or a blank field.
VIEWS ON THIS POST

219

Posted on:

Monday 22nd October 2012
View Replies!

Simple lookups in table

Simple questions I hope. 1. What I'd like to do is do a lookup based on 2 fields in my table. I'm tracking inventory for the company I am currently working with. I have a computer table with 3 relevant fields: compID, areaId, locationId computer table compID = PK for this table areaId = FK from area table locationId = FK from location table area table areaId location table locationId areaId There is a distinct relationship between area + location. I want a lookup for the locationID, based on the area they have selected.
VIEWS ON THIS POST

164

Posted on:

Monday 22nd October 2012
View Replies!

Retaining a value entered throughtout the user's session

I am creating a database to track a student's course load in a degree program. When the student enteres their ID on the first form I want to retain that value so subsequent forms and views display their records, or allow new entries/updates to different tables without re-entering their ID. I'm having no luck passing the value entered into the first form's text box to other processes selected prior to closing out the database. Thank you.
VIEWS ON THIS POST

204

Posted on:

Monday 22nd October 2012
View Replies!

Autolookup help

no
VIEWS ON THIS POST

329

Posted on:

Monday 22nd October 2012
View Replies!

Can anyone tells me what's wrong with this lookup?

I'm trying to look up "Adjustment" from Option ARM table where "Index" in query equals "Index" in Option ARM table. What is wrong with the statement below DLookUp([Option ARM Points Paid Margin Adjustments]![Points Paid],[Points Paid to Lower Interest],[Option ARM]![Index]=[Option ARM Points Paid Margin Adjustments]![Index]) Thank you. KellyJo
VIEWS ON THIS POST

231

Posted on:

Monday 22nd October 2012
View Replies!