Date variables in a SQL statement

[atrottier]

I have two varaible:StartD ate = Date
EndDate = DateAdd("d", 7, StartDate)
I'am using these to try to retrieve data from a table. What is the correct syntax to use variables of type "DATE" within the "WHERE" clause. I've been trying to use the "Between" option and "<= , =>" when comparing these dates to the DATE column on the table. If I put two actual dates in place of the variable names such as #1/1/2006# and #1/1/2007# it works I just can't get the variable names to work. Thanks, in advance.

[mlcampeau]

Instead of using Between, you could try like this:

WHERE [YourDateColumn] >= [StartDate] And [YourDateColumn] <= [EndDate]

[JKing]

Are you executing this sql through code? Or is it simply a query? Either way could you post the full SQL for the problem.

[atrottier]

Here is the code. It is in the "Clicked" event of a command button.
StartDate = Date
EndDate = DateAdd("d", 7, StartDate)

DeleteSQL = "DELETE [ExtractDataActu alStart] " & _
"WHERE ActualStart <= " & StartDate & " AND ActualStart >= " & EndDate & " ; "
CurrentDb.Execu te DeleteSQL, dbFailOnError

Error box tells me "Syntax Error - missing an operator. Anyone have any ideas?
I think I need to get quotes around the variable names? Been trying can seem to get it.
Thanks!

[MikeTheBike]

Here is the code. It is in the "Clicked" event of a command button.
StartDate = Date
EndDate = DateAdd("d", 7, StartDate)

DeleteSQL = "DELETE [ExtractDataActu alStart] " & _
"WHERE ActualStart <= " & StartDate & " AND ActualStart >= " & EndDate & " ; "
CurrentDb.Execu te DeleteSQL, dbFailOnError

Error box tells me "Syntax Error - missing an operator. Anyone have any ideas?
I think I need to get quotes around the variable names? Been trying can seem to get it.
Thanks!

You could try

StartDate = Date
EndDate = DateAdd("d", 7, StartDate)


DeleteSQL = "DELETE [ExtractDataActu alStart] " & _
"WHERE ActualStart BETWEEN #" & StartDate & "# AND #" & EndDate & "# ; "

CurrentDb.Execu te DeleteSQL, dbFailOnError

??


MTB

[atrottier]

Thats closer Mike, at least the "#" get stuck around the dates. Still getting a syntax error:
"Syntax error(missing operator) in query expression '[ExtractDataActu alStart] WHERE ActualStart BETWEEN #7/24/2007# AND #7/31/2007#'.
The syntax looks good any other ideas why still the error.

[JKing]

You're missing the from clause.

[code=vb]
DeleteSQL = "DELETE [ExtractDataActu alStart] " & _
"FROM yourTable " & _
"WHERE ActualStart BETWEEN #" & StartDate & "# AND #" & EndDate & "# ; "
[/code]

Replace yourTable with the name of the table you are trying to delete records from.

[MikeTheBike]

You're missing the from clause.

[code=vb]
DeleteSQL = "DELETE [ExtractDataActu alStart] " & _
"FROM yourTable " & _
"WHERE ActualStart BETWEEN #" & StartDate & "# AND #" & EndDate & "# ; "
[/code]

Replace yourTable with the name of the table you are trying to delete records from.

OOOPS!
Good job someones paying attention, don't how I missed that, obviously woods and trees syndrom. That's my excuse........

MTB

[atrottier]

Thanks guys! I'am used to writing SQL for a SyBase system which is regular SQL server and there is no need to list the table in the "FROM" statement if that is the one getting deleted from.