Date Won't Add To Table!!!

[DMT Dave]

Hi Guy's, good day to you all, for some reason that i can't figure out why a date won't add to a date field!!!

Hope you can see something i can't see

XL Sheet Cell Value Is: Tue 08/04/2025

(Set The Recordset)
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblEdit WHERE 1 = 0") 'you don't use these values so load an empty recordset on the table



(Now remove Tue from XL cell)
strDate = CDate(Right(xlSHT.Cells(i, 11).Value, Len(xlSHT.Cells(i, 11).Value) - InStr(xlSHT.Cells(i, 11).Value, " "))) ' Ready Date

(Get Friday Prior Date)
dtShipDate = DateAdd("d", -1 - Weekday(strDate), strDate) ' Add To Fridays Date Prior To strDate (Ready Date)

(Set Main Form Date)
Forms!frmMainMenu!frmIndex1!cboShipmentDateIndex1 = dtShipDate ' Adding dtShipDate Fine

Debug.Print "Date On XL Sheet: " & strDate; vbCrLf & _
"Ship Date: " & dtShipDate

Immediate Window
Date On XL Sheet: 08/04/2025
Ship Date: 04/04/2025

Now Add To ShipmentDate Field

With rs
.AddNew
!ShipmentDate = dtShipDate

dtShipDate Not Adding To ShipmentDate Field ???

spent a while stepping through this, it should be simple

Sorry not added code tags, but didn't because of short snips

If i need to add more, can add more and would put into code tags

thank you guys

[Welshgasman]

You really should not have the day in the cell, just the date. You can format the cell so you want to see the day in there as well.
If you do that then you will have a date, which you should know by now is just a number.

I used to use that 1=0 option to get an empty recordset, but there is an option on the OpenRecordset to do that, but can I find it now? The hell I can.

[CJ_London]

If you are showing all your code then you are missing some
https://learn.microsoft.com/en-us/of...-dao-recordset

[DMT Dave]

Hi CJ, WGM, sorry i only added the part upto failure, i am adding many extra field data then at the end, its important for me to say

.update
.close
set rs = Nothing

I don't believe i need

rs!Fields("ShipmentDate") = dtShipDate

instead of !ShipmentDate = dtShipDate

because it's within a With Block ?

[DMT Dave]

I could cheat and add an insert sql at the end where ShipmentDate is null but that doesn't tell me why initially it's not populating the date field

[Welshgasman]

Upload a db with enough to see the issue.
Do you have an End With statement?

[DMT Dave]

WGM, CJ, please forgive me with all of your mite,

this won't help, what an idiot

Add it then wipe it why not!!!!!!!!!!

With rs       
 For iQty = 1 To x
        .AddNew
            !ShipmentDate = dtShipDate
            !Year = Format(dtShipDate, "yyyy")
            !Quarter = Format(dtShipDate, "q")
            !Month = Format(dtShipDate, "m")
            !Week = Format(dtShipDate, "ww")
            !Customer = sCust
            !CustAdd1 = sCustAdd1
            !CustAdd2 = sCustAdd2
            !CustTown = sCustTown
            !CustPostCode = sCustPC
            !ShipmentDate = Null

[Welshgasman]

Show ALL of your code from now on please

[DMT Dave]

certainly WGM, just didn't want to spend a while removing sensitive data when i just knew 100% it was a contradiction in the code, but i know you guy's can only see/fix whats in front of you

[Welshgasman]

Use this for anything sensitive.
Randomize Data

[DMT Dave]

Will check that out WGM, i didn't mean that i didn't want to spend a while removing sensitive data, it was more that i didn't have time on my hands while at work to do so.

We as people who ask for advice have to understand you guy's can only scan / fix / repair / advise / what you can see on screen

you all put great effort in to help people, but you need help to be able to help....

I think its hard for you guy's as you have to understand:

What the main goal is
different prefixes being used in variables
sequence of events

well done you all for help you give everyone

will check that randomiser out (prob at home when more time)

Kindest and fab day