MM/DD/YYYY appears as time when using update query

[Rachel Garrett]

I have some VBA/SQL that updates a value in a table when a user
changes a value in the form. Right now, the user's changes are
reflected in the right place. However, the data they're typing in is a
date, and what shows up in the table is 12:00:10 AM, 12:00:13 AM, or
similar. This happens whether I just str(Me.UserField.Value) or use it
in conjunction with Format:

Private Sub UserField_AfterUpdate()

Dim mySQL As String
Dim myString As String

'User's input is already in date format, but need to make it a string;
myString = Format(Me.UserField.Value, MMDDYYYY)

mySQL = "UPDATE [TableName]"
mySQL = mySQL + " SET [TableName].[Target Date] = "
mySQL = mySQL + myString
mySQL = mySQL + " WHERE ([TableName].[Superkey] = '"
mySQL = mySQL + Me.KeyPart2.Value + " KeyPart3)"

'Hide VB's automatic warning messages about running SQL on a button
click

DoCmd.SetWarnings False
DoCmd.RunSQL mySQL
DoCmd.SetWarnings True

End Sub

Any suggestions?

 

[Rachel Garrett]

I figured it out -- I needed hash marks in the mySQL string to go
around the date. Amended version works: