Solved Use a date value in string (1 Viewer)

[foshizzle]

I'm trying to run the VBA below to delete table records based on a certain date.
The date is selected by the user in a form control then set to TempVars using TempVars!tmpEndDate = Me.txtEndDate.Value

Dim strSQL As String
strSQL = "Delete * From [tblBalance] WHERE [TransactionDate] =  " & CDate(TempVars!tmpStartDate)
DoCmd.RunSQL strSQL

The value of strSQL is Delete * From [tblBalance] WHERE [TransactionDate] = 4/4/2023

I believe this is failing to delete because the date is included as part of the string.
How can I get around this?

 

[arnelgp]

strSQL = "Delete * From [tblBalance] WHERE [TransactionDate] = #" & CDate(TempVars!tmpStartDate) & "#"

 

[foshizzle]

ok the hash tags make sense. Thanks! This worked!

 

[Gasman]

Be aware that format must be mm/dd/yyyy
With that date you have there, it is ambiguous.

 

[Pat Hartman]

I almost never use embedded SQL. You need to be much more precise with your delimiters so that local variables are interpreted correctly. As in this case, you specifically need to format the date to be unambiguous - yyyy/mm/dd or use the default US format of mm/dd/yyyy. But that is because the SQL is a STRING.

If you had used a querydef, this would have been correct:

Delete * From [tblBalance] WHERE [TransactionDate] = CDate(TempVars!tmpStartDate)

When a date field is defined as a date, it is NOT a string. It is a double precision number where the integer portion is the number of days since Dec 30, 1899 and the decimal is the fraction of the day since midnight. So 0.0 = midnight on Dec 30, 1899 and 0.5 = noon on that date. Negative numbers are dates earlier than Dec 30. So -2.25 =6AM on Dec 28, 1899. Storing a date this way allows Access to easily do arithmetic with it. SQL Server, Word, Excel, and most RDBMS' use a similar technique although the origin date (zero date) may be different.