dlookup with multiple criteria

I have a form that needs to look up a "goal" by matching several fields in a 
table.  I can't figure out how to do dlookup with multiple criteria

frmManualTaskDataEntry
[employee]
[date]
[mailcode]
[state]
[disabilityind]
[volumecode]


tblMailCodeTasks
mailcode
state
disabilityind
state
goal
0
Utf
4/22/2010 1:53:02 PM
access.forms 6864 articles. 1 followers. Follow

2 Replies
2148 Views

Similar Articles

[PageSpeed] 56

Buzzmcduffie -

You use AND to connect the multiple criteria, and must include proper 
delimeters for text and date fields.  It will look something like this 
(untested):

DLookup("[goal]","[tblMailCodeTasks]","[mailcode] = '" & Me.mailcode & _
"' AND [state] = '" & me.state & "' AND [disabilityind] = '" & _
me.disabilityind & "'")

You have two 'state' fields listed in your table.  If one of them is really 
date and you need to include criteria on that, then it would be like this:

DLookup("[goal]","[tblMailCodeTasks]","[mailcode] = '" & Me.mailcode & _
"' AND [state] = '" & me.state & "' AND [disabilityind] = '" & _
me.disabilityind & "' AND [date] = #" & me.[date] & "#")

You really should change the name of the 'date' field to something else as 
this is a reserved word in Access, and it can cause problems.  For now, 
always put that field name in square brackets...

-- 
Daryl S


"buzzmcduffie" wrote:

> I have a form that needs to look up a "goal" by matching several fields in a 
> table.  I can't figure out how to do dlookup with multiple criteria
> 
> frmManualTaskDataEntry
> [employee]
> [date]
> [mailcode]
> [state]
> [disabilityind]
> [volumecode]
> 
> 
> tblMailCodeTasks
> mailcode
> state
> disabilityind
> state
> goal
0
Utf
4/22/2010 2:06:04 PM
What am I doing wrong??
Private Sub cbxCompany_AfterUpdate()
Me![Goal] = DLookup("[Goal]", "tblMailCodeTasks", tblMailCodeTasks.MailCode 
= [Forms]![frmManualTasksDataEntry]![cbxMailCodeTask]) And 
((tblMailCodeTasks.DisabilityIndicator) = 
[Forms]![frmManualTasksDataEntry]![cbxDisabilityIndicator]) And 
((tblMailCodeTasks.State) = [Forms]![frmManualTasksDataEntry]![cbxState]) And 
((tblMailCodeTasks.Active) = "yes")
End Sub

"Daryl S" wrote:

> Buzzmcduffie -
> 
> You use AND to connect the multiple criteria, and must include proper 
> delimeters for text and date fields.  It will look something like this 
> (untested):
> 
> DLookup("[goal]","[tblMailCodeTasks]","[mailcode] = '" & Me.mailcode & _
> "' AND [state] = '" & me.state & "' AND [disabilityind] = '" & _
> me.disabilityind & "'")
> 
> You have two 'state' fields listed in your table.  If one of them is really 
> date and you need to include criteria on that, then it would be like this:
> 
> DLookup("[goal]","[tblMailCodeTasks]","[mailcode] = '" & Me.mailcode & _
> "' AND [state] = '" & me.state & "' AND [disabilityind] = '" & _
> me.disabilityind & "' AND [date] = #" & me.[date] & "#")
> 
> You really should change the name of the 'date' field to something else as 
> this is a reserved word in Access, and it can cause problems.  For now, 
> always put that field name in square brackets...
> 
> -- 
> Daryl S
> 
> 
> "buzzmcduffie" wrote:
> 
> > I have a form that needs to look up a "goal" by matching several fields in a 
> > table.  I can't figure out how to do dlookup with multiple criteria
> > 
> > frmManualTaskDataEntry
> > [employee]
> > [date]
> > [mailcode]
> > [state]
> > [disabilityind]
> > [volumecode]
> > 
> > 
> > tblMailCodeTasks
> > mailcode
> > state
> > disabilityind
> > state
> > goal
0
Utf
5/11/2010 11:04:01 AM
Reply:

Similar Artilces:

