DLookup with 3 Criteria??

Is there a way to do a DLookup with 3 criteria, I have tried this with no luck...


=DLookUp("[Price]","tblPrice","[LocationID] = " & [Location] & " And [Date]=#" & [TodaysDate] & "#" And "[MonthForward] = #01/01/2004#")

HELP!


Post your answer or comment

comments powered by Disqus
As I put in my intro post, I often have trouble with syntax. Its so basic I know there is an expert here who can help.

Data:
I have 2 queries that have all the data I want.
The first query's data has been turned into a form.
-The 2nd query has multiple values of the same information and is broken out by a unique ID number called Risk_ID and has only values 1-55.
I want to add an unbound field from the 2nd query using a basic Dlookup with multiple criteria, that will give me the Total_Score, dependent on the Job_Station and the Risk_ID.

Code:
The following code works but only gives me the first Total_Score as it relates to the job stations
=DLookUp("[Total_Score]","qry_LineSummaryScore","[Job_Station] =" & [Job_Station])
This code gives me only the very first Total_Score whose Risk_ID is 2, regardless of the Job Station.
=DLookUp("Total_Score","qry_LineSummaryScore","Ris k_ID=2")

The syntax of the following code is wrong because it again only gives me the very first Total_Score whose Risk_ID is 2, regardless of the Job Station.
=DLookUp("[Total_Score]","qry_LineSummaryScore","[Job_Station] =" & [Job_Station] And "[Risk_ID]=" & 2)

All fields are Numbers, no text values.
I must have looked at every example available on this site and others, and read the syntax description a dozen or so times. It just isn't clicking in my head. Some direction is appreciated.

Thanks Matt.

Hi,

I searched the forum for this problem but my problem is still unique.

Problemsituation (see attachment):
I have a form (frmAanvoergegevens) with 3 fields:

* Contractnummer - txtContractnummer - textbox - numerical
* Productcode - cboProductcode - combo box - text
* Leverancierscode - cboLeverancierscode - combo box - text

I also use a table called tblContracten. The fields above are also in that table.

Question:
If I click on the button 'Opslaan' I want to check if the fields (which are filled in) are the same as the fields in the table tblContracten. If yes, then a message will pop-up.

Until so far I used the following code:


	Code:
	If Not IsNull(DLookup("[txtContractnummer]", _
    "tblContracten", "[Contractnummer] = " & Me.txtContractnummer _
    & " And [Productcode] = '" & Me.cboProductcode _
    & "'" And "[Leverancierscode] = '" & Me.cboLeverancierscode & "'")) Then
        
    MsgBox "The contract is ok.", vbExclamation
    End If

I need some help...


Regards,
JiTS

Hai,

I want to use a Dlookup() function with multi criteria of two Numeric fields in Access 2010. The function give me result when I use them individually. When I combine both criterias, it is not filtering records to t combined criteria. Can anybody tell me what I am doing wrong here? The following statement I use as the control source of that field:

IIf([ToolNumber]=0,"N/A", DLookUp("[bslocation]","[ballscoop]","[noofballs] =" & [Forms]![ASSEMBLYTOOLS]![ToolNumber] And "[ballsize] =" & [Forms]![ASSEMBLYTOOLS]![bsballsize]))

Fields [noofballs], [bsballsize] and [ToolNumber] are numeric

Second Question:

In a tabl, I want to use a field to enter data like 1/16, 3/32, 1/8, 5/16, 1/4 etc etc... and do calculations with that field, what format I should choose?. When I choose the field as number type, it converts the value to decimals. Can I use Text format and do calculations with those fields? Or is there any special type available in Access 2010 to accomodate such formats?

Thank you and best regards

I have a aPointsRate table such as shown below:

DayTypeID____BeginTime_____EndTime____MultiplyBy
2____________00:00am______8:59am____2.0
2____________9:00am_______5:59pm____1.0
2____________6:00pm_______11:59pm___1.5
3____________00:00am______8:59am____2.0
3____________9:00am_______5:59pm____1.0
3____________6:00pm_______11:59pm___1.5

When a user enters a Date and TimeIn, eg:
Date=21/10/2003, TimeIn=3:00pm
I expect to retrieve MultiplyBy=1.0

