VBA Module- How to format date in a field?

[Faction21]

First off, Im using Access 2000, and created a new module.

Ok, Here is the info:

-A table called Indexing contains a field Index9
-Index9 needs to be updated to the current date, in a particular format.
-the format is as follows: YYYY-MM-DD

I would Like to create a module to do this, but im not sure how i would make this run or work, meaning do i make a function? how do i get it to run? using a macro? etc etc...

But first off, how do i update Index9 with the date in the particular format?

Here is the code i could come up with:

Sub Format()

Dim strSQL As String

Dim currentdate As Date
Date = Now 'Format(Now, "yyyy-mm-dd") how do i format this?


strSQL = "UPDATE Indexing, SET [Index9] = Date, WHERE [Index9] Is Null;"

DoCmd.RunSQL strSQL

End Sub

-Your Time and efforts are much appreciated

 

[Rich]

Just use an UpdateQuery
newdate:Format(Date(),"yyyy-mm-dd")

date already returns todays date, now includes the time as well

 

[Faction21]

Oh ok, do you mean like in Design view for creating a new query?

 

[Rich]

yes, just put Is Null in the criteria section of the filed you want to update

 

[Faction21]

WOW, this really helps, silly me trying to re-invent a square wheel... U gys are awsome, thanks a whole bunch.