delete records by date entered in form

[nikky]

I have the following code to delete records from a database. The problem seems to be with the date field; if I use a different parameter, for example, delete all records where Lastname = "smith", the code works fine. But I cannot get this to match records with the date entered in the form field "CreatedON"

Dim ThisDate As Date
ThisDate = Me!CreatedON


Dim db As DAO.Database
Dim SQL As String
SQL = "Delete * from [RegularHours] Where [RegularHours].[DayScheduleSet] = " & "#" & ThisDate & "#"
' SQL = "Delete * from RegularHours " & _
'"Where RegularHours.RegID = 47642"
Set db = CurrentDb()
db.Execute SQL
MsgBox ("All Done")

Any ideas ?

 

[Golom]

Try this
[blue][tt]
SQL = "Delete * from [RegularHours] " & _
"Where [CreatedON] = #" & ThisDate & "#"
[/tt][/blue]

 

[RoyVidar]

Hi!

Could it be a date formatting issue? If you're using other date format than US, you'll need to format the date into the string, for instance:

[tt]... = #" & format$(ThisDate,"yyyy-mm-dd") & "#"[/tt]

- here using ansi format.

Roy-Vidar

 

[nikky]

Thanks for the suggestions, but they didn't seem to do the trick. Here's my latest try

SQL = "Delete * from [RegularHours] " & _
"Where [DayScheduleSet] = #" & Format([ThisDate], "Short Date") & "#

 

[RoyVidar]

Hi again!

Please, when trying to assist the description "didn't seem to do the trick" makes it rather hard on the crystal ball, we need more info, error messages if any (helpfull just to know there werent any), what happens...;-)

You are sure there is is at least one date in the table matching the criterion?

If this is a date formatting issue, you'll need to explicitly format this to a US recognizable format. "mm/dd/yyyy" or "yyyy-mm-dd" is the two ways I know of that work. "Short Date" formats according to regional settings (which on my version is "dd.mm.yyyy", which again provides a type mismatch).

If this isn't a regional settings issue, then perhaps either the form control or the field in question contains also a time part in addition to the date (you know of course that dates are stored as numbers, the integer part is the date, the fractions represents time). If this is the question, one workaround could be for instance formatting both the field and control in the query.

[tt]SQL = "Delete * from [RegularHours] " & _
"Where format([DayScheduleSet],'yyyy-mm-dd') = '" & _
Format([ThisDate], "yyyy-mm-dd") & "'"[/tt]

Don't know if this is "correct" politically or what, but it works on my setup where both field and control contains time.

Roy-Vidar