DLookup On A Field
Aug 29, 2005
In a previous database i used the dlookup function as the conrol source for an unbound text box.
it looked like this:
=DLookUp("[Name]","tbl_GP_Details","[GPNumber] =" & [Forms]![frmPatient_Dets]![GPNumber])
This populated the text box with the name from the gp details where the gp number was the same as the gp number on the form.
This worked fine but now i need to create something similar however instead of using a number to link them (GPNumber) the field is text this time, the above code does not work. Do i have to add anything else so it recognizes it to be a text field and not numeric??
Hope this makes sense
View Replies
ADVERTISEMENT
Jan 3, 2007
Thought this would be a good solution, but having trouble with the syntax.
=DLookUp("[StartDate]","[tblJobHistory]","Me.[EmployeeID] =" & tblJobHistory!EmployeeID & " AND Min([tblJobHistory]![StartDate])")
I am getting the dreaded #Name? can I use the Min command like this?
Any help appreciated:)
View 6 Replies
View Related
Mar 27, 2013
I have a report based on a parameter query. One of the parameters is TM Name. This parameter value is selected from a list box. The table on which the query is based also has TM Name as a look-up from a list. So ofcourse the ID is showing in the TM Name field on the report...not the actual name.
I am attempting to use a Dlookup to show the actual name than I will hide the field with the ID. Here's what I have as the control source:
=DLookUp("Name","TM_Names","[TM]=" & [Reports]![Report_TM_Records]![TM])
It is pulling in the value of the first row in the "Name" column of the "TM_Names" table. Doesn't seem to be looking at the value in the TM field on the reports.
My gut feeling is the report needs to be "saved" or "refreshed". I have tried a few macros to do this but I get errors.
View 1 Replies
View Related
Apr 11, 2014
I have table [table1] which have 4 fields
EMPID - primary key
EMPFirstname
EMPLastname
EMPDOB
Input box as txtempid as string
I am trying to learn how I to display other field based on one field in my case last three field based on EMPID
I am actually getting syntax error when I dry to display DATE Field
Following are my attempts
Dim Verfirstname as string
Dim Verlastname as string
Dim VerDOB as date
Verfirstname = dlookup("[EMPfirstname]","table1","[EMPID]='" & me.txtempid & "'")
Msgbox Verfirstname
works fine. But when I use for Date, I am getting syntax error
VerDOB = dlookup("[EMPDOB]","table1","[EMPID]='" & me.txtempid & "'")
Msgbox VerDOB ---- now I am getting syntax error
I tried to understand over net and I believe i need to used with "#" before and after the date field but my criteria...
View 1 Replies
View Related
Jun 6, 2014
I'm designing a query. It's very simple.
I just need to compute something like:
Var: TT[YYZ for this month]-TT[YYZ for last month]
The table is called C1 and has the fields i'm interested
YYZ: Location
TT: number
mxx: date
So, i have for YYZ a list of places and for TT a list of values. MXX has the date of the record on the table.
There is one record per date, which it means, there only one record for 1/1/1991 for the YYZ=2.
NF should calculate the difference between the the record on t against t-1.
I've tried with this
Var: YYZ-DLookUp("yyz","[C1]"," "[YYZ]=[YYZ]" AND "[mxx]=DateAdd("m", "-1", "[mxx]"))
But it fails.
I upload a xls sheet with the desired field, Var ,calculated.
View 2 Replies
View Related
Nov 14, 2014
I have a small table, that only contains one record (and should only ever contain one record, which simply gets edited to suit once in a while).The table is called OtherRates, and the fields are as follows:
WRD_Sat/Sun/BH
WRD_Mon-Fri
AHDifferentia
SKDifferentia
Sat/Sun/BH_OTRate
Mon-Fri_OTRate
I am trying to use DLookup to return a value from the relevant field in this table, however the field to look in is a variable.The code (below) that I have tried returns an error and highlights the DLookup line,.
Code:
Dim WRD
Dim DayType
[code]....
View 3 Replies
View Related
Aug 10, 2014
I have a field on a form that is the results of a dlookup to a table.field. I want the value to be updated when I execute a button that is running a macro. How do I do this automatically, and not have to hit the refresh button?
View 5 Replies
View Related
Jun 13, 2013
I am trying to create a form with multiple combo boxes where users can select fields from my main database and click a button to generate a report based on their selections. I think I am supposed to create a generic report with perhaps 4 fields where I would link the four combo boxes to. The issue is I cant seem to get the selection of the combo box to change the field that the report should pull from the database. I think I am supposed to use dlookup in the reports control source for each field, but I cant seem to get it to work.
View 14 Replies
View Related
Oct 30, 2013
I have a field created in a query expression
BirthMon: Format([DOB],"mmmm")
I would like to create another field in the query using DLookup to return the value of the BrithMon.
BrithMonthID=DLookUp("MonID","tblListMons","BirthM on = Mon")
This works good if used with an unbound text box on a form, but when entered into a query expressions, an error is returned: cannot find the name 'BirthMon'
Can I used DLookUp in a Query expression to refer to another Query created field?
View 2 Replies
View Related
Jan 8, 2013
I am new to Access (using Access 2012) and All I am trying to achieve is to autofill the field [Frequency] from Table FullList based on the value of [Frequency] from Table Courses using a DLookup code used to update the FullList form. The code is not updating anything! It is frustrating! Frequency is a number (integer) and while Course is a text. The figures of my Access database are below.
View 2 Replies
View Related
May 16, 2014
My intentions are to get my combo box to display data into a field in my form, when it is changed/selection is made. So far my form has the following:
Combobox: CBOCharacters
Field: CharacterGender
Both of these are in the same table; tbl_Characters. The data type for CBOCharacters is a number and its called CharacterID in the table.
So far I have created this piece of code:
Private Sub CBOCharacters_AfterUpdate()
Me.CharacterGender = DLookup("CharacterGender", "qry_StillNeeded", "[CharacterID]= " & Me.CBOCharacters)
End Sub
When I run this event procedure i get an error:
Runtime error '2471'
The expression you entered as a query parameter produced this error '[CharacterID]'
View 3 Replies
View Related
Oct 20, 2014
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.
View 2 Replies
View Related
Sep 22, 2013
I have a combo box in a sub form with three values, rate1, rate2, and rate3. I have another three fields in the sub form rate1, rate2, and rate3. The rates are dependent on the item. When I select the rate from the combo box I want a cost field to update on change to the relevant rate. I tried this to no avail:
Code:
txtCost = DLookup(cboRate.Value, "tblItem", "ItemID=" & ItemID)
View 5 Replies
View Related
Feb 25, 2015
I have a small problem with dlookup multiple criteria. Vba code looks like this:
Code:
Label34.Caption = DLookup("[Spent_Hours]", "249_1_CHours", "[Date_Added]= " & Me.Text27 & " And [Shift] = '" & Me.Text29 & "'")
This gives following error:
Syntax error in number in query expression '[Date_Added]=4.02.2015 And [Shift] = '2'.
[Shift] column is a numeric field.
View 14 Replies
View Related
May 28, 2014
I have a table that consist of pricing for different types of products:
Table name: Costs
ID TYPE EastNew EastRenew WestNew WestRenew
1 500 4100 1000 4400 900
2 501 4100 1000 4400 900
3 600 3400 900 4600 1200
4 700 3700 1300 4900 3300
I have a form that consist of many fields that the user needs to manually input data. 3 fields on that form are the criteria i need to determine which pricing to use back on the table. The first field, which name is LTYPE, is a combobox that the user needs to choose as its type (i.e 500,501,600,700). The second field, which name is EastCoast, is a checkbox (Yes/No) that tells me if it's East Coast (East = yes and West = No). The third field, which name is NewRenewal, is a combobox that the user chooses NEW or Renewal).
Example 1, if the user chooses type 501, checkbox is YES for East, Product is NEW. Then the pricing is $4,100
Example 2, if the user chooses type 700, checkbox is unchecked for NO (which is West), Product is Renewal. Then pricing is $3,300
I would like the unbound field to auto determine the pricing based on the 3 criterias chosen. I read around the forums that Dlookup would be the function for this, however, i tried many times but it did not work.
View 6 Replies
View Related
Dec 13, 2006
I'm fairly new to setting up Access databases. Been learning a lot. Ran into a snag that I'm hoping you can help me with.
I am using a form to display text labels and running statistics for our jail.
The form, "Complete_Worksheet" gets its data from a query, "qryComplete_Worksheet". Fields that make these up are:
ID (key)
Standard (text)
Outcome Measure (text)
Numerator/Denominator (text)
CalcValue (number)
ID, Standard, Outcome Measure, and Numerator/Denominator are queried from a table, "tblComplete_Worksheet". CalcValue comes from another query, "qryDataSelect". My problem is this:
In the final calculated statistic, some records are numerators and some are denominators. I need one more field, which I have been trying to create as a calculated control on my form which will divide each numerator CalcValue value by the denominator CalcValue value following it. Since these values change constantly as new data is entered into another data table, the calculated control needs to also change with them. I have been attempting to do this using the following expression:
=DLookUp("[CalcValue]","Complete_Worksheet", "[ID]="&"[ID]-1"/[ID]=")
I keep getting a #Error message in the control in form view. When I run the expression in the Immediate window, I get a Compile Error:
Expected: line number or label or statement or end of statement
Any suggestions on how to make this work?
View 9 Replies
View Related
Apr 15, 2014
I'm trying to pull a date from a table into a text box on a form tab control using DLookup and I just can't figure out what is wrong with my DLookup expression:
=DLookUp("DateOrdered","tDateOrdered","PrNumber=" & [PrNumber]) ----- (DateOrdered is short date, PrNumber is text, db is split Access 2013)
Whats missing in this expression? I've tried every criteria variant I could find but to no avail.
View 9 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