Incorrect Dates, Help, I Need Somebody Help!

[DMT Dave]

Hi Guy's, i have spent days on this and can't figure out why i am returning incorrect dates, some delivery dates and collection dates don't match the dates in the tables for the searched (double click event)



I think have have now gone past myself to be able to rectify, if you guy's can see something and kindly simplify this would be totally appreciated ?

In the iItemNo field, there is always 6 digits followed by hyphen and a client name something like

123456-Smith
987654-Jones

WHILE IT IS NOT IDEAL, I CHANGED iLiftNo from Integer to Long but didn't' change all of the code, I can do that if i thought that was causing the incorrect dates problem!

iItemNo = Left(Me.ItemNo, 6)strMFG = iItemNo 'Currently doing nothing
    If IsNull(iItemNo = DLookup("ItemNo", "tblStock", "ItemNo Like ""*" & iItemNo & "*""")) Then
        strBody = "There are No MFG No: " & iItemNo & " " & "In Stock!" & "|"
    End If
    If Not IsNull(iItemNo = DLookup("ItemNo", "tblStock", "ItemNoLike ""*" & iItemNo& "*""")) Then
        iSortNo = DLookup("SortNo", "tblStock", "ItemNo Like ""*" & iItemNo& "*""")
        strType = DLookup("ItemType", "tblStock", "[SortNo] = " & iSortNo)
    
        Set rs = CurrentDb.OpenRecordset("Select * From tblStock WHERE ItemType = '" & strType & "'")
        Do Until rs.EOF
        If rs.Fields("NewQty") <> "0" Then
        strAV = "Available Qty " & FontStart & "<B>" & "<font color='black'>" & rs.Fields("NewQty") & "</B></font>"
        End If
        If rs.Fields("NewQty") = "0" Then
        strAV = "Available Qty " & FontStart & "<B>" & "<span style='background:yellow'>" & rs.Fields("NewQty") & "</font></B></span>"
        End If
        
        strBody = strBody & rs.Fields("ItemType") & " - " & "MFG No: " & rs.Fields("ItemNo") & " - " & "Warehouse Qty: " & rs.Fields("StartQty") & _
        " - " & "Allocated Qty: " & rs.Fields("AllocatedQty") & " - " & strAV & "|"
        rs.MoveNext
        Loop
    End If
        
        Set rs2 = CurrentDb.OpenRecordset("Select * From tblEdit WHERE ItemNo Like ""*" & iItemNo & "*"" ORDER BY tblEdit.DelTo;")
        Do Until rs2.EOF
        
        If rs2.Fields("Status") = "Collection" Then
            strStatus = DLookup("Status", "tblCollections", "ItemNo Like ""*" & iItemNo & "*""")
                If Right(strStatus, 3) = "ted" Then ' Collected (Already Shipped)
                dteColDate = DLookup("CollectedDate", "tblCollections", "[ItemNo] Like ""*" & iItemNo & "*""")
                strValue = "&emsp;" & "&emsp;" & rs2.Fields("DelTo") & " - " & rs2.Fields("PONumber") & " - " & rs2.Fields("SONumber") & " - " & UCase(rs2.Fields("ItemNo")) & " - " & Chr(9) & strStatus & " - " & Format(dteColDate, "ddd-dd-mmm-yyyy")
                End If
            If Right(strStatus, 3) = "ion" Then ' Collection (Not shipped Yet)
            dteColDate = Null
            ShipDate = "Waiting Collection"
            strValue = "&emsp;" & "&emsp;" & rs2.Fields("DelTo") & " - " & rs2.Fields("PONumber") & " - " & rs2.Fields("SONumber") & " - " & UCase(rs2.Fields("ItemNo")) & " - " & Chr(9) & strStatus & " - " & "Waiting Collection"
            End If
        End If
    If rs2.Fields("Status") = "Delivery" Then
        strStatus = rs2.Fields("Status")
        dteDelDate = DLookup("DeliveryDate", "tblAssign", "ItemNo Like ""*" & iItemNo & "*""")
        strValue = "&emsp;" & "&emsp;" & "&emsp;" & rs2.Fields("DelTo") & " - " & rs2.Fields("PONumber") & " - " & rs2.Fields("SONumber") & " - " & UCase(rs2.Fields("ItemNo")) & " - " & Chr(9) & strStatus & " - " & Format(dteDelDate, "ddd-dd-mmm-yyyy")
    End If
    If rs2.Fields("Status") = "Planning" Then
        strStatus = rs2.Fields("Status")
        strValue = rs2.Fields("DelTo") & " - " & rs2.Fields("PONumber") & " - " & rs2.Fields("SONumber") & " - " & UCase(rs2.Fields("ItemNo")) & " - " & Chr(9) & strStatus & " - " & "Not Shipped"
    End If
    If IsNull(rs2.Fields("Status")) Then
        strValue = rs2.Fields("DelTo") & " - " & rs2.Fields("PONumber") & " - " & rs2.Fields("SONumber") & " - " & UCase(rs2.Fields("ItemNo")) & " - " & Chr(9) & strStatus & " - " & "No Status"
    End If
    If rs2.Fields("Status") = "On Hold" Then
        strDate = ""
        strStatus = strMark & rs2.Fields("Status") & strMarkEnd
        strValue = rs2.Fields("DelTo") & " - " & rs2.Fields("PONumber") & " - " & rs2.Fields("SONumber") & " - " & UCase(rs2.Fields("ItemNo")) & " - " & Chr(9) & strStatus
    End If
    If rs2.Fields("Status") = "Friday" Then
        strStatus = "Due In: " & rs2.Fields("Status")
        strDate = ""
        strValue = rs2.Fields("DelTo") & " - " & rs2.Fields("PONumber") & " - " & rs2.Fields("SONumber") & " - " & UCase(rs2.Fields("ItemNo")) & " - " & Chr(9) & strStatus
    End If
    If rs2.Fields("Status") = "Monday" Then
        strStatus = "Due In: " & rs2.Fields("Status")
        strDate = ""
        strValue = rs2.Fields("DelTo") & " - " & rs2.Fields("PONumber") & " - " & rs2.Fields("SONumber") & " - " & UCase(rs2.Fields("ItemNo")) & " - " & Chr(9) & strStatus
    End If
    sBody = sBody & strValue & "|"
    rs2.MoveNext
    Loop


    Debug.Print sBody
    
        strA = "Hello, hope you are safe and well.|"
        strB = "This listing is current stock details for " & strType & "|"


        Set myItem = myApp.CreateItem(olMailItem)
        Set OutAccount = myApp.Session.Accounts.Item(1)
        With myItem
        .subject = strType & " " & iItemNo & " Stock Details"
        .To = ""
        .HTMLBody = FontStart & Replace(strA, "|", "<br>") & "<br>" & "<br>" & Replace(strB, "|", "<br>" & "<br>") & "<br>" & "<br>" & BoxStart & "<br>" & _
        Replace(strBody, "|", "<br>" & "<br>") & "<br>" & BoxEnd & "<br>" & "<br>" & _
        FontStart & BoxStart & "<br>" & strMFG & " History" & "<br>" & "<br>" & Replace(sBody, "|", "<br>" & "<br>") & "<br>" & BoxEnd & "<br>" & "<br>" & _
        wkr & "<br>" & "<br>" & _
        UserfName & FontEnd & "<br>" & "<br>" & _
        "<P><IMG border=0 hspace=0 alt='' src='file://T:/Logo Media/" & SigFile & "' align=baseline></P>" & "<br>" & "<br>" & _
        "<FONT color=#00008B>" & eDisc & "<br>" & "<FONT color =#00008B>" & eDisc2
        .SendUsingAccount = OutAccount
        .ReadReceiptRequested = True
        .Display
        End With