Conditional Formating multiple cells in 1 row based on a list of d
Hi, I need to highlight various cells in 1 row based on a list of dates in a separate column. Look in help for conditional formatting or post a more concrete example -- Regards, Peo Sjoblom "Clinton" <Clinton@discussions.microsoft.com> wrote in message news:9631D926-8C85-47F3-8FAF-FA2321CA5B3F@microsoft.com... > Hi, > I need to highlight various cells in 1 row based on a list of dates in a > separate column. In CF you'll need the "Formula Is", rather than "Cell Value Is" option. If the formula evaluates to TR...

Multiple Calendar Reminders
Any insight on why EVERY appointmet in my calendar continues to pop-up when I open Outlook? I have closed them all and rebooted, but still hundreds of pop-up windows when I open Outlook. Any Thoughts? Try starting Outlook with the /cleanreminders switch. Start | Run | Outlook.exe /cleanreminders ....may work for you depending upon your operating system and configuration. -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP http://home.hawaii.rr.com/schorr **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. M...

dlookup with if
Access 2003 On the form called fClosure ApprovalPopUp I have a combobox called SignaturePM. The default value for SignaturePM is currently =DLookUp("[ContactPMID]","[t040Project]","[ProjectID] =" & [Forms]![fClosure]![ProjectID]) How do I edit the above DLookUP to this... =if LoginUser = PMUser then DLookUp("[ContactPMID]","[t040Project]","[ProjectID] =" & [Forms]![fClosure]![ProjectID]) if LoginUser = ManagerUser then DLookUp("[ContactPMgrID]","[t040Project]","[ProjectID] =" &a...

Multiple scales
Hi all, I have a set of data that has 3 grouping, one has <10 per quarter, one has between 30 - 80 per qtr and one has >1000 per quarter. I want to express this as 3 lines on a graph but when i use a single scale i cannot read the lower values. Is it possible to put 3 scales ona single axis. Thanks in advance Mike Hi, Here is an example with 2 y axis on one side, you can extend the idea: http://peltiertech.com/Excel/Charts/TertiaryAxis.html Instead, you might consider putting one on the right, the secondary scale. -- Thanks, Shane Devenshire "Miketsw" wrote: &...

searching multiple tables
I have a access database i have programmed and used for years. I have divided it up to have company data for each year. So each year has a MDB file with data table, multiple search queries, and a interface to click on particular queries. The data base has first name, last name,tracking number, date, and item description. I the past, i have just clicked on this years file and could search that year. What i tried to do was move data tables from past years into a new MDB file and put in a new search quires. Some of the names may be the same as does the item type. The tracking ...

Copy multiple charts
Y'all Does anyone know how to select all charts on a worksheet for copying en masse to WORD. The charts are layed out on the sheet and, currently, they are selected manually together (SHIFT+Select) copied and then pasted into WORD (paste special + picture + no float). I need a small routine that, on the press of a button, will select and copy all of the seven charts (per Word page). The actual pasting into word i'm happy to leave manual..... Mark Mark - This line copies all the charts on the active sheet. ActiveSheet.ChartObjects.CopyPicture Appearance:=xlScreen, _ F...

query on multiple tables
To all; My form has [combo2] from Classes_Available and stores the ID to be used later. when the combo2 after update opens a sub form which shows all of the classes that are scheduled from Scheduled_Classes. What I am trying to do is build a query that checks to see if a person is already scheduled for a class type so they don't get scheduled for the same class type twice... Table Students [ID] {and other info} Classes_Available [ID] [Class_Type] {and other info} Scheduled_Classes [ID] [ClassID] [Start_Date] ...

Multiple copies of messages Exchange 2003
We have been running Exchange 5.5 for a number of years with approxiamtely 150 internal mailboxes and 1200 external contacts. We have about 40 Distribution lists for various combinations of these internal and external recipients. Some DLs are "umbrella" DLs containing other DLs, which has always worked for us without a hitch Upraded to Windows Server 2003/AD Exchange 2003 in Jan 2004 and all worked fine up until May 20, 2004. When sending messages to our vaious DLs, users are complaining that they recieve multiple copies (5 seems to be the number for external contacts, 2 for interna...

Parsing normalized excel data into multiple access tables.
Using Access 97 (and strongly considering SQL Server Express) I have a data warehouse project that requires daily import of large Excel files. The files come normalized. My issue is that i'm not sure how to take the excel file and parse it into all the sub-tables within access. Let me give some background : The excel files contain a unique patient ID#. The ID# is the first field on the "primary" table in access. This primary table also includes static information like name, birthdate, SSN. A one-to-many relationship exists with several other access tables that will retain...

a statement on multiple backups
I've always stated that having multiple backups is a good idea, well last week I found out how good of an idea it was, I had recently installed a new dual core cpu ie faster better etc. I made a backup before I did the upgrade, I also installed a new larger hd for primary drive C, well to my sad suprise I found my latest update to my backups corrupted everything, and I had only one copy of my backups ie Image of Drive C. anyway I ran into multiple issues, bad SATA cable, etc, it's taken me all week to get it up and operating, when Id rather be outside playing in the sunsh...

multiple identities
Hi, I tried to add another identity on my home computer, but after I did that, I could no longer download any new headers from the main identity or the new one I had just setup. So I deleted the new identity and just tried to change the mailing information to access one of my fun accounts. Now I can't get new headers at all my my service provider, but I can at these microsoft newsgroup. Now I can't add an identity at all since the newsgroup view on Outlook XP no longer has an "Identity" option to be able to select "Manage Identities". I am XP for the OS. Am I...

Changing background color on multiple child dialog
In MFC 6.0 I used SetDialogBkColor to change the back ground color of my dialog and all the child windows. Now in .Net it has been declared deprecated. It appears that I need to use the OnCtlColor method. However this only changes a dialog background, not them all. Is there a way to change the background color of all the dialogs or do I need to ass the OnCtlColor method to all my child dialogs also? Larry You could change the system color, but you'd probably want to save and put it back afterwards. This would change all applications that are currently running or start after yo...

Save one or multiple attachments in one go
Currently I have to click on each email and manually save the attachment. Anyone have any tips that will save attachments from multiple emails is one go? I mean is there a way in MS Outlook to extract and save outlook attachments manually or automatically. See if anything here fits your needs: http://www.slipstick.com/addins/auto.htm --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer: Fareed Khan <anonymous@discussions.microsoft.com> asked: | Currently I have to click on each em...

SSL-one server and multiple domains
I am in my test environment. I have one domain on my server, testdomain.com. Mail works fine. Now I added an email domain for xyz.com. That works fine too. At least sending from joe@xyz.com to bob@testdomain.com. Now I'd like to put SSL on both domains. Is that possible? Again, they both reside on the same Exchange 2003 server. If you know that it is possile, please give me direction and not just a simple yes. I also saw an article a while back on blocking one domain from seeing the other domains contacts. I can't seem to find that article now that I want it. If you ...

Excel: Combine contents from identical cells on multiple sheets.
How do you combine contents from indentical cells on multiple sheets into one cell on a different sheet? I am working on a work schedule for partime workers who will each have a sheet assigned to them to enter their name in a cell next to the days they can work. I want their names to then appear in a master calendar in list form under the date they can work for that month. In the master sheet, try a formula like: ='Sheet One'!A1 & " " & Sheet2!A1 & " " & 'Last Sheet'!A1 I've shown [Sheet One] and [Last Sheet] as being betw...

help with range lookup and date criteria
Hello, I have a list of doctors (column A) that are each on-cal through a number of days. My argument is: if the date value of B1 an C1 is within the current date, then repeat the value of A1 -- James Spaldin ----------------------------------------------------------------------- James Spalding's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2567 View this thread: http://www.excelforum.com/showthread.php?threadid=39088 hi, James ! > ... a list of doctors (column A) that are each on-call through a number of days. > ... argument is: if the date value ...

Dlookup
Hello. I am trying to add a dlookup like I use on my forms to pull a value from an unassociated query onto my report. Is there a way I have to structure this that is different than a form? I am just looking up one field in a query called balance. The query is StatementBalance. It does not depend on anything in the report to determine the record to pull. The query has already done the work. Thanks On Thu, 6 Dec 2007 09:23:34 -0800 (PST), nybaseball22@gmail.com wrote: > Hello. I am trying to add a dlookup like I use on my forms to pull a > value from an unassociated query onto my...

Sum(if ... multiple conditions ... Interpretation?
Excel 2000 ... I attempted to write the following array formula free-hand & failed ... Consequently, I used Conditional Sum Wizard to build formula for me. Above said ... Can one of you Excel Magicians interpret (in plain English) how end of this formula works? =SUM(IF($M$2:$M$12000=$BG2,IF($T$2:$T$12000=0,1,0),0)) ,1,0),0)) What do last 3 positions & characters stand for? I am pretty certain the 1st 1,0) is part of 2nd IF Function, but have no idea how formula is working. Thanks ... Kha Look in HELP for sumif instead. This if you want to sum t for cells in m that match ...

