Date in table not the desired format

[PartsPig]

I have a 3 forms that write to the same table. Each has a date field that is populated by the user and written to the table. One of the forms does not write the date in the proper format and I can't figure out why. I have each date in the table set to Date/Time for Data Type. Each date field in the 3 forms uses the calendar to select date feature. The date fields on the forms have all the same property settings. Two of the date are stored in the table in m/d/yyyy format but the third is storing time 12:00:03 AM no matter what time of day you populate it. What am I missing??

 

[Micron]

Check the format property of the table date fields. Other possibilities are textbox format property (but you say you ruled that out) or code is doing it.

 

[PartsPig]

Check the format property of the table date fields. Other possibilities are textbox format property (but you say you ruled that out) or code is doing it.

All three of the table date fields are set to Date/Time as shown in the pic I shared in the original post. I confirmed all the textbox format properties are blank (no format selected). It dawned on me that only one form has the ClosedDate textbox in it (the problem child) and I have tried setting that to Short Date but still getting the same result in the table so I set it back to blank. All the code for the "Close RO" form is below. I tried using the variable txtClosedDate in the mySQL section but still got the same result so I reverted back to the dtClosedDate variable. I also added a pic of the form for prosperity's sake. I am stumped.

'frmCloseRO
Option Compare Database


Private Sub btnGo_Click()
    Dim strRO As String
    Dim blnClose As Boolean
    Dim blnConfirmClose As Boolean
    Dim mySQL As String
    Dim dtClosedDate As Date
    
    strRO = txtROToClose
    dtClosedDate = txtClosedDate
    
    blnClose = (MsgBox("You want to close RO = " & strRO & " on date " & dtClosedDate & "?", vbYesNo, "Closing RO " & strRO & "?") = vbYes)
    'MsgBox "blnClose = " & blnClose, vbOKOnly
    txtROToClose = Null
    txtROToClose.SetFocus
    
    If blnClose = True Then
        'check if already closed and if not close all lines for RO entered
        If DCount("IsOpen", "tblPartsTracking", "ROnum = " & strRO & " And IsOpen = true") > 0 Then '0 is closed
            MsgBox "dtClosedDate = " & dtClosedDate, vbOKOnly
            mySQL = "UPDATE tblPartsTracking " & _
                    "SET tblPartsTracking.IsOpen = False, " & _
                    "    tblPartsTracking.ClosedDate = " & dtClosedDate & " " & _
                    "WHERE ((tblPartsTracking.RONum)=" & strRO & ");"
            DoCmd.RunSQL mySQL
            MsgBox "RO " & strRO & " has been closed as of " & dtClosedDate & ".", vbOKOnly
        Else
            MsgBox "RO " & strRO & " is already closed.", vbOKOnly, "Already closed"
        End If
    Else
        MsgBox "RO " & strRO & " will NOT be closed.", vbOKOnly, "Not closing RO " & strRO
    End If

End Sub

Private Sub Form_Load()
    txtROToClose = Null
    txtROToClose.SetFocus
    txtClosedDate = Date
End Sub

 

[Micron]

That is not what I was talking about. You showed the fields and their data types, not if any formatting is applied. Look at the format of the date field in this table. Check your 3 to see if they're all the same.

It seems you know about that though, because you mentioned short date format in your next post. I'll have to look at the rest later.

 

[PartsPig]

That is not what I was talking about. You showed the fields and their data types, not if any formatting is applied. Look at the format of the date field in this table. Check your 3 to see if they're all the same.

View attachment 122969

It seems you know about that though, because you mentioned short date format in your next post. I'll have to look at the rest later.

Ah I see. All those format settings were blank as well. I did change it to Short Date and it no longer shows a time but instead it shows the date 12/30/1899. The odd part is that I have several msgboxes that show the date during the running of the code and it is always correct even after the table is updated where a msgbox states "1234 has been closed on date 2/28/2025" yet that's not what is in the table????

 

[Micron]

it shows the date 12/30/1899

That suggests to me that the form control is blank.

"1234 has been closed on date 2/28/2025" yet that's not what is in the table

That doesn't.
Is this the same db I uploaded somewhere around 02/18 and if so, does it have these forms in it?
And if this is about 3 forms, each with that code then make sure the code in the problem form is exactly the same as the ones that work.

 

[PartsPig]

That suggests to me that the form control is blank.

That doesn't.
Is this the same db I uploaded somewhere around 02/18 and if so, does it have these forms in it?
And if this is about 3 forms, each with that code then make sure the code in the problem form is exactly the same as the ones that work.

Yes it's the same DB. Although there are 3 forms updating records in the same table, they each populate different fields with little overlap. The ClosedDate which is having all the problems is only in one of the three forms, frmCloseRO.

 

[Micron]

if so, does it have these forms in it?

And the rest of that question? If I could replicate the issue then maybe I could take a look but I'd need to know what to do.
Would also be better if I didn't have to dig in to find out which 3 tables are involved.

 

[Micron]

OK, I got it figured out. Do you want to see if you can spot the problem (you might learn something useful) or should I just tell you?
If you want to try, put a break point on the runsql line. When it stops there, type in the immediate window and press enter:
?mySql
and read your sql statement. Keep in mind - which data types need to be delimited?

 

[PartsPig]

OK, I got it figured out. Do you want to see if you can spot the problem (you might learn something useful) or should I just tell you?
If you want to try, put a break point on the runsql line. When it stops there, type in the immediate window and press enter:
?mySql
and read your sql statement. Keep in mind - which data types need to be delimited?

Ok so I tried removing the spaces in the SET statement, adding AND to replace the comma, putting quotes around the date, formatting the date in the SQL statement and a few other things. All to no avail. I give up. I actually enjoy trying to figure it out but at some point it just has to work. I really thought the below was going to work but no...

            mySQL = "UPDATE tblPartsTracking " & _
                    "SET tblPartsTracking.IsOpen = False and " & _
                        "tblPartsTracking.ClosedDate = Format(" & dtClosedDate & ", ""Short Date"") " & _
                    "WHERE ((tblPartsTracking.RONum)=" & strRO & ");"

OR THIS ONE

            MsgBox "dtClosedDate = " & dtClosedDate, vbOKOnly
            mySQL = "UPDATE tblPartsTracking " & _
                    "SET tblPartsTracking.IsOpen = False and " & _
                        "tblPartsTracking.ClosedDate = Format(" & dtClosedDate & ", ""m,d,yyyy"") " & _
                    "WHERE ((tblPartsTracking.RONum)=" & strRO & ");"