I have a form that has a field that needs to get its data from a query based on two fields in the form. The following is my DLookUp statement:
DLookup("[COMEX]", "OptionMetalsListQ", "[Metals] = '" & [cboMetals].[Column](1) & "'" And "[DateOfPrice] = " & Me.txtDateOfPrice)
Comex is a metal market. the bound column in cboMetals is text and The date is a date.I am getting the error "Run-time error '13': Type Mismatch.
Why does this work when the text box is used and not when the variable is used directly?
Code:
Private Sub Command61_Click()
Dim strDBName As String
strDBName = getDBName()
Me.Text59 = strDBName
Me.Text62 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = text59")
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = " & strDBName)
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = strDBName")
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName]" = strDBName)
End Sub
Text62 returns the correct value
Text64 failes on everyone of the examples
I have been trying to find a solution to why I can't get a Dlookup with multiple criteria to return the value I need.
Essentially I am trying to use an Order Number to find the item number which is contained within another table. However the order number has multiple lines (suffixes) which alter the item number. Therefore I am trying to have the item number be populated by the correct 'combination' of Order Number and line ("suffix").
I have managed to use the Dlookup in the after update of each box of the form separately and they retrieve values in the table correctly:
Afterupdate of main order number:
Code:
Private Sub OrderNumbertxt_AfterUpdate()
ItemNumbertxt = DLookup("item", "dbo_job", "[job] = '" & Forms![**INPUT]![OrderNumbertxt] & "'")
End Sub
Afterupdate of suffix:
Code:
Private Sub SuffixTxt_AfterUpdate()
ItemNumbertxt = DLookup("item", "dbo_job", "[suffix] = Forms![**INPUT]![SuffixTxt]")
End Sub
However when I combine them as follows in the afterupdate of the Suffix box I receive a "Run-time error '13': Type mismatch"
Code:
Private Sub SuffixTxt_AfterUpdate()
ItemNumbertxt = DLookup("item", "dbo_job", ("[suffix] = Forms![**INPUT]![SuffixTxt]") And ("[job] = '" & Forms![**INPUT]![OrderNumbertxt] & "'"))
End Sub
I think the reason is something to do with some being numbers and some being a combination of text and numbers (based on the replies of other topics), but have been trying to modify these slightly and can't get it to work still.
Also the Order Number is a combination of letters and numbers (normally in the form of AB12345678), the suffix is just a number between 0-9 and the Item number it finds is a combination of numbers and letters.
I 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.
I have this
Code:
If Not IsNull(strCount = DLookup("[Number_Of_Records]", "All_Booked_Callbacks ", "[CallBack_Date] =#" & Me.CB_DAte.Value & "#" _
& " And [CallBack_Time] = #" & Me.CB_Time.Value & "#")) Then strCount = DLookup("[Number_Of_Records]", "All_Booked_Callbacks ", "[CallBack_Date] =#" & Me.CB_DAte.Value & "#" _
& " And [CallBack_Time] = #" & Me.CB_Time.Value & "#") Else strCount = "0"
All_Booked_Callbacks is a query which has a date and time columns and count of callbacks for that day and time, this always returns 0
I am trying to make a DLookup function to return the ID number of an entry that matches 2 or 3 criteria but I am struggling to get the syntax correct for the second and third criteria.
Here is what I have so far:
1 criteria, works fine =DLookUp("[timedata]![id]","timedata", "[processdone] =" & Forms![Mainform]![p11] )
2 criteria, works fine =DLookUp("[timedata]![id]","timedata", "[processdone] = " & Forms![Mainform]![p11] & " And [timedata]![BGSnum] = 1001" )
BGSnum is a numerical value but it changes for each form I load, so what I want to do is use the form location value as the criteria.
E.g.
=DLookUp("[timedata]![id]","timedata", "[processdone] = " & Forms![Mainform]![p11] & " AND [BGSnum] = ' " & Forms![Mainform]![BGS] & "' ')
and possibly a third criteria too. This is where I am having problems and the syntax is wrong so MS Access wont let me save the macro.
I have a small problem with dlookup multiple criteria. Vba code looks like this:
Code:
Label34.Caption = DLookup("[Spent_Hours]", "249_1_CHours", "[Date_Added]= " & Me.Text27 & " And [Shift] = '" & Me.Text29 & "'")
This gives following error:
Syntax error in number in query expression '[Date_Added]=4.02.2015 And [Shift] = '2'.
[Shift] column is a numeric field.
I have a table which contains the following:-
boardingpoint pickupcode pickuptime
bolton N1 07.30
bolton N2 08.00
each boarding point has 6 different pickupcodes and pickuptimes
i am trying to create a form, which is currently displaying data from an query depending on which tour is selected from the main form.
my current dlookup is like this.
=DLookUp("[pickuptime]","boardingpoints","[boardingpoint]=Forms![pickup list]![boardingpoint]")
what do i need to add to the end of this so that it also looks at the pickupcode as well as the boardingpoint to display the correct pickuptime and not just the first one it comes to?
Hello,
I have an inquiry about using dlookup to retrive all fields from a table that are using the same criteria. Here's the table structure
Table1:
ID (Autonumber)
Number
Code
Grade
I have the following data:
Number Code Grade
4 15 68
4 52 78
4 17 69
5 85 83
5 28 17
I am using this to retrive values for number 4 but I only get the first row
DLookup("Code", "Table1", "[Number] = 4")
DLookup("Grade", "Table1", "[Number] = 4")
I get only as a result 15 and 68, how can I retrive all the rows that have Number 4?
Thanks in advance for your help
Can you put a Dlookup in the criteria of a query?
I have table which contains bookings
customername excursionname etc
i want to include in my query results some information relating to the excursionname for a report, so that i can use them for sorting.
how do i add a dlookup to the query to display the pickupcode for the excursionname from the table boardingpoints
i have the fieldname Expr1 etc, it might look something like this:-
=DLookUp("[pickupcode]","Excursions","[Excursionname]=query![pickuplist]![Excursionname]")
Maybe?!?
I have the following DLookup statement which if the number of bags is greater than 0 will find the previous accommodation name. How do I get it to only select the accommodation name if the CustomerID field is the same as the previous as well?
BagsFrom: IIf([Bags#]>0,DLookUp("[Accommodation_Name] ","TblAccomBagsSorted","[Test2ID]=" & [Test2ID]-1)," ")
Thanks
Gary
I'm back yet again with a new and intriguing set of problems.
I am building a fancy report and Come to find out you cannot use SQL to return one simple number in a text box because even though you know you will write the statement to return only one answer everytime, access doesnt understand that so we can do a domain aggregate thing with DLookup.
OK fine I have to do alot of them but still OK, here is my issue
I can get DLookup to give me what I want if i type into each control source the criteria that I want to return.
I want to lookup a number from a recordset but there are two things that make the number unique date and strain. strain is easy to cough up in the right place because i will hard code that into the control source dlookup, the part that is killing me is the date
I can get a dlookup to give me accurate results based on multiple criteria, but how can i get that to include the date for which I am running this report.
I do enter the date parameter each time i open the report.
Here's what i got so far
=DLookUp("csQuantity","SpawnScheduleCulSpawn","csstrain='AC-PA'" & " AND week = #4-29-07#")
That returns the answer i want, but i have 50 of these text boxes in one report so i cant go through and write the date in each one.
how can i make the part following " AND week =" dynamic so that it runs the date that i entered as the parameter for the report?
Thanks in advance!
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 am using MS Access 2010 and have an issue with one of my forms. First off, I have a table called coefficient which contains a column MIN, a column MAX, and a column COEFF.In my form, called CALC, I want to grab a number that it is in textbox [Text12] and place the related coefficient number in textbox [Text15].I placed a control source in [Text15] using =DLookup("[COEFF]","coefficient","[Text12]>[MIN] And [Text12]<[MAX]").
View 2 Replies View RelatedI seem to have run into a snag. I'm pretty sure this would work otherwise:
DLookup(iif(Left( [GenericFieldname1], 4) = "C101","Offered","NOT OFFERED"),"Generic Query")
When I added this field/function to the query, I got #error values and when I clicked on one, I got an error that said:
The expression you entered as a query parameter produced this error:The object doesn't contain the automation object 'Please Enter Year'.'
I assume the problem is that I have the user filter the report by use of the Like() function for [Year] in the query when the report is accessed. How do I tell the DLookup function to search the column as filtered by the Like() function?
creating a DLookup expression for an unbound control on a report. This report displays data collected from the users from a table called tblaudit completed. This table contains (among others) three fields labeled below
Brand
Form
Area Changed
I also have another reference table labeled refevalareas. The fields in this table are as follows
BrandName
FormName
AreaName
PointValue
I would like to create an unbound control on my report that "prints" the point value depending on the Brand, Form, and Area displayed for each record. Though the field names are different the data collected and displayed in each table is consistent. I haven't worked much with Lookups and the logic is challenging for me to grasp but I think I have the basic idea which is shown below..
DLookup( refevalareas.[PointValue], refevalareas, ("[BrandName] = '" & Reports!rptqryauditcompleted![Brand] = "'") & ("[FormName] = '" & Reports!rptqryauditcompleted![Form] = "'") & ("[AreaName] = '" & Reports!rptqryauditcompleted![Area] = "'")
I'm having trouble with DLookup() using dates as part of a multiple criteria. I'm using the following;
Code:
If IsNull(DLookup("ExchRate", "TBL_DDPExchRates", "CurID = " & Me.Combo4 & " AND ExchDate = #" & Me.ShipOBDate & "#")) And Me.Combo4 <> 2 Then
It seems that if ShipOBDate is any date between the first and tenth of the month the DLookup fails to locate the appropriate record. I can't understand why as ShipOBDate and ExchDate are both formatted as Short Date and ExchDate is being populated via an OpenArgs which is derived form the field ShipOBDate..Is the fact the the date is getting converted from a date to string and back to a date some how upsetting things
I have a database which I have been using for over a year now which includes a Dlookup formula in a report.The dlookup references the query QRYDwgCount and looks up the number of drawings issued by a particular architect. I have been using the following formula which was previously working
="Total drawing count for " & [IssuedBy] & " = " & DLookUp("CountOfDrawingNo","QRYDwgCount","IssuedBy = '" & [IssuedBy] & "'").
The problem I have now encountered is a new job with an architect that worked on another job already in the database. Instead of bringing forward the number of drawings issued by the architect for this current job, the formula is showing the number of drawings issued for the earlier job. I therefore need to add "ContractName" = [ContractName] but I am getting either Error or an incorrect answer as the result.This is what I have written:
="Total drawing count for " & [IssuedBy] & " = " & DLookUp("CountOfDrawingNo","QRYDwgCount","IssuedBy = '" & [IssuedBy] & "'" And "ContractName='" & [ContractName] & "'")
I have a form where I am using two combo boxes the second combo box fields are being based on what is selected from the first comb box. When I enter the DLookup criteria into the text box I am trying to lookup based on the selection of the combo boxes it returns nothing. Here is what my DLookup looks like:
DLookup("[BranchNumber]", "[tblAllADPCoCodes]", "[ADPCompany]= '" & [cboADPCompany] & "' And [LocationNumber]= '" & [cboLocationNo] & "'")
I can't seem to get past this expression/criteria problem.
[Actual] is my field I want to grab data from
[L3-4-5] is my main table
[CDATE] is a form control where the user enters a date
[Quarter] is a number field and want it to equal 1
[Partname] is a text field and string it to equal 1
My current expression is:
=DLookUp("ACTUAL","L3-4-5","ID=" & [CDATE] And "[QUARTER]=1" And "[PARTNAME]='1'")
Although my other expression on another form works.In my if code statement I have
Me.DAYS_TRAINED.Value = DLookup("Days", "TRAINED LH A-PILLAR", "ID=" & FTM_NAME)
So I think I don't understand the multiple criteria part.
I have a form with multiple combo boxes. Once the combo boxes are populated, I need the text box to look up the value in the query based on two combo box selections.I am attempting to do a DLOOKUP, but I am obviously not doing it correctly because the result is #NAME?.
Here is what I have done: In Default Value section of the txtONE property sheet: =DLookUp([fieldTHREE],[qryMAIN],[cmbONE].[AfterUpdate] And [cmbTWO].[AfterUpdate])
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'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 have a form with combo box named "TEXT17" i have an another text box named(Basic Pay). How can I use dlookup creteria with two condition
EMPID= text17 value and
month in lbl9 value="mar"
I also paste my code here but show error
Me.Text43 = DLookup("Basicpay", "Salaryall", "[EMPID]='" & Me.Text17 & "'" And "[Month]='" & "Mar")
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'm using a Dlookup in expression builder and it's only returning the value of the first record rather than the criteria I specify.
View 6 Replies View Related