Dlookup error
Hi, and thank you for any help. I put this section into the load event on a form: Private Sub Form_Load() ACT_TRVL = DLookup("[GTotal]", "qryTravel", "[SSN] = '" & [SSN] & "'") End Sub ACT_TRVL is connected to TblAttendance The problem I am having is that I get an error message (but it still works ok): "Object doesn't support this property or method" How do I get rid of the message or correct this. Also I have noticed that the field does not show up until I close the form and reopen. Also does this for some cont...

multiple value lookups
How do you do a lookup with multiple values "will" <anonymous@discussions.microsoft.com> wrote in message news:372DE651-4DB6-4823-90C8-6625C176133E@microsoft.com... > How do you do a lookup with multiple values Your question could have been clearer! If you want to do a two-dimensional lookup, this formula looks up A1(row) and B1 (column) in an array named 'Table': =INDEX(Table,MATCH(A1,INDEX(Table,,1),0),MATCH(B1,INDEX(Table,1,),0)) If you want to do a double-column lookup, this formula looks up D1 in column A and E1 in column B, returning the column C value corr...

Opening Multiple mailboxes
I have a customer who has 4 mailboxes listed in her mailbox in OUtlookXP. Usually when you open several mailboxes, it shows up as "Mailbox - Lastname, Firstname". On two of the mailboxes she adds, when she first adds them, the display is correct. After a few hours, it dropes the words "Mailbox -" and only shows the Lastname, Firstname. She can remove and readd and the same thing happens. She is demanding that they show up in alphabetical order. She has all of the latest SPs. Has anyone seen this? ...

