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]

My key point to keep in mind was with respect to delimiting data types. If you don't know what that means you should have looked it up.
Numbers don't get delimited. Strings must be delimited with single or double quotes (which can be tricky when you're concatenating sql).
Dates must be delimited with octothorpes (#).
I suggest you build your sql with spaces at a line start when the first word needs a space before it. It is far easier to see those spaces all aligned on the left. Note the difference around your date variable. Also, you had a pair of double quotes (can't recall if there was a space in there) which I removed.

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

If you use that or a similar sql string elsewhere and don't fix it you'll still have the problem. If that works on 2 other forms as I think you reported, then I guess it must be correct there.

 

[PartsPig]

My key point to keep in mind was with respect to delimiting data types. If you don't know what that means you should have looked it up.
Numbers don't get delimited. Strings must be delimited with single or double quotes (which can be tricky when you're concatenating sql).
Dates must be delimited with octothorpes (#).
I suggest you build your sql with spaces at a line start when the first word needs a space before it. It is far easier to see those spaces all aligned on the left. Note the difference around your date variable. Also, you had a pair of double quotes (can't recall if there was a space in there) which I removed.

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

If you use that or a similar sql string elsewhere and don't fix it you'll still have the problem. If that works on 2 other forms as I think you reported, then I guess it must be correct there.

OMG I remember that now! I should have been able to figure that out. <stupid> The reason the other date fields didn't have this issue is because they are input forms that don't use SQL, just the form input fields.

Thanks for the lesson!!

Fun Fact: The telephone company Bell Labs is where the term octothorpes originated. It is the name they assigned to the # when they added it to the telephone. My father used to work there way back in the day.

 

[welshgasman]

That can easily be just a format problem?
Are you sure they all have the same properties?

 

[Micron]

WGM, doesn't matter, although I could check. The table that was involved is properly designed (well at least the date field is). The problem was passing 03/05/2025 and not #03/05/2025# . Also, OP said that the other 2 tables were fine, but those forms are bound to those tables rather than using code.

 

[PartsPig]

That can easily be just a format problem?
Are you sure they all have the same properties?

Yes. All three date fields are populating the table properly now. The other two didn't have an issue, only the one using SQL to populate the table which Micron showed me how to correct. His post is marked as the solution above. Thanks again!