0

In Access, I have a table named tblBundle and am trying to query the last step that was completed on every bundle in an order.

tblBundle contains: Work Order, Bundle #, BundleLtr, Step, Complete [boolean]

I ultimately want to copy this into vba to create a list of the Bundle #, Ltr, and last step completed for continued data entry on the next step completed.

For some reason I cannot ascertain, the SQL query below returns 2 records for bundle 1 (190, 200) when I believe it should only be returning one record - 200.

SELECT Max(tblBundle.Step) AS intLstep, tblBundle.BundleNbr, tblBundle.BundleLtr, 
tblBundle.Complete
FROM tblBundle
GROUP BY tblBundle.WO, tblBundle.BundleNbr, tblBundle.BundleLtr, tblBundle.Complete
HAVING (((tblBundle.WO)="195687-1-1") AND ((tblBundle.Complete)=True));

Query Output

Can anyone help me figure out why my query is returning the extra value?

SamwiseVB
  • 51
  • 8
  • 3
    Possibly there is a non-visible difference in `BundleLtr` between those two records. Eg. one is null and the other is an empty string. – Tim Williams Jan 10 '22 at 23:56
  • The interesting thing is they are both null and confirmed with the zoom window. Also, records for Bundle 2 are exactly the same as Bundle 1 since they were copied and pasted. So I'm curious as to why Bundle 2 doesn't show up with 190 too – SamwiseVB Jan 11 '22 at 00:09
  • I'm not an Access user so I'm not sure what the "zoom window" is, but I'd find it hard to believe that both of those first two records have the same values in `BundleNbr` and `BundleLtr` – Tim Williams Jan 11 '22 at 01:29
  • @samwiseVB the best way to confirm whether you have either nulls or empty strings or both in your aggregate query results is to query for them explicitly in tblBundle. I wouldn't trust the zoom window for this purpose. – bugdrown Jan 11 '22 at 02:35
  • zoom is when you click in the cell, shift+f2 and it opens a larger window of what's in the cell. Also, when filtering the field the options are (blanks), a, b – SamwiseVB Jan 11 '22 at 07:57

3 Answers3

1

In MsAccess, NULL values appear as blanks when viewed on the Datasheet view. Also, it seems that when blanks are entered for a ShortText column on the Datasheet view, they are turned into NULLs (on my version of MsAccess). It seems that your data may have NULL for one of the Steps (say 200), and spaces (zero or more) for the other (say Step 190), you could force your query to treat both the same way:

SELECT Max(tblBundle.Step) AS intLstep, tblBundle.BundleNbr,
 nz(tblBundle.BundleLtr,'') as BundleLtr,
 tblBundle.Complete
FROM tblBundle
GROUP BY tblBundle.WO, tblBundle.BundleNbr,
         nz(tblBundle.BundleLtr,''), tblBundle.Complete
HAVING (((tblBundle.WO)="195687-1-1") AND ((tblBundle.Complete)=True));

Please note that the invisible character can, in fact, be a character that looks like space, but it is not (like non-breaking space), etc. If the above solution does not work, try something like:

SELECT  asc(mid(BundleLtr, 1,1)), asc(mid(BundleLtr, 2,1)) FROM Table2 where Step=190

To see the ascii values of the data in that column.

tinazmu
  • 3,880
  • 2
  • 7
  • 20
0

It seems like because your query is breaking down the the result and also returning BundleNbr is causing it to take both Max because its valid.

Also you break this down in your Group By statement and aggregate by that.

Both values of 190 and 200 have a BundleNbr of 1. If you remove that column you'll get the actual max value of 200 (If you want the max of intLStep.

Ankhit Sharma
  • 367
  • 1
  • 5
  • 16
  • I'm wanting the query to return the BundleNbr/Ltr and the last step completed on each bundle #/Ltr combo. The returned values are correct except for Bundle 1 Step 190. It is confirmed the BundleLtr is Null for both entries – SamwiseVB Jan 11 '22 at 00:11
0

I was able to use this following function to "clean up" my string and remove the additional 'Bundle 1'. This does not solve the original question, but works for my purposes.

'[Removes duplicate values from a string]
Function DeDupeString(ByVal sInput As String, Optional ByVal sDelimiter As 
String = ",") As String

Dim varSection As Variant
Dim sTemp As String

For Each varSection In Split(sInput, sDelimiter)
    If InStr(1, sDelimiter & sTemp & sDelimiter, sDelimiter & varSection & sDelimiter, vbTextCompare) = 0 Then
        sTemp = sTemp & sDelimiter & varSection
    End If
Next varSection

DeDupeString = Mid(sTemp, Len(sDelimiter) + 1)

End Function

Found From: Removing Duplicate values from a string in VBA

SamwiseVB
  • 51
  • 8