DateAdd

  • Thread starter Thread starter Paul Mars
  • Start date Start date
P

Paul Mars

I know it is there, but I can not remember it and can not find it in help.

Update Query, I need to insert a date into a date/time field. I need to
start with a specific date and add +1 to each successive record update. This
will only be done once. There are 230 records. Records are sorted on Primary
key. So:
First record date field needs to be: 1/1/90, second record 1/2/90, third
1/3/90, etc.


Thanks much!

Paul
 
There are a million variations to do this. Here's but one (using ADO)

Dim objConn As ADODB.Connectio
Dim objRS As ADODB.Recordse
Dim strSQL As Strin
Dim datUpdate As Dat

datUpdate = #01/01/1990

Set objConn = CurrentProject.Connectio
Set objRS = objConn.Execute("SELECT [PrimaryKey] FROM [YourTable]"

Do Until objRS.EO

strSQL = "UPDATE [YourTable] SET [NewDateColumn]=#" & datUpdate & "# WHERE [PrimaryKey]=" & objRS(0
datUpdate = DateAdd("d",datUpdate,1

objRS.MoveNex
Loo

NOTE: If your PK is text, you'll need to modify the WHERE clause - "WHERE [PrimaryKey]='" & objRS(0) & "'"
 
Not easy with SQL.
Very easy with DAO.
Set the limiting dates as you wish:

Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = #1/1/2004# To #12/31/2004#
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function
 
Back
Top