Hello Everyone!,
A quick question to you folks...
What would be the correct DLookup syntax to perform the following operation:
I need to bring in a value from a field named "Description" from a table called "PNs and Desc". The "Description" should be relative to another field named "PN" (located in another table).
Any ideas?
Thnx in advance!
I have a report that when run, prompts a user for an id. The user enters and id and it pulls the information based on that id. The information is being pulled from data stored in Table A. I also have Table B that associates an id with a name. In my report I have the input being stored as a string, called strCampusId. I have a text box with called txtCampusMinistryName. I want the report to do a lookup based on the users input and display the CampusId Name, however I am getting an error. The error says, RunTime Error 3075, Syntax error (missing operator) in query expression 'ParishId=*0101'.
Table B (called CampusMinistry) only has 2 columns, ParishId and ParishName
Here is my code that I am trying to use.
txtCampusMinistryName = DLookup("ParishName", "CampusMinistry", "ParishId=" & strCampusId)
Thanks for any and all help.
I have a textbox on a report that I wish to populate based on the value that is in another textbox/field on the report, and I thought DLookup was the way to go - however, I cannot seem to get it to work.
I have a table (ComplaintsResponses) that has two fields, both text
[ShortDescription]
[ResponseText]
The text from [ShortDescription] is saved in a field on another table that contains all the other relevant information that is used in the report, and whilst this short description is mostly fine, I have one report where I need the data from the larger [ResponseText] field.
I have tried the following code:
Code:
=DLookUp("[ResponseText]","[ComplaintsResponses]","[ShortDescription='" & [Reports]![PublicComplaintsArea]![txtSAPCRMResponse] & "'")
and
Code:
=DLookUp("[ResponseText]","[ComplaintsResponses]","[ShortDescription='" & [SAPCRMResponse] & "'")
Both of which return a #Error in the text box.
The field that contains the text that is used for the lookup is SAPCRMResponse, and the textbox on the report itself is called txtSAPCRMResponse.
I am working with a database that I downloaded and am trying to modify to fit my needs.
This is an inventory database. The products table contains a description and pricing. I want the description and pricing to populate in the Purchase Order form, so I added Dlookup fields in the Purchase Order form. I was happy.
However, the pricing information is not populating to my Inventory Transactions Table from the Purchase Order form by way of this Dlookup feature, and therefore will not show on my report, and in turn does not show in my Total of my Purchase Order report.
As a work around, I tried creating a calculation in the purchase order report, of =[UnitsOrdered]*[Products.UnitPrice], and the pricing totals show fine on my report, but the subtotal doesn't work.
I was unable to upload my file...so a few notes of info...
There are no queries set up in the database for this report.
I had tried a sorting grouping thing (in the Report) by Subtotal, but now can't get rid of it.
When I show the field list for the report, across the top of the window reads:
SELECT DISTINCTROW Employees.*, Products.*, [Inventory Transactions].*, [Purchase ORders].*, Suppliers.*, nz([Inventory Transact
Looks like it runs out of space
I am trying to attach a couple of images to support my comments.
Since this issue crosses both reports and forms (and tables!), I am not sure where to properly post. The end result I am looking for is on my report.
I am using Access 2003...
I have a report with quite a few subreports in it. There are a number of calculated fields on the form, most of which use Dlookup to retrieve at least one of the figures required for the calculation. The Dlookup runs fine and the report opens but the calculated fields are devoid of data in Report view. When I switch to Print Preview view the fields are now populated. Below are two variations of the DLookup syntax I have used to try and alleviate this issue.
=DLookUp("[8]","qry_MonthlyTotalsByYearFirstAid","[ActivityType] = 'First Aid Injury (FAC) Reported in Safeguard'")/[sub_AllHours].[Report].[8]
=DLookUp("[8]","qry_MonthlyTotalsByYearFirstAid","[ActivityType] = 'First Aid Injury (FAC) Reported in Safeguard'")/[Reports]![rpt_AllFigures]![sub_AllHours]![8]
Note that the field [8] specified here is simply a month number and forms a column in the crosstab query for the corresponding query name.
I would add the query referred to in the DLookup to the source query for the report but the source report's data is derived from a Crosstab query, which only accepts one data field (Access terms this as the value field.
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
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
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
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:
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
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!
Do the Access functions dlookup, etc. cause performance issues when using remote SQL servers where the data is held?
View 2 Replies View RelatedDear 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.
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!
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
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
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
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 RelatedQuestion:
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
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
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.
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.
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
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?
I would like to automatically fill in a persons initials if they receive a referral and if it is located on a particular unit.
I already use a form having a unit text box and whether it is a physical therapist or occupational therapist. The referral text box just takes a number.
Right now I have code that says, if referral is more than 0, and the Unit is ???, then therapist = xxxx.
There are 14 units, shared by 21 therapists.
Would DLookup be good, and if so, How do I go about it.
I have a Referral Table, and a UnitAssignment table.
Hi all
I'm using an ADO event to update or add records
I'd like to check records in a table using dlookup.
Effectively I want o scan the table dataPhotos to check if the value EQID is present in the table. If it is ... I want to update. If it isn't I'd like to add the record. Below is the code I'm using. I'm doing something wrong.
Any ideas are appreciated.
Private Sub cmdAdd_Click()
Dim ADOrs As ADODB.Recordset
Dim sSQL As String
Dim Photo As Variant
Photo = DLookup("[EQID]", "dataPhotos") = Me.cboStudentList
If Photo = Me.cboStudentList Then
sSQL = "Select * from dataPhotos where dataPhotos.EQID = '" & cboStudentList & "';"
Set ADOrs = New ADODB.Recordset
ADOrs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
With ADOrs
.Fields("Intranet") = Me.chkIntranet
.Fields("Internet") = Me.chkInternet
.Fields("SPub") = Me.chkSPub
.Fields("GMed") = Me.chkGMed
.Fields("IUAagreement") = Me.chkIUA
.Fields("Comment") = Me.txtComment
.Update
End With
Set ADOrs = Nothing
MsgBox Me.txtStudentName & " Edited"
Else
sSQL = "dataPhotos"
Set ADOrs = New ADODB.Recordset
ADOrs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
With ADOrs
.AddNew
.Fields("EQID") = Me.cboStudentList
.Fields("Intranet") = Me.chkIntranet
.Fields("Internet") = Me.chkInternet
.Fields("SPub") = Me.chkSPub
.Fields("GMed") = Me.chkGMed
.Fields("IUAgreement") = Me.chkIUA
.Fields("Comment") = Me.txtComment
.Update
End With
Set ADOrs = Nothing
MsgBox Me.txtStudentName & " Added"
End If
End Sub