End If

[June7]

That's a lot of code to decipher. Exactly where is the filter you say is not working correctly?

[CJ_London]

just a guess but these do not look right

DLookup("CollectedDate", "tblCollections", "[ItemNo] Like ""*" & iItemNo & "*""")
DLookup("DeliveryDate", "tblAssign", "ItemNo Like ""*" & iItemNo & "*""")

unless iItemNo is unique to whatever you are trying to do (would have thought you would have a customer or order id as well) - and why use like? if possible item numbers are

12345
345

then when looking for 345, you are just as likely to find 12345

[DMT Dave]

Hi June7, the filter is iItemNo (left 6 digits of the Item No Field)

at the start of the code is: iItemNo = Left(Me.ItemNo, 6)

Forget the rs setting and loop, that works fine looking in the stock table

it is the tblAssign and tblCollections tables that have sporadic different dates

The event is double click that field

It is this code thats returning wrong dates

 Set rs2 = CurrentDb.OpenRecordset("Select * From tblEdit WHERE ItemNo Like ""*" & iItemNo & "*"" ORDER BY tblEdit.DelTo;")
        Do Until rs2.EOF
        
        If rs2.Fields("Status") = "Collection" Then
            strStatus = DLookup("Status", "tblCollections", "ItemNo Like ""*" & iItemNo & "*""")
                If Right(strStatus, 3) = "ted" Then ' Collected (Already Shipped)
                dteColDate = DLookup("CollectedDate", "tblCollections", "[ItemNo] Like ""*" & iItemNo & "*""")
                strValue = " " & " " & rs2.Fields("DelTo") & " - " & rs2.Fields("PONumber") & " - " & rs2.Fields("SONumber") & " - " & UCase(rs2.Fields("ItemNo")) & " - " & Chr(9) & strStatus & " - " & Format(dteColDate, "ddd-dd-mmm-yyyy")
                End If
            If Right(strStatus, 3) = "ion" Then ' Collection (Not shipped Yet)
            dteColDate = Null
            ShipDate = "Waiting Collection"
            strValue = " " & " " & rs2.Fields("DelTo") & " - " & rs2.Fields("PONumber") & " - " & rs2.Fields("SONumber") & " - " & UCase(rs2.Fields("ItemNo")) & " - " & Chr(9) & strStatus & " - " & "Waiting Collection"
            End If
        End If
    If rs2.Fields("Status") = "Delivery" Then
        strStatus = rs2.Fields("Status")
        dteDelDate = DLookup("DeliveryDate", "tblAssign", "ItemNo Like ""*" & iItemNo & "*""")
        strValue = " " & " " & " " & rs2.Fields("DelTo") & " - " & rs2.Fields("PONumber") & " - " & rs2.Fields("SONumber") & " - " & UCase(rs2.Fields("ItemNo")) & " - " & Chr(9) & strStatus & " - " & Format(dteDelDate, "ddd-dd-mmm-yyyy")
    End If
    If rs2.Fields("Status") = "Planning" Then
        strStatus = rs2.Fields("Status")
        strValue = rs2.Fields("DelTo") & " - " & rs2.Fields("PONumber") & " - " & rs2.Fields("SONumber") & " - " & UCase(rs2.Fields("ItemNo")) & " - " & Chr(9) & strStatus & " - " & "Not Shipped"
    End If
    If IsNull(rs2.Fields("Status")) Then
        strValue = rs2.Fields("DelTo") & " - " & rs2.Fields("PONumber") & " - " & rs2.Fields("SONumber") & " - " & UCase(rs2.Fields("ItemNo")) & " - " & Chr(9) & strStatus & " - " & "No Status"
    End If
    If rs2.Fields("Status") = "On Hold" Then
        strDate = ""
        strStatus = strMark & rs2.Fields("Status") & strMarkEnd
        strValue = rs2.Fields("DelTo") & " - " & rs2.Fields("PONumber") & " - " & rs2.Fields("SONumber") & " - " & UCase(rs2.Fields("ItemNo")) & " - " & Chr(9) & strStatus
    End If
    If rs2.Fields("Status") = "Friday" Then
        strStatus = "Due In: " & rs2.Fields("Status")
        strDate = ""
        strValue = rs2.Fields("DelTo") & " - " & rs2.Fields("PONumber") & " - " & rs2.Fields("SONumber") & " - " & UCase(rs2.Fields("ItemNo")) & " - " & Chr(9) & strStatus
    End If
    If rs2.Fields("Status") = "Monday" Then
        strStatus = "Due In: " & rs2.Fields("Status")
        strDate = ""
        strValue = rs2.Fields("DelTo") & " - " & rs2.Fields("PONumber") & " - " & rs2.Fields("SONumber") & " - " & UCase(rs2.Fields("ItemNo")) & " - " & Chr(9) & strStatus
    End If
    sBody = sBody & strValue & "|"
    rs2.MoveNext
    Loop


    Debug.Print sBody

