multiple criteria in DLookup

[crowegreg]

Are you able to have 2 criterias in a DLookup statement? Here's the vital info:
Expr: OnHandQty
table name: ICPartWarehouse
criteria: FKICPart = me.FKICPart and WarehouseNumber = 3

Here's what I have so far:
=DLookUp("OnHandQty","ICPartWarehouse",????)

Thanks for your assistance!!

 

[John Big Booty]

Try;

DLookup("OnHandQty", "ICPartWarehouse", "[FKICPart] = " & me.FKICPart & " And WarehouseNumber = 3")

 

[crowegreg]

Thanks for the quick reply. I think I didn't give you all the information needed. Both of the criterias are fields within the table. So both FKICPart and WarehouseNumber are the field names within the table ICPartWarehouse.

 

[John Big Booty]

OK so what are the field names you are trying to match them with in Table ICPartWarehouse?

 

[crowegreg]

For the field name WarehouseNumber it will be 3. For the field name FKICPart it will be from a textbox on the form, text180.

 

[John Big Booty]

Can you post a copy of your DB?

 

[crowegreg]

I had to zip the file. The form is frmMasterTitle. On the tab "BWLookup". Text192 is the dlookup statement I'm working on. I'm been testing different things, so what's their is not what you've previously recommended.

 

[John Big Booty]

I don't currently have access to '07, so will take a look at it latter this arvo.

 

[crowegreg]

Thanks for your assistance.

 

[crowegreg]

I noticed that I had an incorrect field name previously. Here's the last criteria I tried.
"[FKICPart] = '" & [text190] & "' And [WarehouseNo] = " & 3)

 

[John Big Booty]

Perhaps the discussion here might help.

 

[crowegreg]

Here's what I've tried. I don't think I've written, an FKICPart can exist in muliple warehouses. I've been separating the criteria to make sure they both work on their own. If I use this statement, =DLookUp("OnHandQty","ICPartWarehouse","WarehouseNo =3"), this works properly. If I manipulate the data, then try this statement, =DLookUp("OnHandQty","ICPartWarehouse","FKICPart =" & [text190]), this also works. But if I combine the criteria into 1 statement, =DLookUp("OnHandQty","ICPartWarehouse","FKICPart ='" & [text190] & "'" And "WarehouseNo =3"), it does not work. By manipulating the data and testing it, the DLookup is not recognizing the second criteria.

 

[John Big Booty]

Sorry is this '10 version of Access?

 

[crowegreg]

Yes. What does that tell you.

 

[crowegreg]

I've resorted to using VBA code behind the form to retrive these values. It worked the first time. You think it's a problem with Access 2010 Dlookup?

 

[John Big Booty]

Sorry I don't have Access '10 so can't comment

 

[crowegreg]

I appreciate all your help. I think I tried every possible syntax, and nothing seemded to work. Here's the code I wrote:
Dim varX As Long
Dim strtest As String
varX = DLookup("SKICPart", "ICPart", "ID ='" & [ISBNBW] & "'")

strtest = "FKICPart = " & varX & " And WarehouseNo = 3"


Me.Text194 = DLookup("[OnHandQty]", "ICPartWarehouse", strtest)

 

[JANR]

Your first problem is that you dlookup in text194 is based on the fact that text190 finds a record, if it dosen't find a record dlookup returns Null and then text194 will give an error.

so for text190:

=Nz(DLookUp("SKICPart","ICPart","ID ='" & [ISBNBW] & "'"),0)

your next problem is that since text190 returns a NUMBER your delimiter around '" & [text190] & "'" will be wrong.

so for text194:

=DLookUp("OnHandQty","ICPartWarehouse","FKICPart =" & [text190] & " And [WarehouseNo] =3")

JR