CODEWITHSUNDEEP
ms-access
bbadgett
bbadgett

Reputation: 60

MS Access strings not matching in Query

I have two externally linked tables in my Access DB. Table tblBatches contains, among others, two separate fields for PO and LineItem such as

Batch       PO         LI
1234567  4101234567     1
1234568  4101234567     4
1234569  4107654321    13
...

I have another table tblWIP that contains a field PO-LineItem such as:

PO-LI              Date
4101234567-01     1/1/2016
4101234567-04     7/7/2016
4107654321-13     12/30/2016
...

All fields are stored as ShortText.

Since the tables are linked to external Excel files, I created a query qryBatchByPO-LI to process the PO and LI fields from tblBatches using the expression:

PO LI: [Purchasing Document]+"-"+Format([Purch# Doc: Item No#],"00")

I want to run a query that spits out Batch and Date as linked by the PO LI from my intermediate query, but it doesn't seem to be working properly.

Namely, it doesn't seem to be linking anytime the line item is less than 10 (therefore only one digit in the first table).

So, my query based on the above examples would only return:

Batch        PO-LI        Date
1234569  4107654321-13  12/30/2016

I've tried modifying my expression in a few ways, including: 

PO LI: [Purchasing Document]+"-"+IIF(len([Purch# Doc: Item No#])<2,"0"+[Purch# Doc: Item No#],[Purch# Doc: Item No#])

I've even tried making a query to process the PO-LI field in the second table by splitting and rejoining the strings, to try and make sure everything is stored as strings, but no luck. In all situations, the results of my intermediate queries look correct, but any line items <10 are not appearing in my final queries.

I have no idea why these items aren't appearing, as again, to my eye the PO LI expression in qryBatchByPO-LI ends up looking the same and matching up with the PO-LI field in the tblWIP. Any help would be appreciated :/

I built the query using design view, but the SQL code ends up:

SELECT [qryBatchByPO-LI].Batch, [qryBatchByPO-LI].[PO LI], 
       tblBatches.[Short text], tblWIP.[Date]
FROM [qryBatchByPO-LI] 
INNER JOIN tblEDSS_WIP ON [qryBatchByPO-LI].[PO LI] = tblEDSS_WIP.[PO-LI];

Upvotes: 1

Views: 673

Answers (1)

jhTuppeny
jhTuppeny

Reputation: 970

Build a query using this SQL;

SELECT tblBatches.BatchID, tblBatches.PO, tblBatches.LI, [PO] & '-' & IIf(Len([LI])<2,'0' & [LI],[LI]) AS PO-LI FROM tblBatches;

Then save this query as qdfOne. Then build another query and link to the table tblWIP like this;

INNER JOIN qdfOne ON tblWIP.PO-LI = qdfOne.PO-LI

When I do this I get all line items returned 1,4 & 13.

The difference is the use of the '&' to concatenate the strings. Using a '+' can end up treating the strings as numbers so that '01' (string) becomes '1' (number).

Upvotes: 1

Related Questions