[DMT Dave]

Hi Ajax, the ItemNo field will have both numeric and text

So the field I am double clicking may have

123456-Jones
123456-Smith
123456-Ajax

etc.. so that's why i am using wildcards, iItemNo is the first 6 digits thinking that the number I am looking for data on and anything else that is like it

please correct me if i am going the wrong way about it

it's DeliveryDate and CollectedDate in rs2 that's returning sporadic wrong Dates

[Minty]

I would think it's because a dlookup that is vague in it's criteria (e.g. Like*123*) will only return the first record it finds.
So this

dteColDate = DLookup("CollectedDate", "tblCollections", "[ItemNo] Like ""*" & iItemNo & "*""")

Will return the Collection date of the first item it see's, not the one you are necessarily after.

[Welshgasman]

Walk through the code line by line.
Why like? as DLookup will only return the first it finds.
I would use Select case for the status, as it can only be one value at a time.

[CJ_London]

you don't need the initial *

and if searching for '123456' you could get any of these returned -

123456-Jones
123456-Smith
123456-Ajax

dlookup is random in that it will return the first match it finds, which may not be the same as you think it should be

Or are you saying the number is unique so you don't have

123456-Jones
123456-Smith
123456-Ajax

but

123451-Jones
123452-Smith
123453-Ajax

