Update [] Set Help

[Taff]

I have a form with a command button that exports a text file. On the OnClick Event of the command button i have the following:-

DoCmd.RunSQL "UPDATE [Award Dataset] SET [Award Dataset].[Export_Date] = Format(Now(),"dd/mm/yyyy")"

I have another table called [ExportstoElwa] which has a field called [Export_Date] also.

What i would like to happen is when i click a different Command Button it deletes all the [Export_Date] dates from the Award Dataset that are equal to those of the [ExportstoElwa].[Export_Date].

Any ideas?


Thanks in Advance All.


Anthony

 

[namliam]

1) dont use spaces in names !
2) use currentdb.execute not docmd.runsql
3) Use date() instead of now(), which prevents you from having to use the format.
4) If you have to use format in a VBA sql thing like above use ' instead of "
DoCmd.RunSQL "UPDATE [Award Dataset] SET [Award Dataset].[Export_Date] = Format(Now(),'dd/mm/yyyy')"

I hope thats ideas enough....

Regards

 

[Mile-O]

DoCmd.RunSQL "UPDATE [Award Dataset] SET [Award Dataset].[Export_Date] = Format(Date(),""dd/mm/yyyy"");"

 

[Taff]

Thanks Mile-O-Phile,

i'm still trying to remove all the dates from the Award Dataset.[Export_date] where it is equal to a field on my form.

The field is called Export_Date.

Something like:-

DoCmd.RunSQL "UPDATE [Award Dataset] SET [Award Dataset].[Export_Date] = null where [Award Dataset].[Export_Date]=me.export_date;"

I know the above is wrong but do you understand what i mean?

 

[namliam]

Thanks Mile-O-Phile,

i'm still trying to remove all the dates from the Award Dataset.[Export_date] where it is equal to a field on my form.

The field is called Export_Date.

Something like:-

DoCmd.RunSQL "UPDATE [Award Dataset] SET [Award Dataset].[Export_Date] = null where [Award Dataset].[Export_Date]=me.export_date;"

I know the above is wrong but do you understand what i mean?

I Guess i didnt help you... Oh well lets try again...

You allmost have it...

DoCmd.RunSQL "UPDATE [Award Dataset] SET [Award Dataset].[Export_Date] = null where [Award Dataset].[Export_Date]=#" & format(me.export_date,"mm/dd/yyyy") & "#"

Should do the trick... My above comments are still valid tho....

(I hope i helped you this time )

Regards

 

[Mile-O]

DoCmd.RunSQL "UPDATE [Award Dataset] SET [Award Dataset].[Export_Date] = null where [Award Dataset].[Export_Date]=#" & format(me.export_date,"mm/dd/yyyy") & "#"

DoCmd.RunSQL "UPDATE [Award Dataset] SET [Award Dataset].[Export_Date] = Null WHERE [Award Dataset].[Export_Date] = #" & Format(Me.[Export_Date],""mm/dd/yyyy"") & "#;"