My DLookup looks like this:
=DLookUp("[MultiplyBy]","aPointsRate",("[DayTypeID]= " & Weekday(Forms!ufmService!ufmServiceVisit.Form!Date )) And (Forms!ufmService!ufmServiceVisit.Form!ufmServiceD etails.Form!TimeIn & " >= [BeginTime]") And (Forms!ufmService!ufmServiceVisit.Form!ufmServiceD etails.Form!TimeIn & "

I'm struggling with the syntax for a dlookup with multiple criteria. (Also, you'll see that my Msgbox is wrong (as I can't recall how to handle a string variable).

Any assistance would be appreciated very much.



Dim iDirectorID As Integer
iDirectorID = Nz(DLookup("nameID", "tblFunctions", "episodeID = " & _ Me!cboSelectEpisode And "Function ='Director'"), 0)

If iDirectorID > 0 Then

Dim strDirector As String
strDirector = DLookup("FirstName" & " " & "LastName", "tblNames", "nameID" = iDirectorID)
MsgBox ("strDirector is already listed as the Director of this episode.")
Exit Sub

End If

I have a table in which I have a field that contains comma's. The field contains a salesprice stored in European format. I need to do a Dlookup where this field is part of the criteria.
If I do a lookup, I get an error 3075 (comma failure).
If I single or duoble quote I get an error 3464 (type mismatch).

Any suggestions on how to use Dlookup with numeric criteria, containing a comma (other then changing the regional settings and converting comma to dot).

See code below. The problem is in Me![CustPrice] (which contains for instance 1,25).

If Not IsNull(Me![CustPrice]) Then
If Dlookup("[Cust-id]", "Pricetable", "[Cust-id] = " & Me![Cust-id] _
& " And [CustPrice] = " & Me![CustPrice]) & _
& " And [Product-id] = " & Me![Product-id]) Then
msgbox "Already exist"
Cancel = True
End If

Good Morning. I am trying to tackle a DLookUp with double critieria.

Me.MPOIAuditItemsTB = DLookup("AuditItemsID", "FindingsElements", "[Element]= '" & Me.MPOIElementTB & "'" And "[AuditID]=" & Me.MPOIAuditTypeTB)

I can get it to work before the And and if I strip it out, after the AND, but not together. What am I missing???

I am trying to open a report base on 3 criteria as follows:
DoCmd.OpenReport "rpt_sumReadmissionDate", acViewPreview, , ("[Source]' Non-Collaborative Facility' And [CurrentAdmit]-[DischargeDate]>2 And [Home]'Yes'")

The first two criteria work fine but when I added the third criteria hundreds of records dropped form the report although majority of the records in the database meet all three criteria.

I'm not sure what I am doing wrong of if access allows for 3 criteria. PLEASE HELP

Hello,

I am creating a form that looks up the value of a field on a table that matches two criteria on my form.

My table [partstore] has three fields, [binid], [pid] and [qty] their values are L01-01-03, 2 and 3

my form [takeapart] (For this question) has two text boxes [psid] and [pid]. the values entered into the forms are L01-01-03 and 2

I'm trying the following code in a button,
Code: Dim getbqty As Integer getbqty = DLookup("[qty]", "[partstore]", "[pid]=[Forms]![takeapart]![pid] AND [binid]='" & [Forms]![takeapart]![psid] & "''") MsgBox getbqty When I run this I get "Invalid use of Null"

When I go to immediate mode to debug it, running the dlookup with just the pid = forms pid works but trying the binid to psid comparison always results in a null?

What am i doing wrong?

Ted

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

Hi, I've been struggling to get a DLookup to work. I have a report that I want to look up some data.

I'm trying to make a report that will print an old invoice based on a date and employee and show the correct inventory levels at that time. Right now I have it working so a person can print todays invoice however once new data is entered I can't get the lookup working for older data.

I'm tracking inventory and have 2 bound fields, called "given" and "sold" in a table called "inventory" everyday when inventory is given to an employee its entered in a subform and at the end of the day the sold items are entered in as well. After the day is over the employee retains his/her inventory for the next day and more is added to it. So a balance of the inventory is kept and works fine.

I have an invoice created which shows how much of each product the select employee had at the beginning of the day and how much they sold and then how much is remaining.

Here's the DLookUp I use in my invoice report and on a form to give me the current inventory balance for a certain employee and product to date.

Code: =DLookUp("sum(Given-Nz([Sold],0))","Product Query","[EmployeeID] = [Reports]![Invoice Old]![EmployeeID] And [SKU] = [Reports]![Invoice Old]![SKU]") Here's the Dlookup I use in my invoice report to give me the amount the day was started with.

Code: =DLookUp("sum(Given-Nz([Sold],0))","Product Query","[EmployeeID] = [Reports]![Invoice Old]![EmployeeID] And [SKU] = [Reports]![Invoice Old]![SKU]")+[Sold] Those both work fine but now I'm trying to add a "between date" criteria to the DLookup as well. I made a form which asks for an employee and date to lookup the invoice however it only brings today's inventory balances even when I select a date from a week ago. I'm hoping I can have the date that's entered in the form reflect on the Dlookup.

So I want the Dlookup to look up the same data I have it set to but only lookup that data from the beginning to the date selected in the form that opens the report not to todays date. The beginning date will always be the same obviously it just needs to stop at the date I select when I runt he report so my balances are for that date not today.

I hope all this makes sense. I've tried so many different variations of Dlookup including the date but can't get it to only pull the data up to the date and do the math up to that point.

Any help is greatly appreciated.

Hi my friends. I have a little problem using Dlookup.
In my form I have 1 textbox (with date and hour ex. 2009-10-30 11:00:00), and 3 dropdownlist.
I want that when I choose the date and the first ddl, the third will be enable and show only the value with the other 2 (date + 1st ddl).
After that when I choose the third, the fourth will be enable and show the value correspond to the other 3... [Sorry for the explaination]

To explain better I make you an example:

date_birth / family_name / name / car
1)1989-10-09 / Doe / Joe / Honda
2)1989-10-09 / Doe / Joe / Ferrari
3)1990-08-01 / Altkins / Mario / Cristler
3)1990-08-01 / Altkins / Jeff / Mercedes

