Hi everyone,
I have a form that I use to enter information onto a table.
Lately I have noticed that there are few duplicate names been entered into the db.
The form has 2 txtboxes for the persons name. The table also has the 2 fields.
How can I make a Dlookup check both fields before allowing the new entry?
Thanks for your help.
Hello,
I have an inquiry about using dlookup to retrive all fields from a table that are using the same criteria. Here's the table structure
Table1:
ID (Autonumber)
Number
Code
Grade
I have the following data:
Number Code Grade
4 15 68
4 52 78
4 17 69
5 85 83
5 28 17
I am using this to retrive values for number 4 but I only get the first row
DLookup("Code", "Table1", "[Number] = 4")
DLookup("Grade", "Table1", "[Number] = 4")
I get only as a result 15 and 68, how can I retrive all the rows that have Number 4?
Thanks in advance for your help
I am trying to Dlookup two fields with dates in them. If the Dlookup returns that today is in the range (e.g. 06-03-2015 in the first field and 10-03-2015 in the second) then i want the system to display a messagebox when im opening up a form. If today is not in the range then the messagebox should not pop up.
Code:
If DLookup("HollidayFrom", "tblBuildingManager", "[HollidayFrom] >= Date()") And DLookup("HollidayTill", "tblBuildingManager", "[HollidayTill] <= Date()") Then
msgBox "A building manager is on holliday"
end if
The table looks like this :
BuildingManager
ManagerID
Phone1
Phone2
Phone3
HollidayFrom
HollidayTill
George Bush
1
06-11111111
06-11111112
06-11111113
1-4-2015
9-4-2015
Bill Clinton
2
06-22222222
06-11111114
06-11111115
30-8-2015
19-9-2015
i have tblDates that has two number long integer fields named FiscalYear and FiscalMonth. then there is my field PerSeq is number long integer.
in my Query1, i have 2 fields that use a question in the field part of the qry like:
Desired FiscalYear: [Enter Fiscal Year] EG 2007
Desired FiscalMonth: [Enter Fiscal Month] EG 2
And now the Qry question. The field in the qry "Desired BaseSeq". i want this dlookup to get PerSeq out of tblDates based on my response to the Desired FiscalYear and Desired FiscalMonth.
I start it out here but it is wrong i know.
Desired BaseSeq: DLookUp("PerSeq","tblDates","[Desired FiscalYear] And [Desired FiscalMonth]=" & [tblDates].[FiscalYear] And [tblDates].[FiscalMonth])
Thanks you.
DLookup function. (this is for a stamp collection database).
On my form ("InventoryInput") I have a text box called "Catalog" for a numeric entry and a text combo box for selecting a "Country" in drop down list.
I want to query a table called "CatNameList" to get the "StampName" of the item (based on the entries of Catalog and Country) and populate that name in the text box. The fields in this table are called "StampName", "CatNumb" & "CName" respectively.
I have successfully placed the following expression in the control source of that textbox and able to populate the StampName I need based solely on the catalog number alone.
That express is :
=DLookUp("StampName", "CatNameList", "CatNumb = Form![Catalog]")
So it will populate the "StampName" data to match the "Catalog" number entry just fine.
However, I need to add a second layer to incorporate the Country.
Example : There is a catalog "1" for "USA", and a catalog "1" for "Canada" but both have different "StampName".
I have been attempting to get that second piece added with no success. Here is the expression I have been trying to get to work :
=DLookUp("StampName", "CatNameList", "[CatNumb] = " & [Catalog] & " And CName = '" & [Country] & "'")
Right now, the text box is just blank with the above expression. I thought it may be because there was no match found, but I have triple checked to ensure I have the spelling correct on the country name in both places.
Basically, I just need the dlookup to take the "catalog" and "country" off the form and match it to the "CatNameList" table fields of "CatNumb" and "CName" to give me "StampName" field back on the form.
Having problems getting dlookup to work in the control source field of a text box.
My form has fields : Catalog # (numeric value) and Country (drop down text selection).
I would like to query a table CatNameList for a name (text) if the catalog # and country find a match on the table.
My field names on the CatNameList table are : Name, Number (to validate against the Catalog # entered on the form) and CName (to validate against the Country drop down on the form).
I am successfully able to populate the name from the CatNameList table on my form using lookup of the catalog # using this :
=DLookUp("Name","CatNameList","Number = Form![Catalog #]")
However, I will eventually have several catalog numbers that will be identical in the table CatNameList, thus why the country is important as the second criteria to be added into the dlookup.
I have tried for a few hours unsuccessfully to add the second portion to my dlookup.
This is what I have currently (not working) that I have been playing with, I'm sure I'm missing a quote mark, & or something simple.
=DLookUp("Name", "CatNameList", "Number = Form![Catalog #] And CName = ‘”& Form![Country] & ”’”)
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 dont know if its possible. Ive 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.