Getting proper date for VBA usage

[NotSoRandomOne]

I know I've done this before, but can't quickly find the solution. How do I get the proper date out of a TextBox that has been formatted for dates? When using the following, the date simply shows up as "12:00:01 AM" rather than the "1/30/2024" text in the box.

   Dim enteredDate As String
   enteredDate = Me.DateTextBox.Value
   ...
   CurrentDb.Execute "Insert Into TransactionTable ([TheDate], [Amount], [Account]) Values(" & CDate(enteredDate) & ", " & amount & ", ""1"")", dbFailOnError

 

[Gasman]

You need to surround the date value with #.
It also needs to be in mm/dd/yyyy or yyyy-mm-dd format.

 

[NotSoRandomOne]

Thanks! Got it working! It had been a while ago...

 

[MarkK]

IMO, this is the most readable, maintainable, and reliable way to do that job, for your consideration...

    Const SQL As String = _
        "INSERT INTO TransactionTable " & _
            "( TheDate, Amount, Account ) " & _
        "VALUES " & _
            "( p0, p1, p2 )"
            
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters("p0") = Me.DateTextBox
        .Parameters("p1") = Amount
        .Parameters("p2") = 1
        .Execute dbFailOnError
    End With

 

[NotSoRandomOne]

Thanks!

 

[ebs17]

I always think a proper parameter query like @MarkK shows is good.
In this case only one record is created. The use of a recordset is equivalent, also in terms of performance.
Here you don't have to worry about formatting problems much.

With CurrentDb.Openrecordset("TransactionTable")
    .AddNew
    .Fields("TheDate") = Me.DateTextBox
    .Fields("Amount") = Amount
    .Fields("Account") = 1
    .Update
End With