I have been working on this problem for about the last hour, and have gotten absolutely no-where - I cannot figure out what is wrong with my DLOOKUP.
I am using it in a query, as part of an IIf statement, but to simplify things whilst testing, I have just bunged the dlookup into a text box on a form, but itself.
The code I am using is:
=DLookUp("[SHORTNAME]","CPL_NOMINEE","[FULLNAME]=" Like "*" & "[AccID]")
What I am trying to do is return the SHORTNAME, from a table called "CPL_NOMINEE", which is a linked table (to Oracle), where the field FULLNAME has the value of ACCID at the end.
An example of each field would be:
SHORTNAME: CNL
FULLNAME: H Noms Ltd 500222
ACCID: 500222
With the code above, I just get a blank box - as if it can't find anything in the dlookup, but I can SEE the data is there in the table - what's up?!?
Am currently having trouble with a DLookup function as follows
Table ======
Products ProductID Description Nett
I have a form (Purchase Order Detail Subform) where by I want to enter the Product ID manually and then for the Description and Nett fields to populate automatically I've started on the description field: =DLookUp("[Description]","Products","[ProductID] =" & [Forms]![Purchase Order Detail Subform]![ProductID]) but this isn't right, being a bit of a newbie to access its probably because I've done something really silly... any idea? Thanks
I have a table called "ShrinkWrapBinding" where I am supposed to look up a "Price" from. As you see in the code below, I calculate a "totalPackages" number which comes from values in a form. My problem is that when I try to use this value (totalPackages) on the DLookUp function (as seen in this code) it gives me a syntax error exactly on the totalPackages variable. Please note that I added single quotes to the 'totalPackages' variable when I use it on the DLookUp function, and have tried every possible combination (single quotes, double quotes) but still doesn't work. What is it that I'm doing wrong?
Code: Dim totalPackages As Integer Dim tempPrice As Double
I made a form for a table which contains client questionnaire responses, "Dec4Docs". I wanted to include the names of these clients in the form, just to make everything easier for whoever is doing data entry, but client names are in a different table, "BasicInformation". Both the questionnaire table and the client information table use a common ID number, "WISnum". I found a number of online tutorials which instructed me to use the DLookUp function for what I'm doing.This site here, for example, suggests the following formula:
^This was meant to find FirstName in the BasicInformation table where WISnum is equal to the WisnumField of the Dec4DocsForm form. Instead, all I got was a flashing error message that partially broke the program's GUI. I could not find a solution on Google, so I played around for awhile, bug checking by adding and removing things to the formula, until I finally got a little lucky and figured this out:
I wonder if you can help me. In my query, for this calculated field, i get an error message about a syntax error missing operator in query expression. what did i do wrong? thanks
The basic idea is that I need to insert a record into a table and then return the Key field so that I can use it to populate another table. This was working just fine for a while, then it stopped and naturally I can't think of anything I did to make it not work. I get an Invalid Use of Null error at the bolded step.
Dim SQL As String Dim SpecID As Long Dim VerifySpec As Variant ' Check for empty strings If Len(Me.txtNewPartNumber & vbNullString) = 0 Then Response = MsgBox("Part Number cannot be blank.", vbExclamation, "Missing Data") Exit Sub End If
I have a subform that has the Expense Code from the Expense Codes Table and I want to look up the Description (yes I know its not correctly spelled in the formula but it is in the table)
I am trying to check for when a user trys to enter a duplicate number.
The control that I am checking is in a subform on the main form:
Main: frmCandidateInfo Sub: frmTestInfo
Control on the subform is: RankOrder
I am trying to check the control entry against the table entry:
Table: tblTestEvents Field: RankOrder
Here is what I have:
Code: Private Sub RankOrder_BeforeUpdate(Cancel As Integer) Dim lngRankDup As Long lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & Forms!frmCandidateInfo!sfTestInfo!Form!RankOrder, 0)) If lngRankDup <> 0 Then MsgBox TestEventID & " already exists in the database" End If End Sub
I know that the error is due to the argument not being correct, but I am not sure how to fix it.
I have been trying to find a solution to why I can't get a Dlookup with multiple criteria to return the value I need.
Essentially I am trying to use an Order Number to find the item number which is contained within another table. However the order number has multiple lines (suffixes) which alter the item number. Therefore I am trying to have the item number be populated by the correct 'combination' of Order Number and line ("suffix").
I have managed to use the Dlookup in the after update of each box of the form separately and they retrieve values in the table correctly:
Afterupdate of main order number:
Code:
Private Sub OrderNumbertxt_AfterUpdate() ItemNumbertxt = DLookup("item", "dbo_job", "[job] = '" & Forms![**INPUT]![OrderNumbertxt] & "'") End Sub
Afterupdate of suffix:
Code:
Private Sub SuffixTxt_AfterUpdate() ItemNumbertxt = DLookup("item", "dbo_job", "[suffix] = Forms![**INPUT]![SuffixTxt]") End Sub
However when I combine them as follows in the afterupdate of the Suffix box I receive a "Run-time error '13': Type mismatch"
Code:
Private Sub SuffixTxt_AfterUpdate() ItemNumbertxt = DLookup("item", "dbo_job", ("[suffix] = Forms![**INPUT]![SuffixTxt]") And ("[job] = '" & Forms![**INPUT]![OrderNumbertxt] & "'")) End Sub
I think the reason is something to do with some being numbers and some being a combination of text and numbers (based on the replies of other topics), but have been trying to modify these slightly and can't get it to work still.
Also the Order Number is a combination of letters and numbers (normally in the form of AB12345678), the suffix is just a number between 0-9 and the Item number it finds is a combination of numbers and letters.
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
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
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.
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
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 ?
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:
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!
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)
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])
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.
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:
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.
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()
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
