I have 2 tables, one called Addresses and Extended Areas.Within my imported orders I have addresses with postcodes and countries. My Extended Areas table has Country, zip_low, zip_high and extended area with a Y.I wish to check certain postcodes from a particular country between zip_low and zip_high, which gives Y if it is indeed a Extended area.
View RepliesI would like to make a DLookup that check 2 criteria in two different columns in the same table. The reason to use a DLookup is that I would like to check if two parts already have been linked together. The user therefore selects two values in to different comboboxes (lstLinkPart and lstLinkToPart). In this case the value of the first combobox is column: ComponentPN in tblProductLinkComponent. For the second (lstLinkToPart) column ProductPN, in the same table, has to be checked.
If both values from the comboboxes match the values in both (and only) columns I don't want to continue and made a code to be executed.
I found multiple examples on the internet for using multiple criteria but something similar to what I require.
Access 2010 - I would like to use DLookup to show results values from a table and display in a unbound textbox on a form. the results from each column in the table need to be on seperate lines, a break if you may. Here is the code I have so far.
Code: txtKeywords = DLookup("colKeyword", "tblKEYWORDS", "cboCategory = '" & txtcategories & "'")
In colKeyword Column In tblKEYWORDS table match what i select in cboCategory Combo drop down box and populate txtcategories textbox on form
What I would like to do is show All colKeyword results in textbox [txtcategories] and display each result on its own line!
example
entry1
entry2
entry3
entry4
I have a MS ACCESS 2010 database with a data table which i am trying to create a query from. I have 6 columns of data( one with an ID Field and 5 Name Fields). Below i have made examples of how it first appears as a simple query and the second will show you what i would like it to look like.
What the simple query looks like: [URL] ...
Second what I want the query to look like: [URL] ....
I have 3 queries named Mech Final Equipment 3 Mth, Mech Final Equipment 6 Mth, and Mech Historical Final Equipment.They all have two fields-Final equipment and Sum of Sum of Down (calculating the number of minutes each piece of equipment was down in the time period selected).
My ultimate goal is to join the three queries to display a pivot chart that uses the Final Equipment as the category field and 3 Mth, 6Mth, and Historical as seperate data fields.What I have is a join query (Which I have named Mech Final Equipment H63 Joined)
Using this SQL:
Code:
SELECT DISTINCTROW [Mech Final Equipment 3 Mth].[Final Equipment], Sum([Mech Final Equipment 3 Mth].[Sum Of Down]) AS Duration
FROM [Mech Final Equipment 3 Mth]
GROUP BY [Mech Final Equipment 3 Mth].[Final Equipment]
UNION
[code]...
Which returns a table that looks like this:
Final Equipment, Duration
Ancillary Equipment, 225
Ancillary Equipment, 401
Ancillary Equipment, 1787
Brush Unit , 1252
Brush Unit , 2519
Brush Unit , 8004
And so on.What I need the table to look like is this
Final Equipment, 3 Mth, 6 Mth, Historical
Ancillary Equipment, 225, 401, 1787
Brush Unit, 1252, 2519, 8004
And so on, like a cross tab.I tried to do a crosstab query but I don't have enough fields.
I am trying to add calculations to queries based on columns in the query... it seems to randomly expect 'Expression' or 'Group by' as column types, and Im having to create 3 sets of queries following on from each other to de-dupe data and allow filters on calculated values.
Also I've got a function which turns a date into a quarterly cohort, e.g. Oct 2013 -> 20134. I use ot on a lot of dates. I created a VBA function, CohortQ used as follows in queries:
Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 CohortQ([InputDate]))
In the VBA, InputDate is defined as a date
Code:
Function CohortQ(InputDate As Date) As Integer
If InputDate = 0 Then
CohortQ = 0
Exit Function
End If
[Code] .....
But when I run it on a date field, it gives me a data mismatch error. I can't step through as it's working on 600K rows.
If I put the function into the query,
Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 Year([InputDate])*10+DatePart("q",[InputDate]))
it works.
I was wondering how to do a crosstab query and have to column headings
I need the Organization Number and the Org name..so something like this
4005 4010
Office of HQ Office of Accounting
Is this possible?
Hello People,
Can you do a dlookup in a query?
I have two tables
Criteria_Ratings:
FFMin
FFMax
Rating
BaseData:
FF/M
What I am trying to do with a Dlookup is return the Rating when FF/M is between FFMin and FFMax. All fields are Numbers.
FFRating: DLookUp("Rating","Criteria_Ratings",[FFmin]<[FF/M] And [FFmax]>=[FF/M])
i have an access table.i m using Dlookup function for select data in dlookup , i want to sel field as combo box value, and i want when i change combobox value by select from list ,dlookup result will be change .
how can i do it.
I am trying to use a Like statement in a VBA dlookup, as some of the fields in a third party db i cannot manage are variations of fields in a table i need.
I currently have:
Me.Clinic.Value = DLookup("[ID]", "dbo_Clinics", "[Name] = Like '%' & [Forms]![frmHome]![subaTcTools].[Form]![subbDST].[Form]![txtClinic] & '%'")
But I get a runtime error 3075 missing operator.
Can't figure out why dlookup will not work for me in a query for certain dates?
Simple example to highlight the problem:
Table1 -
Read_Date----Read_Value
31/07/14-------10
01/08/14-------20
03/08/14-------30
20/08/14-------40
Query based on this table with the following calculated field:
Expr1: DLookUp("[Read_value]","Table1","Read_Date=#" &[Read_Date]& "#")
Query output -
Read_Date-----Expr1
31/07/14--------10
01/08/14
03/08/14
20/08/14--------40
Will not return a value for 01/08/14 or 03/08/14 (dd/mm/yy)
If you try it with many dates its skips several and I cannot see a pattern.
I read a lot about retrieving the LAST generated Identity from an SQL-server table. Everythings seems to work, but I get a wrong result.It seems that SELECT SCOPE_Identity does not work with access when working with such a code for example:
strsql = "INSERT INTO tbltest ( myDate ) " _
& "SELECT '" & dteDate & "' AS dte; SELECT SCOPE_IDENTITY() AS TestID; "
Call SQL_PassThrough(strsql,"myTest_PT")
2nd parameter is an existing PT-Query with the connection and return values=yes. If no 2nd para then there is no resultset, only the insert.
So I changed it to:
strsql = "INSERT INTO tbltest ( myDate ) " _
& "SELECT '" & dteDate & "' AS dte; "
Call SQL_PassThrough(strsql)
Set rs = CurrentDb.OpenRecordset("select @@Identity as TestID from tblTest")
lastID = rs("TestID")
This brings me to my big surprise an Identity from ANOTHER (!!) table, but not the last one from tbltest. The code is running for test reasons in another modul and not in the one I creating the received ID.
As I have to get for sure the last ID from tblTest I cannot work with DLookup, as in my multisuer App this is not sure enough.build a construction where I get the last ID from the table where I just made my insert.
I have a table that will have ITEMID as unique field but location could have save values. I tried using DLOOKUP to look at the value for the previous record to see if I need that value is same as current, however, since my data query are text formats, I am unable to. I get a message of mismatch data type. apply my query to look at sorted table by Location and ITEMID and if previous location = location, then my area=0?
View 6 Replies View RelatedBit of a strange question / problem. I am using a Dlookup between two queries. Everything runs fine is I leave the join as option 1: only include rows where the joined fields in both tables are equal. But if I change it to number 3 Include ALL records in right table in left it returns a syntax error on the query.
I can run the same query with same relationships of another table and it is fine!
I have a Dlookup which returns the correct value but returns the text of the instead of a numeric value. I have triple checked that the fields are all numeric. I suspect there is something wrong with my Dlookup syntax as it is my biggest weakness. In the following syntax what could be wrong?
DLookUp("SSER","TABSSS",[grosspay] & " Between [TABSSS].[Minrange] And [TABSSS].[Maxrange]")
I have a table tblDateGroups, with two field, [Group] (like This Week, Next Week, Last Month, etc) and [Code] that holds the code for criteria for each group in a query. I know the code is fine (like next week = Between Date()-Weekday(Date())+8 and Date()-Weekday(Date())+14 ) because I tested before putting in the table.
On my form I have a combo box that list all the [Group] and i hidden textbox that looks up the code based on the cbx after update...all that is good.
However, when i try to requery the report the criteria is not working. I am simply using the date criteria to read the hidden text box with the code string.
Forms!frmMainHome!subaWelcome.Form!txtCodeForQuery .
the error code is "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."
I have been working on a query in my database and I would like to look up a Number Value from another table based on a Number ID field and the Number Date in the Number Values table, where the Number ID field in the table agrees to the Number ID Index field in the query, and the Number Date field in the table agrees with the Initial Date field in the query. The Dlookup syntax I currently have is as follows:
DLookUp("[Number Value]","Number Values","[Number ID]=" & [Number ID Index] & "And [Number Date]= [Initial Date]")
The Dlookup shown above does not return an error, but it returns a blank field. I know that individually, the Dlookup for the Number ID works, but when I add the And for the Number Date the Dlookup returns a blank. Also, I have verified that there are existing records where the Number Date and Initial Date fields agree.
Also, I changed the format of the date fields in the tables to be ShortDate, thinking that the Timestamp was causing the Dlookup to not find a match. I also tried to use the DateValue() function within a query to reformat with not luck.
Why my Dlookup is returning a blank?
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'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.
Retrieval of data from a query and putting it into a textbox on a form. I currently have a form where I would like to put in a Job Number, and then hit an "Update" button and it will show some of the data fields associated with that Job Number. I have a table called "All Jobs" and each Job Number is unique. Where I'm really having issues with is in regards to Dates and Boolean fields.
In one form, I put in a job number and it will bring up the Company, Motor type, Received Date, and Quote. I have been able to retrieve the Company, Motor, and Quote with a DLookUp expression using a query. The expression looks like:
CompanyName.Value = DLookUp("Company","Approved Job Form - Find Job Info").
CompanyName would be the name of the textbox on the form, with "Company" the field within the "Approved Job Form - Find Job Info" query. The query itself uses the job number entered onto the form and returns the other data fields associated with that job number. Running the query by itself returns the correct information. My issue comes with using the Date Received field. It seems that because that field type is a Short Date, my DLookUp expression keeps prompting the debugger in the code. Currently I have:
RcvDate.Value = DLookUp("Receive Date","Approved Job Form - Find Job Info")
This will not bring the date from the query into my text box like the others.
In another form, I'm also having issues with a Boolean data field. I would like to have a query that looks up a job number, and if my data field (Job On Hold) is checked (True), I would like it to return a value of "Job On Hold / In Progress" into a text box. If it is unchecked (False), I would like it to return "Job Approved". I've tried a bunch of different ways to make it work, but I'm really stumped. The makeshift code I last tried (I've tried this numerous ways, so I'm not sure if I was closer before) is:
Dim jobstatus As Boolean
jobstatus = DLookup("Job on Hold", "Job Status Form - Search Status")
If jobstatus = False Then
Status.Value = "Job Approved"
Else
Status.Value = "Job On Hold / In Progress"
End If
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"
=DLookUp("Description","[Code]","[CodeID]='" & [ViolationCombo] & "'")
=DLookUp("Description","[Code]","[CodeID]='" & [ViolationCombo] & "'")
I have a table that holds information for a type of show and the base cost of the show. I am trying to make a query that gathers information for all the shows in the table and uses the Dlookup function to identify the base cost of that show. This will in turn be used in the same query to create a quote, but I can not get the Dlookup function to work and I am sure that I may be doing it very wrong.
The table is called ShowList. That table contains 3 fields, ID, Show Type, and Cost.
The query is called Venue Cost and will have a series of fields that work to develop some math on all items from another table called Venue Info. Venue Info uses ShowList to select the type of show.
What I want the query to do is look up the Show type from VenueInfo and then look up the base cost of the show type from ShowList. What I have been using is not working but is as follows:
DLookUp("Cost","ShowList","[Cost] =" & "[Venue Cost]![Venue Type]")
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 RelatedI 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;