Update and Delete RunSQL using parameters set inside a VB Module

[relms]

I have been trying to get an update and a delete RunSQL statement to work using the parameter "DeleteDate" which is defined in my VB Module (I have no clue if I'm using the correct terms...). Anywho, here's the code I'm using.

Dim DeleteDate As Date

DeleteDate = #5/5/2002#

DoCmd.RunSQL "DELETE * FROM [MyTable] WHERE [MyTable].[Date] = DeleteDate;"
DoCmd.RunSQL "UPDATE [MyTable].[Date] SET [MyTable].[Date] = DeleteDate WHERE [MyTable].[Date] = *;"

It compiles and actually works, but it prompts me for "DeleteDate" twice instead of using the value I set (5/5/2002).

Can anyone tell me what I'm doing wrong?

Thanks!

Rob Elms

 

[RickBerem]

Hi

you should try
DoCmd.RunSQL "DELETE * FROM [MyTable] WHERE [MyTable].[Date] = #" & DeleteDate & "#;"

the same process for line 2
DoCmd.RunSQL "UPDATE [MyTable].[Date] SET [MyTable].[Date] = #" & DeleteDate & "# WHERE [MyTable].[Date] = *;"

This should do the trick!


Salvatore Grassagliata

 

[relms]

AWESOME!

Worked like a charm!

Thanks Rick!