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.
Table1 has three fields. I'd like to use the following DLookup statement:
DLookup(“[Field1]”,”Tablename”,”[Field2]=Left([Field3],4)&’55’”)
Note that in the criteria, I want Field2 = the first 4 characters of Field 3 + the string value 55.
I am sure I've got the quotation marks wrong.
I have the following SQL Statement which returns the desired result to me
Code:
SELECT tbl_p_stats.i_closing
FROM tbl_p_stats
WHERE (((tbl_p_stats.i_weekending)=[forms]![frm_a]![i_olddate]) AND ((tbl_p_stats.i_sacat)=[forms]![frm_a]![wfid1]) AND ((tbl_p_stats.i_complexity)=[forms]![frm_a]![wfc1]));
And am now trying to convert it to a Dlookup to provide the default value to a control on form Loading. The statement i came up with is
Code:
= DLookup("[i_closing]", "tbl_p_stats", "[i_weekending] = " & i_olddate & " AND [i_sacat] = " & wfid1 & " AND [i_complexity] = " & wfc1)
However, it is showing the control (wf1oi) as a blank control now. When I run the dlookup through the immediate window, the returned result is Null.
A little more detail
- All the mentioned controls live on the same form (frm_a)
- The control that this Dlookup is going into is called wf1oi
I need my INSERT statement to DLookup tbl_module_repairs and insert the information from a field based on a WHERE condition of primary key matching on the form and table. Here is what I have but it will not work on the last value:
Code:
Dim lookModType1 As Variant
lookModType1 = DLookup("module_type", "tbl_module_repairs", "prikey = " & Me.txt_prikey1.Value & "")
SQLtext4 = "INSERT INTO TBL_RF_TECH_LOG ([TechID], ActionStatus, Barcode, EquipmentDescription) Values (Left([txt_techid_rework_in], 2),'In Rework', '" & Me.txt_bc1 & "', " & lookModType1 & ");"
DoCmd.RunSQL SQLtext4
Note: My DLookup works fine by itself for other uses.
I have the following Select Statement:
SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],
[code]....
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
I have a query with the following criteria in one of the fields:
>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())
fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.
>=DateAdd("m",-12,fom()) And <=fom()
Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.
Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())
I have also added the column name to each expression and it still doesnt produce any results.
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 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 14 Replies View RelatedCan'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;
I have a field created in a query expression
BirthMon: Format([DOB],"mmmm")
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?