DLOOKUP #Error

Aug 23, 2006

Hello all,

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?!?

Any help much appreciated!

View Replies


ADVERTISEMENT

DLookup #Error

Aug 4, 2005

Hi all

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

View 3 Replies View Related

DLookUp Syntax Error Help

Mar 26, 2008

Hello,

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

totalPackages = Me.QuantityBooks1 / Me.ShrinkWrapQty

tempPrice = DLookup("[ShrinkWrapBinding]! [Price]", "ShrinkWrapBinding", "'totalPackages' >= [ShrinkWrapBinding]![RangeField]")


Thank you

View 8 Replies View Related

DLookUp Flashing Error

Dec 16, 2014

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:

=DLookup("CustomerID", "Orders", "OrderID = " & Forms![Orders]!OrderID)

Which I adapted to:

=DLookUp("FirstName","BasicInformation","WISnum=" & [Forms]![Dec4DocsForm]![WISnumField])

^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:

=DLookUp("FirstName","BasicInformation","WISnum=" & '[Forms]![Dec4DocsForm]![WISnumField]')

View 1 Replies View Related

Syntax Error In My Dlookup Inside Qry

Dec 22, 2007

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

FormulaID is a text field.

UnitRM Combined: nz(DLookUp("UnitRM Combined","QryFormulaSummed","[QryFormulaSummed].[FormulaID]='" & tblCostJunction.FormulaID & "'"))

View 5 Replies View Related

Dlookup Type Mismatch Error

Feb 15, 2005

Can someone please help with the following:

Dim strX As Variant

strX = DLookup("State", "TblScheduleK12004", "[PartnerID] = '" & Forms!FrmPrintRpt!cboClient & "'" And "[SetNum] = " & Forms!frmPrintRpt!txtSet)

[PartnerID] is a text field
[SetNum] = is a number field

I can get it to work with the Partner ID OR the SetNum but keep getting the error when I add the "AND" to include both.

View 2 Replies View Related

Modules & VBA :: DLookup Compile Error

Jun 23, 2014

why I am getting "Compile Error: Expected: =" here:

Code:
Private Sub Form_Load()
MeText9 = DLookup("[Along_line_spacing]", "Performance_Requirements_Defaults_Table")

when this:

Code:
Private Sub Form_Load()
Me.charttypestatic = DLookup("[Static_Chart_Type]", "Project_Defaults")

works??The "Performance_Requirements_Defaults_Table" table is linked, the "Project_Defaults" table is not.

View 7 Replies View Related

Modules & VBA :: DLookup Gives Invalid Use Of Null Error

Aug 11, 2014

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

[code]....

View 6 Replies View Related

Forms :: DLookup Not Working In Subform - Keep Getting Error

Apr 23, 2014

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)

=DLookUp("Expense Discription","Expense Codes","Expense Code=" & [ExpCombo])

But I keep getting "error" returned

I have double checked spelling field names etc...

View 6 Replies View Related

Modules & VBA :: DLookup Error - Wrong Number Of Arguments

Jul 1, 2013

Access 2010

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 tried:

Code:
lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & Me.RankOrder, 0))

Code:
lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & [RankOrder], 0))

Code:
lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & Forms!sfTestInfo!Form!RankOrder, 0))

But nothing seems to work.

View 3 Replies View Related

Modules & VBA :: DLookup Data Type Mismatch Error

May 26, 2015

I am trying to get my VBA module to find the ID of a Member Number from a table using a dlookup but I keep getting a data type mismatch error,

Code:
Dim Answer As String
Dim varX As Variant
Dim rst As Recordset
Dim strSQL As Integer
Dim stWhereStr As String
Set db = CurrentDb()

[Code] .....

In the table the ID field is an automatic number and the Member Number is a short text field.

View 3 Replies View Related

Modules & VBA :: Multiple Criteria To Return Value - DLookup Error

Feb 11, 2014

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.

View 3 Replies View Related

DLookup Not Right

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

Dlookup's

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

How Do I Use DLookup???

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

DLookup

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

Dlookup ?

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

Dlookup

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

Dlookup

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

DLOOKUP Help

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

Dlookup

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

DLookup Help

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

Help Using DLookup

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

Dlookup

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

DLookup Again

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

Dlookup

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







Copyrights 2005-15 www.BigResource.com, All rights reserved