[DMT Dave]

The Number is unique, If i double click on the field, it returns all 123456 that has been through the database

123456-Jones
123456-Smith
123456-Ajax

It can't return 987654 (only 123456) and anything after it

The correct amount of records return, all other data PO, SO numbers and everything are all correct, it is the 2 dates (DeliveryDate) and CollectedDate) that are wrong

WGM, if I debug.Print and press Ctrl+G then F8, that will show results in immediate window

but the immediate window is showing where i want to space then:

strValue = " " & " ", is showing in immediate window

would it be then something like:

Should remove " " and change to vbTab ?

remove DLookup

select Case rs.Fields("status")

Case is "Delivery"
dteDelDate = rs.fields("DeliveryDate")

Case is "Collection"
dteColDate = rs2.fields("CollectedDate")

I think i have lost myself lol

[CJ_London]

The Number is unique, If i double click on the field, it returns all 123456 that has been through the database

123456-Jones
123456-Smith
123456-Ajax

how can the number be unique when you say 'it returns all 123456 that has been through the database'

[orange]

Dave,

Can you provide a sample db (with only a few records) that has this issue?
You've posted a lot of code for readers to review and understand.
I think a sample db will get you focused responses.
I also think we need a clear, perhaps reworded, statement of the requirement.

[DMT Dave]

Sorry Ajax, i may have explained incorrectly, 123456 is a stock number

We may have added 20 the system 20 x 123456

rs is looking to see what stock remains of 123456 also allocated and available, this is working fine

rs2 is checking tblEdit (where these are allocated to customer)

If we assign for delivery, they go to tblAssign
if we assign for collection they go to tblCollections
If there are some left in Stock (tblStock)

so there could be 3 x 123456 in tblStock

so there could be 6 x 123456 added to tblEdit
123456-Jones Status field "Planning" (Not Shipped)
123456-Ajax Status Field "Collection"
etc etc

4 x 123456 in tblCollections

so im trying to loop through

tblAssign and tblCollections WHERE the item no = 123456 & "*"
return delivery dates from tblAssign
return collected dates from tblCollections

I will snip a result as an example but just wrong dates are returning

please forgive me for long winded reply but hope I have made some sense, going to snip a result and post it

[DMT Dave]

So the highlighted items were delivered on 4th May 2022, this is double checked in the table tblAssign as correct, 7 x 810013 was delivered 4th May to one location on that particular number 810013 (123456 in my example posts)

25th May is returning for items delivered on 4th May!!!!

Attachment 47944

[Welshgasman]

So if you assign 20 of stock 123456, you have 20 records with 123456 value in tblAssign?
Why all these tables?
I would have had one table with a field to indicate the status of the 20 items of stock of 123456, which would be "Assigned"

I still think you need to walk through the code and inspect the fields/variables, line by line, to get to the bottom of this.
Otherwise you are just clutching at straws.

[Welshgasman]

So the highlighted items were delivered on 4th May 2022, this is double checked in the table tblAssign as correct, 7 x 810013 was delivered 4th May to one location on that particular number 810013 (123456 in my example posts)

25th May is returning for items delivered on 4th May!!!!

Attachment 47944

No point showing us the rubbish that is being produced?
We would need to see the data that is being searched, for the dates.

That is why you are asked for a DB.
Moke123 has supplied a DB here that will hide pertinent data, so no GDPR is revealed.

You need to go look for it, download it and use it. I can see you would make good use of it, so make sure you keep a copy.