Using DLOOKUP With Tables Only
Nov 27, 2013
In the attached Word document I have a screen shot showing the fields for the "Membership" table and "Class table." I am working exclusively with tables, no forms.
In the Membership table, I will enter a numeric value in the Class field. In the Tuition field, I want to use a DLOOKUP, which would look to the class column next to it, take that value as a lookup number, then go to the Class table (second screen shot) and locate that lookup number in the ClassID field, find the corresponding value in the Tuition column, then drop that result in the tuition field of the membership table.
Example:
In the Membership table, I enter 7 in the Class field. The tuition field should automatically return $160, because in the Class table, the classID 7 corresponds to a price of $160.
I tried setting up the Tuition field in the membership table as a calculated field, then I entered a DLOOKUP in the expression box and received an error.
Is it possible to do this type of Lookup?
View Replies
ADVERTISEMENT
Jul 30, 2014
I'm trying to do a lookup between two tables, whereas table A includes the product ID and table B includes the price. Is it possible to create a field in table A with a DLookup function on the product ID to get the product price from table B?
Whenever I try to type in the DLookup function, it does not calculate.
View 2 Replies
View Related
Oct 19, 2007
I have never some across this before but my database keeps throwing a "Too many tables open error" which has taken me a day to get to the bottom of. I believe that the use of a dlookup in one of the queries not cleaning up after itself.
The issue I have now is that even importing the tables into a new database causes this problem as i guess I'm inheriting the same table id issue.
Does anyone know how to clear the table IDs that the dlookup left open ?
Secondly, could anyone suggest a more robust method of looking up one value from another table. Basically I need to look up the USD to GBP rate (one value) for every record in an other table. I cant use a join (trade ccy->fxrate ccy)as there is properties to link on. Ive tried the elookup function I found on the net but performance is still an issue.
Any help is appreciated
View 6 Replies
View Related
Apr 9, 2014
This is my code.
=DLookUp("[ItemDescription]","MasterData","[BarcodeID] = " & [Forms]![frmMasterData]![Barcode])
I have a from called "TakeOn". The from is based on a table, called "tblTakeOn" with fields ID, Barcode, ItemDescription and SerialNumber.
In the form "Take On" is is a combobox named barcode, that looks up the bacode values from a table called "MasterData".
The "MasterData" table has only 3 fields, ID, Barcode and ItemDescription.
I want to in the "TakeOn" Form to, in the ItemDescription Field, display the ItemDescription from the ItemDescription value in the 'MasterData" table.
Access displays a circular reference error message.
View 1 Replies
View Related
Aug 13, 2012
I'm new to access and need to modify an existing database to get a report I want to create. I'm an Excel user and could accomplish what I want to do in seconds using Excel's Lookup functions, so I'm trying to do the same with Access Dlookup.
I have two tables in my database that tracks components in a hardware product. The first table is just a list of hardware units shipped. The second table is a list of various components (and versions thereof) used in each unit. The two tables are linked by a UNIT_ID field. I need to create a report showing each unit and what the various versions of the components are. My difficulty is that the component table is does not have a dedicated field for each component and each unit can be comprised of a different quantity of components. So the tables look something like this:
UNITS TABLE:
ID Customer
1 Joe
2 Bob
3 Bill
COMPONENTS TABLE:
ID Component Revision
1 Main Board A
1 Blue Board B
2 Main Board B
2 Blue Board B
3 Main Board A
3 Red Board C
I want to produce a report that looks like
ID Customer Main Board Blue Board Red Board
1 Joe A B --
2 Bob B B --
3 Bill A -- C
I setup queries to get revisions of each Board and am trying to compile into the full report, but can't figure it out. Right now I'm trying to use Dlookup to lookup data from one of the queries and insert into a larger table. I can lookup a single value fine, but can't figure out how to get Dlookup to give me the Blue Board & Red Board values for each of the system ID numbers in a master query (which would be a snap in Excel).
View 4 Replies
View Related
Apr 13, 2014
i already make this sql for dlookup table for may insert button, all going good, but when i'm going to make double command with different msg box for different criteria, it going fail
1. This my Working code:
If Me.txtidborang.Tag & "" = "" = (DLookup("NoGerankod", "HutangKeseluruhan", "NoGerankod='" & Me.txtnogeran & "'")) Then
MsgBox "Grant Number Invalid", vbOKOnly
Me.[cbostatuspembayaran] = "Geran Negatif"
CurrentDb.Execute "INSERT INTO Januari (bla..bla..bla..)
End if
2. This my not working code:
If Me.txtidborang.Tag & "" = "" = (DLookup("NoGerankod", "HutangKeseluruhan", "NoGerankod='" & Me.txtnogeran & "'")) Then
MsgBox "Grant Number Invalid", vbOKOnly
Me.[cbostatuspembayaran] = "Geran Negatif"
If Me.txtidborang.Tag & "" = "" = (DLookup("NoMatrikkod", "HutangKeseluruhan", "NoMatrikkod='" & Me.txtukmper & "'")) Then
MsgBox "This Student still have debt ", vbOKOnly
Me.[cbostatuspembayaran] = "Geran Aktif"
CurrentDb.Execute "INSERT INTO Januari (bla..bla..bla..)
End if
End if
Q: How can i combine two dlookup together for different msg box?
View 6 Replies
View Related
May 26, 2015
The image below describes the scope of the issue.
The function is called upon in the onLoad event of frmMain.
View 3 Replies
View Related
May 2, 2005
Can anyone see where I've gone wrong. The purpose should be if the value in the query is 0 or less (a minus amount) and the order number and part number matches the order number and part number on the form then a warning should appear. It's not picking up the record for flagging when it should:
If (DLookup("OutstandingQty", "qryremaininginvoiceamount", "OutstandingQty <= 0" & " And [OrdNo] = " _
& Me.[OrdNo] & " And [InvPtID] = " & Me.InvPtID)) Then
View 6 Replies
View Related
May 19, 2006
Hi,
I am pretty new to Ms Access.
I have a table which has a list of trades (Builder, Carpenter, Painter etc) going down the side and going across the top I have the number of employees (1Emp, 2Emp, 3Emp). The values in the table show the charge for that number of employees for that trade.
I have a form which has three combobox's that show the Number of Manual Employees, Number of Clerical Employees and the number of working directors and a combo box that shows the trade. What I want to happen is when the user clicks a command button on the form the charge for the total number of employees for that trade is shown in a txt box.
I have the following code attached to my button:
Private Sub GetValue_Click ()
Dim TotalEmployees As Integer
TotalEmployees = [NoManual] + [NoClerical] + [NoWorkingDir]
Value = Dlookup(TotalEmployees & "Emp", "ValueTable", "[Business] = Forms![Form1]![Business]")
End Sub.
When I type "[3Emp]" in the Dlookup it will work fine and finds the charge for that trade. But when I pass it the Result from the addition and concatenate it with "Emp" it doesn't seem to work.
I originally thought it was because I declared the TotalEmployees as an Integer by I also tried declaring it as a string - to no avail.
I just can not figure out why it will not look up the TotalEmployees.
I would much appreciate any help on this on any suggest as to how I can get the values from my table.
Charlene
View 1 Replies
View Related
Jul 7, 2006
I am using Dlookup to look up LastName, I have
AssignBy = DLookup("[LastName]", Employees, "POC =" & Me.Combo22)
I am getting an ERROR ""You entered an invalid argument in a domain aggregate function"
I need AssignBy = LastName from Employees Table where POC=what is selected in Form for Combo22.
ex. POC=3 go to Employees Table find POC that equals 3 and get the LastName.
Please Help!!
Jessie
View 4 Replies
View Related
Mar 26, 2007
Please help prevent my laptop from being smashed against a wall numerous times and then being thrown out the window, after all, the wall is mostly innocent in this situation.
I have read post upon post and other dim references to DLookup in the Access Help file, etc. I am generally a bright guy (although inexperienced in DataBases, VBA and some forms of lovemaking) but I have not been able to figure out the DLookup function. Could you please give me a VERY simple explination of how to use this function and it's expressions?
If it will help, we can use the following senario...
Table_Special_Needs
Special_Need_ID
Special_Need (Data Includes: Initiatives, High Ropes, etc.)
Standard_Price
Table_Event
Event_ID
Initiatives
High_Ropes
Extra_Linens
I would like to be able to add a price field for each item in the Event table and have the table lookup the price from the Special Needs table.
Many thanks for any help you can give.
t
:confused:
View 2 Replies
View Related
Oct 19, 2007
I would like to know if i am doing an Inventory in an Invoice Program and i want the [quantity] from a subform on the Orders Form to be deleted automatically from a Products Table , Would the Dlookup Function Help and if yes how would i implement it ?
Regards Dups
View 2 Replies
View Related
Feb 18, 2008
Dear all,
Firstly, thank you all for allowing me into your group!
Secondly however, PLEASE HELP!
I've created a database for storing students details and exam records for my university department, and everthing is fine except for one final problem: In order to allow final grades to be calculated all student marks have to be stored numerically, but each number corresponds to a code: eg N=0, G2=1, G1=2 .... A1=22
I'm trying to get the student marks report to display not just the numeric code, but also the final code eg, C2. To do so I've been trying to use DLookup as follows:
Expr1: =DLookUp("[Grade Code]","Lookup Table","[Score]= Forms![Total Grade]")
where "Grade Code" is a field in the Lookup Table (ie, it lists the A1, A2), "Lookup Table" is a table listing all the numbers and their corresponding codes, "Score" is the numeric score in the "Lookup Table" table, and "Total Grade" is a calculated (Numeric) field in the open report (though created in an accompanying query). I just can't get it to work however!! When I run the report, the column is just blank, and nothing I do seems to be able to get the corresponding code (Eg, B1, B2) to appear on the report!
Please help me if you can, I'm not kidding when I say my job is on the line with getting this database to work! Once I've got this sorted, all I need to do is find a way of letting the secretaries input the alphanumeric code, but the database automatically stores that as a number. I figured however, that sorting the first problem will go some way to finding out how to sort the 2nd one!
Thank you all just for being out there!
View 3 Replies
View Related
Oct 12, 2007
Do the Access functions dlookup, etc. cause performance issues when using remote SQL servers where the data is held?
View 2 Replies
View Related
Nov 17, 2007
Dear all,
How are you?
I need your help.
Can you help me in this DLOOKUP function.
I want to retreive "covered period" from financial statement table where investment id = the value of investment ID from Financial statement form.
This is the syntax:
x: DLookUp("[PeriodCovered]","Financial Statements","[InvestmentID] = " & Forms!Investments!InvestmentID)
Kidly,find attached the DB.
Thanks in advance.
View 5 Replies
View Related
Nov 21, 2007
Hi,
I am trying to use a dlookup to return the price of an item that meets two criteria. This is what I came up with, but it doesn't work. The part after the AND works by itself, but when I combine the two I either get a #Error or no values that meet the criteria.
Any suggestions? I can provide more info if needed.
Expr3: DLookUp("[Crt plan price]/1000","CPC List","[Plant] = ' " IIf(IsNumeric(Right([Date Code],2)),"Unk",Right([Date Code],2)) ' " And [Material] = " & [Item])
Thanks!
View 2 Replies
View Related
Feb 16, 2008
Hi all
I am trying to set a criteria in a DLookup and I don’t know if it’s possible. I’ve managed to get a result using the following code in a form but it only matches an exact date.
strValue = DLookup("Price", "Price List", "ServiceItemID = " & "ServiceItemID" & " and CStr([ContractStartDate]) ='" & CStr([Date]) & "'").
What I really need is to be able to do a between criteria on the [Date] field and check out whether that fits between [ContractStartDate] and [ContractEndDate] and then return the Price according to the [Date].
I actually want to run it as a query but have no idea how to do this. So, can a "between criteria" be put into a DLookup? and can it all be put into a select query? If so how? If not, what better way it acheive the desired outcome?:confused:
Thanks
View 14 Replies
View Related
Apr 8, 2005
I need to fix my form (ie survey) so that a user can only scroll through the current page and not to another record using the mouse scroll button.
I'm having trouble using the Dlookup function.
Please help
View 5 Replies
View Related
Jun 2, 2005
I've got a dlookup problem, i'm working on an adp recreating from an mdb. I've got a main form called contract_sessons and then a sub form called session_contracts and a sub form in that called consessions. In the consessions this holds ths dates for each session what i'm trying to do is lookup the period for the date in the table called dates. The current code I'm using in an unbound field is =DLookUp("[Period]","[dates]","[date]=Forms![Contract_Sessions].Form![Session_Contracts].Form![conSessions].Form![Date]") but all that appears is #error.
Anyone help
View 2 Replies
View Related
Jul 6, 2005
I know there are lots of questions relating to this function but I ahve searched and cannot find an answer. Can I add a Dlookup to a field that calculates a sum of the fields based on todays date? I am not very good a coding so any help welcome. I guess I am asking if Dlookup can be combined with Sum and Date()
View 2 Replies
View Related
Jul 7, 2005
Question:
I have two unrelated tables
Table 1 :
Name
Phone
Email
Table 2 :
Contains a number of fields but the mains fields I'm referencing are :
Requestor
Requestor Phone
Requestor Email
Form is based on the second table: I want to able to chose a requestor and have Requestor Phone and Email Populate based on the info from first table.
Please help, I hope this is not a stupid question.
http://www.elysiumlightproductions.com/image-files/requestor.jpg
View 5 Replies
View Related
Aug 17, 2005
I'm not familiar with using dlookup and I'm having some problems.
I have a form with a project number. the project number relates back to a table that has organizations and project number -the table name is Project
I need the organization name to appear on the form. The where clause of the Dlookup is where I'm stumpped
DLookUp("[Organization]","Project"," ???? ")
Thanks
View 1 Replies
View Related
Jun 15, 2006
I've been reading and reading all previous posts and have yet to find anything related to what I need help with.
I've done this before through help from my dad, so I know it's possible. What I basically would like to do is show a list all records that start with a letter picked from a combo box. I.e. combo box letter selected is g, a list box on the form will show all records on my 1 table that start with the letter g.
there's a way to use dlookup (i believe), to show this by taking the first letter from all the fields and comparing them to the letter listed in the combo box. but I'm completely lost in how to do it.
View 1 Replies
View Related
Aug 20, 2006
Im having a strange problem with my dlookup.
heres the code:-
Me.description = DLookup("[description]", "table2", "[partno] = '" & Me.PartNo & "'")
basically this is in the afterupdate of the partNo text box and when i enter a part no it brings up the description of the first record only no matter what part no i enter.Can anyone see a problem with the code.
View 11 Replies
View Related
Aug 31, 2006
I am working on a volunteer tracking system for a haunted house I work at.
I have a table of Shifts, with fields ShiftID, Shift Name, Date, and Hours. On my sign in form, I have a subform that records who works, what spot they are in, and I want to have the number of hours that is linked to that date transfer to a textbox on this subform.
Need some help with the DLookup function.
Thanks!
ScrmingWhisprs
View 3 Replies
View Related
Oct 20, 2006
I have a query that outputs to a text box. There are multiple records in the query but it is only outputting one record. My code is as follows
Private Sub Command30_Click()
CUSTOMERS.Value = DLookup("[Account] & ' ' & '(' & [VAR] & ')' ", "Customer Variance")
End Sub
How could I make ALL the records show up in the textbox one underneath the other? Or separated by a comma?
View 3 Replies
View Related
