[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 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 executed q query which has a Nz function. this works fine when exceuted from access. but when i try to executed the same from Vb i get an exception
"Undefined function 'Nz' in expression"
can anyone point out why this is happening? and wts the solution?
I have two forms...frm1 has a text box with an expression in it and I need frm2 to display the result of the expression. I'm using the DLookup expression and it either gives me #Name? or #Error? message in the text box frm2.
=Dlookup("[loan#]","tbl_loan","[Days] =" & Forms![frm1]!Days) <that gives me #Name? message
1. I have a table called "CONTRACT NAMES AND NUMBERS" with a field called "REDUCED_USERS", this field is a checkbox (Yes/No in the table). This table houses all of the customers with their id numbers and basic info.
2. I have another table called "REQUESTS" which houses their orders. This also has a field called "REDUCED_USERS".
In my form "Amendment Request Tracking" I have tried to do a DLookUp in Expression Builder to check the box, per order, if the customer has reduced users in the "CONTRACT NAMES AND NUMBERS".
I have tried many variations and have just realised that this is probably because it is a yes/no field so may struggle with what to populate with (currenly nothing!).
My most recent variation of expression is (where NAD_NUMBER is the common field in both Tables and Form with relevant relationship):
=DLookUp("[REDUCED_USERS]","[CONTRACT NAMES AND NUMBERS]","[CONTRACT NAMES AND NUMBERS]![NAD_NUMBER]=[NAD_NUMBER]")
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.
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]
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] & "'"))
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!
The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)
When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?
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?
Simple this one but it's something I can't seem to get working so I thought I would ask as I'm missing something obvious.
What is the correct expression to use in a query to select records with particular dates. The database contains information on quotations and each record has a follow up date field and a status field.
The query should find records where the follow up date is today and the status is "Live".
I have =NOW() in the date field and "Live" in the status field but no records are being found even when I know there are some that match the criteria.
Good Morning. Have a quick question for the gifted.
First let me say I have inside my table Target Install, Received Date, and Type of Circuit
In the Query, I have created a column with the following statement. Due Date: [Target Install]-30 which works great to give me 30 days from the target install. The issue is that this statment is not correct for all products. So I need to understand how to build a formula for this in the query.
If [Type of Circuit] = "Branch Network Frame" than [Target Install]-30 or If [Type of Circuit] = "Class I" than Received Date+7
Could someone provide me with an expression that would always return data from last week? I've been playing around with serialdate and datepart but can't quite figure it out.
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 have a query with Product, Product Directorate, Date Received, Date Delivered, and Date approved fields.
Product field criteria: "NAT CONV"
Product Directorate criteria: ([forms]![frmWhatDates].[cboPD]) or ([forms]![frmWhatDates].[cboPD is null)
Date Received criteria: (Between [forms]![frmWhatDates].[txtRecvStart] And [forms]![frmWhatDates].[txtRecvEnd]) or(([forms]![frmWhatDates].[txtRecvStart] is null) or ([forms]![frmWhatDates].[txtRecvEnd] is null))
and so forth
When I closed my query, and opened it up again it looked bizarre. Because of the Is Null it added criteria multiple times.. I don't know what's going on but this makes it hard if i want to edit it in the future.
Why did Access do this to my query? Is there anyway I can make this simpler and how?
What I have is a form that filters records according to what is input and it generates a report. If that date fields are left blank.. I want it to display all the records
