DLookup - Slows Database Down
Jan 27, 2005
Hello All
I have created a Dlookup inside a text box that dependant on a order number selected from a combo box
it will search a table and bring up the supplier
The Lookup is as follows: =DLookUp("[Suppl]","AMICOS_PUORDER","[LIORDERNR]=[AMICOS_PUORDER]![OrderNR] ")
This works fine but has slowed the database up big style.!
The reason for this appears to be, the lookup is searching all the time even if no order has been selected from the combo box in the current record.
If there a way to have the Dlookup only search when the combo box is not null?
View Replies
ADVERTISEMENT
Jul 30, 2015
I have my main linked table VI, and 4 of VI's fields are to be filtered in a query.
To do so, I have 4 very very tiny local tables (less than 20 records) called respectively "soff", "skus", "warr" and "typ", in which the user will only put what he wants in the result, and by doing Inner Joins, I filter it easily.
Problem is, doing more than 1 Inner Join slow the query so slow it's unbelievable.
If I run the query:
Code:
SELECT VI.*
FROM VI INNER JOIN SKUS ON VI.Sku= SKUS.Sku;
It runs in 0.7 seconds. It's about the same if I replace SKU by SOFF, WARR, or TYP, the query takes about 0.5 to 1.5 sec.
Now if I run:
SELECT VI.*
FROM (VI INNER JOIN SKUS ON VI.Sku= SKUS.Sku)
INNER JOIN SOFF ON VI.SOff = SOFF.Soff;
It takes either 15 seconds, or up to 100 seconds.
I tried by doing 4 successive queries, it's the same. The 1st query runs well, and then it grinds to a near-halt.
Running either the 4 queries or a query with the 4 inner joins takes me about 200 seconds, sometimes 400-500.
It's not even a hard query, VI has only almost 1mil rows, and the fields are indexed. The result is 800 rows.
If each join on VI takes 1 second, it should do 1sec+1sec+1sec+1sec, taking in account the fact that the left side of the join grows smaller at each step, it should even do something like 1+0.8+0.5+0.2, or something. Why does having these joins together, or follow each other, make things so damn slow?
View 7 Replies
View Related
Jul 29, 2013
I have an asset database I am designing to manage our computer inventory and assets.
I am trying to get a DLookup to work with one of my forms that will auto-populate some of the fields depending on what is entered in to the ProductID field. For instance, Make, Model, Asset type...
My problem is that the string that returns contains special characters, specifically "#" and gives me the error message -
Run-time error '3075':
Syntax error in date in query expression 'productID=EN371UA#ABA'.
My expression is definitely working, it just looks like it things it has something to do with date/time which it does not. Unfortunately, most HP equipment contains a # in the Product ID number.
Here is my expression -
Private Sub ProductIDCombo_AfterUpdate()
Make = DLookup("Make", "productlist", "productID=" & [ProductIDCombo])
End Sub
Make is the field I am looking up from the ProductList table. The Product ID is the ID I'm looking up from the ProductList table to find the make. My problem is actually getting it to return the correct value of "HP or Dell or Lenovo". etc.
View 12 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
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
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
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
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
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
Aug 17, 2005
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
View 1 Replies
View Related
Jun 15, 2006
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.
View 1 Replies
View Related
Aug 20, 2006
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.
View 11 Replies
View Related
Aug 31, 2006
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
View 3 Replies
View Related
Oct 20, 2006
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?
View 3 Replies
View Related
Jul 10, 2006
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.
View 2 Replies
View Related
Sep 18, 2006
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
View 4 Replies
View Related
Oct 11, 2006
Hi,
Can I retrive an autonumber field from a database using a DLookUp function?? if so how??
View 5 Replies
View Related
Oct 29, 2006
I'm trying to create a report that shows room assignments using the DLookUp function.
I have a query that gives me the information I need. How do I write the DLookUp function?
I want the person's name that is assigned to a particular room:
DLookUP("[Name]", "qryName", [Building]="24" AND [RoomLookup]="1")
I get an error! in the report when I format it as above.
Thanks!
View 4 Replies
View Related
