MS Access strings not matching in Query

Question

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];

jhTuppeny · Accepted Answer · 2017-02-07 13:04:20Z

1

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).

edited Feb 7, 2017 at 13:04

answered Feb 6, 2017 at 20:22

jhTuppeny

9901 gold badge12 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Newd Over a year ago

I do believe this is the answer, but you should include why you think this is necessary so future readers can apply it to their situation as well.

bbadgett Over a year ago

This worked, but I don't understand why. The only difference I can see is I used '+' to concatenate and you used '&'. Are the two operations different?

Newd Over a year ago

@bbadgett + is generally used for math with numbers and & is used for concatenation strings. So when you try to "add" 01 it becomes 1 because + handles numerical values. This works fine with numbers above 9 because in the numerical world, 10 = 10, but 01 = 1. So instead of your query comparing "abcd-01" it is comparing "abcd-1". Best I can guess anyway.

Collectives™ on Stack Overflow

MS Access strings not matching in Query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related