Duplicating multiple items by multiple customers
I need to be able to do something and I'm not sure if it's possble. I need to be able to enter several item like the following: ProductID Product Discription 87765 Ball Baseball 25934 Bat Wooden Bat 59842 Glove Leather Glove The number of items may change depending on how many items are ordered. Once I have the order, I want to be able to enter all the customer numbers (e.g. CustID) who have placed this exact order because it could have been 20 customers. This way I would end up with 60 entries like the following: CustID Product...

How to open multiple cases
Does anyone know if there's a way to open a case for several companies at one time? I've got a situation where I need to deploy a service bullitin for serveral companies. The title, description, etc.. are all the same. Thanks. Hmmm. Do you really need to open cases at first? If it's just a bulletin, then maybe you create a campaign to notify the appropriate customers, and then track the responses against the campaign. -- Microsoft Certified Professional - Dynamics CRM http://www.bturnkey.com "Coco" wrote: > Does anyone know if there's a way to open a ca...

Copying multiple rows from different spreadsheet based on a logic
Dear Friends, Need your help in the following : Here is my requirement: I have a worksheet which has columns 1,3,6,8. Which has few rows with a unique values in column 1. I get a spreadsheet everyday with columns 1,2,3,4,5,6,7,8,9,10. I need to insert new rows with only columns 1,3,6,8. I need a button using which I should be able to update. It would be great if the macro gives me an option of selecting the file. Thanks in advance. You might try hiding the undesired columns>f5 visible cells only>copy/paste. Or a macro. -- Don Guillett SalesAid Software dguillett1@austin.rr.com...

Multiple check deposits from yourself
I remember a while back someone asked how to record a transaction in which you wrote yourself several checks from one account and deposited them all at once to another account. I did exactly this the other day and wanted to share how it can be done: First go to the register for the account you are depositing the checks to. Instead of clicking "transfer", select "deposit". Then click to split the categories. From here enter each check as a seperate split transfer category from your other account. It may be helpful to enter check numbers on the memo line since you can't ...