VBA, DLookup with multiple criteria A97

DLookup with multiple criteria A97

Author	Message
Kahun #1 / 7	DLookup with multiple criteria A97 Hi Folks Any offers on how to apply syntax DLookup with multiple criteria. Cheers -- Kahuna
Wed, 18 May 2005 21:36:44 GMT

Marshall Barto #2 / 7	DLookup with multiple criteria A97 Quote: >Any offers on how to apply syntax DLookup with multiple criteria. You can use the AND and OR operators with parenthesis to combine lots of criteria. E.g. DLookup("f1", "table", "f2 = 9 AND (f3 = 'a' OR f3 Is Null") -- Marsh MVP [MS Access]
Wed, 18 May 2005 22:28:49 GMT

John Vinso #3 / 7	DLookup with multiple criteria A97 Quote: >Hi Folks >Any offers on how to apply syntax DLookup with multiple criteria. Sure. The third argument to DLookUp (or DMax or DMin or any domain function) is a valid SQL WHERE clause without the WHERE. One way to get the syntax is to use the Query grid to create a query which finds the data that you want to look up; go into SQL view and see it. You'll then need to build this SQL string up from its components - if the query references form controls, for instance, you will want to concatenate their values into the DLookUp third argument rather than their names: DLookUp("[LastName]", "[tblPersonnel]", "[DepartmentID] = " & Forms!myform!cboDepartmentID & " AND [Position] IN ('Manager', 'Foreman', 'High Muck-a-Muck') AND HireDate < #" & Forms!myform!txtHiredBefore & "#") John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public
Thu, 19 May 2005 02:27:09 GMT

Kahun #4 / 7	DLookup with multiple criteria A97 Thanks Marsh, is there a ')' missing? I'll try it anyway. Cheers -- Kahuna Quote: > >Any offers on how to apply syntax DLookup with multiple criteria. > You can use the AND and OR operators with parenthesis to > combine lots of criteria. E.g. > DLookup("f1", "table", "f2 = 9 AND (f3 = 'a' OR f3 Is Null") > -- > Marsh > MVP [MS Access]
Thu, 19 May 2005 20:16:36 GMT

Kahun #5 / 7	DLookup with multiple criteria A97 Exceptional as always John, thanks. The advice ".....is a valid SQL WHERE clause without the WHERE" put it all in perspective. Cheers -- Kahuna Quote: > >Hi Folks > >Any offers on how to apply syntax DLookup with multiple criteria. > Sure. The third argument to DLookUp (or DMax or DMin or any domain > function) is a valid SQL WHERE clause without the WHERE. One way to > get the syntax is to use the Query grid to create a query which finds > the data that you want to look up; go into SQL view and see it. > You'll then need to build this SQL string up from its components - if > the query references form controls, for instance, you will want to > concatenate their values into the DLookUp third argument rather than > their names: > DLookUp("[LastName]", "[tblPersonnel]", "[DepartmentID] = " & > Forms!myform!cboDepartmentID & " AND [Position] IN ('Manager', > 'Foreman', 'High Muck-a-Muck') AND HireDate < #" & > Forms!myform!txtHiredBefore & "#") > John W. Vinson[MVP] > Come for live chats every Tuesday and Thursday > http://go.compuserve.com/msdevapps?loc=us&access=public
Thu, 19 May 2005 20:17:39 GMT

Marshall Barto #6 / 7	DLookup with multiple criteria A97 Quote: >Thanks Marsh, is there a ')' missing? I'll try it anyway. Yes, you caught that one, good eyes. -- Marsh
Thu, 19 May 2005 22:01:21 GMT

Debbie #7 / 7	DLookup with multiple criteria A97 I LOVE these newsgroups! I was having trouble with a DLOOKUP because it involved a date field. I couldn't figure out the syntax but I found the answer right here from John. THANKS! Quote: > >Hi Folks > >Any offers on how to apply syntax DLookup with multiple criteria. > Sure. The third argument to DLookUp (or DMax or DMin or any domain > function) is a valid SQL WHERE clause without the WHERE. One way to > get the syntax is to use the Query grid to create a query which finds > the data that you want to look up; go into SQL view and see it. > You'll then need to build this SQL string up from its components - if > the query references form controls, for instance, you will want to > concatenate their values into the DLookUp third argument rather than > their names: > DLookUp("[LastName]", "[tblPersonnel]", "[DepartmentID] = " & > Forms!myform!cboDepartmentID & " AND [Position] IN ('Manager', > 'Foreman', 'High Muck-a-Muck') AND HireDate < #" & > Forms!myform!txtHiredBefore & "#") > John W. Vinson[MVP] > Come for live chats every Tuesday and Thursday > http://go.compuserve.com/msdevapps?loc=us&access=public
Mon, 23 May 2005 07:11:03 GMT

Page 1 of 1

[ 7 post ]