Do you understand the problem?

I know how to use Dlookup with one value, but with more than one I don't know...

Any suggestions?

Thx and greetings.

Hi,

I'm having a hard time figuring out the exact syntax for the below dlookup function. When I separate the criteria individually, I get a result, but when I combine the two, I get run-time error of Type Mismatch.

strTableID is a string variable

Quote: DLookup("ID", "MyTable", "myFieldName= '" & strTableID & "'" And "Status = 'Open' ")

Hi,

I'm having a tough time getting the following Dlookup to work:

DLookup("[ConfirmedLoad]=0", "qryDriver4a2", "DeliveryDate=#" & Format(Forms!frmLoadAllocation!cboAllocationDate.V alue, "mm/dd/yyyy") & "#" & "AND [StateID]='1'")

My original Dlookup (which worked fine!) didn't require StateID but it is necessary to include it now. Can anyone help me with the correct syntax for the StateID portion? Thank you.

Hi, I need help on the condition part of the dlookup function. Both conditions are based on variables, with one created using TempVars in Access 2010.

Variable one: GstrID (a global variable)
Variable two: Created by TempVars.Add "OrderType", strOrderType)

So the function should like something like below...I know I'm nowhere near correct on the syntax

Quote: dlookup("MyField", "MyTable", "MyID = GstrID" And "MyOrderType = TempVars(OrderType) )

This should be very easy but I'm goofing it up somewhere. I am performing a simple Dlookup on a table where the value of a combo box is compared to the primary key field.

Dim xTimer As Boolean

xTimer = DLookup("Timer", "tbl_ref_assignment", "Me.AssignmentType = AssignmentID")

In this case Timer is a boolean field.
Me.AssignmentType is a combo box which contains a numeric integer value
AssignmentID is a primary key which is also a numeric integer

I need to find the "Timer" value which is either True or False for the record that matches the value in Me.AssignmentType but I keep getting a:

"Run-time error '2001': You cancelled the previous operation."

I've tried formatting the criteria in a number of ways but nothing works.

Hi everyone,

I need help with one very simply update query which I need to run.
I have made a simple update query with one criteria:
It works great.

UPDATE tblArticle INNER JOIN qrsLimPriceNEW ON tblArticle.ArticleID = qrsLimPriceNEW.ArticleID SET tblArticle.PriceROZCoef = 4
WHERE (([qrsLimPriceNEW].[Expr2] Between 2.21 And 2.95));

Now my next step is to create query where i have mulltiple creteria for example:
UPDATE tblArticle INNER JOIN qrsLimPriceNEW ON tblArticle.ArticleID = qrsLimPriceNEW.ArticleID SETtblArticle.PriceROZCoef = 2 WHERE (([qrsLimPriceNEW].[Expr2] Between 0.61 And 1.50));
tblArticle.PriceROZCoef = 3 WHERE (([qrsLimPriceNEW].[Expr2] Between 1.51 And 2.2));
tblArticle.PriceROZCoef = 4 WHERE (([qrsLimPriceNEW].[Expr2] Between 2.21 And 2.95));
All together I have 7 different conditions.

I simply don't know what funcition I need to use to make it work in one query together not 7.

I tried searching there some similar problems but I still don't understand.
Thanks for help in advance.

Hi,

Say for example I have a table with 3 fields: Date, Sector, Weight

Is it possible for me in the one SQL statement to select the sector field with one set of criteria and the weight field with another set of criteria?

ie is it possible to combine the following 2 SQL statements into 1 query?
SELECT fldSector FROM tblSectors WHERE fldDate = #12/19/2012#
SELECT fldWeight FROM tblSectors WHERE fldDate = #12/18/2012#

so the result displayed would be one column with the fldSector and another column next to it with fldWeight.

I'm making a report that has several text boxes that will have calculated values pushed to them via a VB Code. This VB code resides on the "ON Format" event of the report.

I have an totals/aggregate query that looks at a table and returns the total estimated structural engineering hours. I need to convert that SQL code to a DLOOKUP in order to use the result in a computation.

Basically I have another totals/aggregate query that looks at a separate table and returns the total actual structural engineering hours. I plan to use DLOOKUP to get this result, and use it to find the % Difference between the value for estimated versus actual hours.

One problem, I can use DLOOKUP with a standard query. But I have NO IDEA how to use it with a Total (aggregate) query.

DLOOKUP (expresion, domain, [criteria])

I get about this far with it:

Dim intHours As Integer
intHours = DLookup("Mech_Hours", "tlbProjStruct", "proj_num= " & Me![txtProj_num] ........

My SQL for the total estimated structural hours looks like this:

SELECT Sum(tlbProjStruct.Struct_Hours) AS SumOfStruct_Hours
FROM tlbProjStruct
GROUP BY tlbProjStruct.proj_num
HAVING (((tlbProjStruct.proj_num)=[Forms]![frmReport_Division]![txtProj_num]));

Any help here?

I would like to query a database with Dlookup based on an employee id and a date. I can get it to work just using the ID.

This works:

	Code:
	Me.txtEmpID = DLookup("EmpID", "PiecePay", "EmpID='" & txtEmpID & "'")

I would also like to have a field called PieceDate equal to txtPieceDate.

The query would be:

	Code:
	Select EmpID
From PiecePay
Where EmpID = txtEmpID AND PieceDate = txtPieceDate

How would I do that with DLookup? I've tried it different ways. My problem is probably with my use of the double and single quotes.

Thanks for any help.

I am building a dlookup expression for control source to a text box on the form [EO_Assignment], and I am getting an error message. I have looked at the syntax suggested for DLookup on other threads on this site for help, but no luck. Here is my expression:

=DLookUp("Comments","Alerts","[SpeciesCode]= " & [Forms]![EO_Assignment]![txtSpeciesCode] & " AND [ElementOccurNum]= '" & [Forms]![EO_Assignment]![txtEONum] & "'")

Comments, Species Code, and ElementOccurNum are fields in table Alerts
txtSpeciesCode and txtEONum are text boxes in the EO_Assignment form

I would like this control to Comments if the criteria are met, otherwise leave blank. Is my problem here that many of the comments fields are blank, so I need to use the Nz command? And if so, can you suggest the syntax, as I am now well beyond my knowledge of Access.

Thanks,
W

Hi,

My set-up: Access 2007, Windows 7

Apologies if this has been asked in the past - I have searched but cannot find any similar posts.

Is there a way to format controls (such as textboxes) on a continuous form in Access 2007 based on more than 3 criteria? As many will know this is the limit for conditional formatting, and I have encountered several situations where it would be very useful to exceed this number.

Any suggestions will be gratefully received.

Thanks,

Mark

Hi, i know there are loads of posts for this problem but no matter how many I read it just doesn't seem to work!

Basically I have a form and i need to have a text box which shows a value of business placed into a specific hotel by a specific client. The table where that data comes from is [RN04_CLIENT] and the field with the value is [RN] and the field with the client name is [RESCOMPANY].

There are two fields on the form where this would link, the first is a field called [txt_HOTEL] and the other is a created dlookup field called [look_CLIENT]. This looks at a code to return the Client name that would match the [RESCOMPANY] field in the table.

If i try to do the dlookup with either criteria on it's own it works fine, but i'm very new to this stuff and would appreciate any help you can on this?

FYI With the 2 criteria in only 1 record would ever return, am I right in assuming that if more than 1 would return the dsum would also work?

Many thanks, anyone from M'cr helps i'll get you a beer!


maybe there's a small nuance I'm missing when using DLookUp with a date as the criteria...
my DMax returns correctly
I do not get an error with my DLookup
BUt the DlookUp retuns the wrong value...

perplexed.. (but with thanks in advance for any assistance), mark


vMaxDate = DMax("[Version_Date]", "tblVersionInfo")
vlblVersion_Info = DLookup("[Version_ID]", "tblVersionInfo", "'[Version_date] = " & vMaxDate & "'")
Me.lblVersion_Info.Caption = vlblVersion_Info


Not finding an answer? Try a Google search.