I'm stumped with this query

[edkocol]

I'm stumped with this query - solved - see post #9

[EDIT:] SOLVED see post #9

This query:

SELECT B_Inventory.Barcode, 
           B_Inventory.QtyOnHand, 
           B_Inventory.QtyInPO, 
           A_ScriptPODetail.Quantity, 
           A_ScriptPODetail.ScriptPOID
   FROM B_Inventory INNER JOIN A_ScriptPODetail ON B_Inventory.Barcode=A_ScriptPODetail.Barcode
 WHERE (((B_Inventory.QtyOnHand)=(B_Inventory.QtyInPO)) 
     And ((B_Inventory.QtyInPO)>0) 
     And ((A_ScriptPODetail.Quantity)=(B_Inventory.QtyInPO)) 
     And ((A_ScriptPODetail.Shipped)=(Yes)));

Produces these results:

Yet, this query:

SELECT B_Inventory.Barcode, 
           B_Inventory.QtyOnHand, 
           B_Inventory.QtyInPO, 
           A_ScriptPODetail.Quantity, 
           A_ScriptPODetail.ScriptPOID 
   FROM B_Inventory INNER JOIN A_ScriptPODetail ON B_Inventory.Barcode=A_ScriptPODetail.Barcode
 WHERE (((B_Inventory.QtyOnHand)=(B_Inventory.QtyInPO)) 
     And ((B_Inventory.QtyInPO)>0) And ((A_ScriptPODetail.Quantity)=(B_Inventory.QtyInPO)) 
     And ((A_ScriptPODetail.Shipped)=(Yes)) 
     And ((A_ScriptPODetail.ScriptPOID)=22252));

Notice the

And ((A_ScriptPODetail.ScriptPOID)=22252))

at the end....

Produces these results:

I am baffled. What am I missing?

 

[Alansidman]

is 22252 a text value? If it is formatted in the table as a text value, you will need to surround it in quotes, ie. "22252"

Alan

 

[edkocol]

Sorry, I should have said it is a Number.

 

[Alansidman]

Indulge me. Try surrounding the value in quotes and advise what happens.

 

[edkocol]

Already did that. I was told:

Data misimatch type in criteria expression

 

[Alansidman]

Thanks; just checking. Can you upload a copy of your DB so that we can do some analysis. Run a compact and repair before zipping. If you have confidential information in it, dummy those records up. Also, don't need all the data, just some sample data to run analysis on.

 

[edkocol]

I wonder if this has a bearing?

A_ScriptPODetail.ScriptPOID is defined in the table design as a lookup based on another table's primary key (A_ScriptPO.ScriptPOID = autonumber).

 

[Alansidman]

A definite possibility. While you have brought that up, look at this link. You should not be doing lookups from your table, but rather in your forms. You will have to link that other table to your query.

http://access.mvps.org/access/lookupfields.htm

Alan

 

[edkocol]

Re: I'm stumped with this query - Solved

Solved.

The lookup specs for column A_ScriptPODetail.ScriptPOID is displaying the value of A_ScriptPO.PurchaseOrderNumber not A_ScriptPO.ScriptPOID.

I now understand the relationship of these two table.

BTW, I "inherited" this system and I am learning it as I go.