Date Format in SQL

[SalmonDB]

I've read a few posts about date formatting in SQL, but I can't seem to get it right. I have an update query but the final date it inputs into the table is 30-Dec-1899. The format is right but the date is not.

I have a pop-up form where I enter the date (the format is fine in both forms) and then it updates the dates on all selected records in the main form. Everything works fine, except it takes a long time to update (even just 2 records) and the date is wrong. Here's the code:

Private Sub cmdUpdate_Click()
DoCmd.SetWarnings False
Dim cnn1 As ADODB.Connection
    Set cnn1 = CurrentProject.Connection
            
    Dim rs1 As New ADODB.Recordset
    rs1.ActiveConnection = cnn1
      
    Dim SQL1 As String
    Dim DX1 As Date
    DX1 = Format(Forms!frmMark!txtDatePrep, "Medium")
    
    SQL1 = "UPDATE tblDNA_Kit_Prep SET tblDNA_Kit_Prep.[Select] = False," & _
            " tblDNA_Kit_Prep.DatePrepared = " & Forms!frmMark!txtDatePrep & "," & _
            " tblDNA_Kit_Prep.PreparedBy_ID = " & [Forms]![frmMark]![cboSampler] & "," & _
            " tblDNA_Kit_Prep.Status = 'Prepared'" & _
            " WHERE tblDNA_Kit_Prep.[Select] = True"
    
    rs1.Open SQL1
    cnn1.Close
    Set rs1 = Nothing
    Set cnn1 = Nothing
    
DoCmd.SetWarnings True
DoCmd.Close acForm, "frmMark", acSaveNo
End Sub

Any help would be much appreciated!
Erica

PS. Also, I can't seem to run the code line by line, it just beeps at me when I press F8, and when I select 'Step Into'. What am I doing wrong (I keep just going back to the form and hitting the command button, driving me nuts).

 

[pbaldy]

Try changing this line to:

" tblDNA_Kit_Prep.DatePrepared = #" & Forms!frmMark!txtDatePrep & "#," & _

 

[SalmonDB]

Thanks pbaldy and Pat. I really appreciate the help. I did what pbaldy suggested and it worked, although I'm not exactly sure why. What does the number sign do? (Sorry, I'm new at this, I've been learning on my own so there are some gaps).

I checked and double-checked each of the forms (main and pop-up) as well as the underlying table. They were all formatted as medium date, so I'm not sure why the Query was not working. When I clicked on the date in a cell after the query, it would show me the time, so it appears that the query was taking the date and passing it along as time and somewhere between the pop-up window and the table, the date was being stored as time, leaving the date as 'zero' but then displaying only the date. I don't understand where this would happen since they were all set a medium date, and there are no default values.

Anyway thanks again for your help.
Erica