I am doing a Dlookup in a query based on the value of one table to look up the value of another field in a table based on two other fields in the second table.
Although my code is presently returning data it is not correct data. Any suggestions?
Test: IIf(IsNull([Attend_Prov_MMIS_Id_W039]) And Not IsNull([Attend_Prov_Lic_Num_3003_2]) And Not IsNull([Attend_Prov_Prof_Code_2165_5]),DLookUp("[Provider_Name]","tblAllProviders","tblAllProviders.[Provider_Lice_Num]='" & [Attend_Prov_Lic_Num_3003_2] & "'&" And "& " & "tblAllProviders.[Provider_Code]='" & [Attend_Prov_Prof_Code_2165_5] & "'"))
I have looked through various posts, but can't seem to find the scenario I am dealing with. I have an expense dtabase I am building. It links to a Capacity dB that contains all of our metrics, such as # of delinquent accounts, # of workable accounts, # calls made, etc. In the expense dB, I need to allocate the actual expenses from the previous month to a specific metric. I have a table that contains the expense ID, description, and the allocation metric. The allocation metric field is populated by a field list based on the table from the Capacity dB, so that the user can assign which metric is to be used in the calculation for that particular expense GLID (Unit Cost:[Expense]/[Allocation Metric]). I have tried to use the DLookup function, and it is looking in the right column based on the allocation metric, but it returns the first value, not the one based on the criteria I entered.
I have seen numerous comments on the fact that DLookup is slow and that I should just join a query in my query to acheive it, but how would I join a value in one table to a field on another?
Data is imported into my table name tblImportReg containing the above fields.
I have also created another table called tblConvImpReg and what I call a lookup table called tblAppCodes.
In the tblAppcodes table I have two fields as follows:
strProdCode strAppDescription
The strProdCode field contains a two character code which identifies the product code. i.e FF The strAppDescription field contains a description of the product for the product code i.e. Fridge Freezer
The tblConvImpReg table has fields exactly the same as the tblImportReg table.
I want to append the data from the tblImportReg table to the tblConvImpReg table, but within that append query I want it to look up the strAppDesc field in the tblAppCodes table and according to the contents of the strAppDesc field in the tblImportReg table and populate the correct Product Code into the strProdCode field in the ImportReg table on appending to the tblConvImpReg table.
I have tried writing the following and niether method has returned what I require [both returned nothing]
Hi everyone, I am trying to set up a dlookup function for a database for the charity foundation I work for and am not sure what is the best way to go about it. I'm trying to make my own dlookup and it's coming up with an error so I'm probably doing it wrong. dlookup's are a little above my level of ability. Anyway, I have a linked spreadsheet table that has a list of funding grants that have been received from us in the past by charities and I need to link the total of this spreadsheet to my database form for funding grant info. The idea is that whenever a charity applies for a funding grant the dlookup will show any previous funding they have received from us.
so my dlookup is: =DLookUp("[Total to date]","Total Funding Dispersed","[Name of Organisation] =")
There is a field called "Name of Organisation" in both my form and the spreadsheet I'm trying to get the information from, so I want the dlookup to lookup the organisation and then, if they have received previous funding, to show me the amount from the "total to date" field.
Is there an easy way to do this in a query, or can someone help me with the dlookup function? I'm lost!
[Indcat] and [2005] are part of the recordset where [Clind], [From] and [To] are values in the Table 'Class'
MY CHALLENGE:
I cannot find the correct way to add the extra selection criteria to the query expression (as per the form expression above) that checks the value [2005] is between the [From] and [To] values in the table. Have tried a number of combinations without success.
Any help, suggestions and/or guidance very welcome.
I've never used DLookup before and I can't get it to work for me so far.
I have 1 table which contains products and different properties of each product, such as the weight of the product.
I have created a query which sums the weight of all products, but only for those that have a value >0 in a certain field. This all works fine.
Now I simply want to display that calculated total weight in a text box on a form. So I thought DLookup could be used for that. But I can't get it to work, maybe because I'm not putting in any criteria? In the control source of the text box I've put the following:
=dlookup("[TotalWeight]","qryTotals")
I don't have any criteria, I just want the value from my qry expression. The textbox on my form now displays #name?
I need some syntax in setting 2 criterias for a DLookup query.
I've attached a sample db with 2 tables: Main & Timesheet
I need a "combo" query showing (on the same line) all Qty for Transcodes N, 1 & 2 where the Staff number and TSNum is the same.
I'm sure about the logic but the syntax is letting me down.
I can pull in 1 of the criteria E.g.:
OT1: DLookUp("Qty","ExOT1","[Staff] =" & [Staff])
But can for the life of me not script the second criteria in.
So in my result of ExCombo I'm getting Staff 11 showing 4 under OT1 while I know the result does not apply for TSNum 29832 as indicated hence the need for the 2nd criteria.
Since I have to change some of the values to text inside the query it might be best to have a look at the attached db rather than just suggesting the correct syntax .
I've been asked to get involved in some access development but don't seem to be get my dlookup syntax correct.I essentially want to lookup what salary band different employees are in.I have two tables:
Employee Table) has the fields: Name, Type, Salary
Salary Band Table) has the fields Employee Type, Salary Band, Lower range, Upper range..my query syntax is:
Code:
Band: DLookUp("[Salary Band]","Salary Band Table", [Salary] & " BETWEEN [Lower range] and [Upper range]" & "AND " & [Type] & " = [Employee Type]")
I can get the first criteria to work but can't get the second part to work - currently it produces an error.
I have a query that runs just fine and takes about 1 minute to run on average. I have vba code that uses Dlookup to determine if the query returns any records. The problem is that each time it runs it has to crunch all the data, all I care about is if there is any result at all. Is there a way to get the query to stop after it finds one record to speed things up greatly?
I have been all over the internet and trying different things for hours to no solution. I have created a form (Code) which I am using to auto fill two categories in a form using dlookup. Both categories fill perfectly but I cannot get the information to transfer to a query in order to capture the information in a report.
These are the dlookups I've been using and the categories I'm attempting to capture are "Description" and "Category"
I am having a problem with a Dlookup query. I want to achieve the following - I have 2 different tables
Demography Table - consisting of 2 fields, City and Region
For example City Region NYC NAM
The other Table totalflow consists of several field, one of the is flow from.
For example Flow from NYC WAS SEA
I want to do the following. Lookup the Flow from filed in table totalflow, and compare it to City in demography Table. If City = Flow from, return the value in Region. All are text fields.
I have tried following Query, but it only returns the same value for all rows. (from SQL view)
SELECT DLookUp("[Region]","Demography Table","[City] = '" &[Flow from]& "'") AS test FROM [Demography Table], flowsize;
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?
I am trying to create a Dlookup in Access 2010 within a query using query wizard. I want to lookup the tax rate for an employee based on a salary range and their 'tax category' (string). Through troubleshooting I can get the criteria to work separately.
These are: DLookUp("Base","TABWT","[TABWT].[Taxclass] = '" & [FirstOfTaxGroup] & "'")
DLookUp("Base","TABWT",[grosspay] & " Between [TABWT]![Minimum] And [TABWT]![MaxBracket]")
These work and return the correct values for each column/row when I run the query.
However, when I combine the criteria (using the build wizard) as follows:
Expr1: DLookUp("Base","TABWT","[TABWT].[Taxclass] = '" & [FirstOfTaxGroup] & "'" and [grosspay] & " Between [TABWT]![Minimum] And [TABWT]![MaxBracket]")
The Dlookup will returns 0 values but will not give an error message.
I've tried quite a few variations on syntax and quotes and so on. However, it's just not working for me.
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 :
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 :
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.
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
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
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.
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
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 ?
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:
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!
