Add a single date to all rows

[JoeFootball91]

I want create a query that adds a field called DATE to my existing data. I want every record to have a date of 1/1/2011 populated in it. I need it to have date formatting so that I can add/subtract other dates from it.

DATE
1/1/2011
1/1/2011
1/1/2011

Can someone please show how to do this?

Thanks!



Joe

[redbull]

Assuming your table already has the column for the date.... here ya go

Public Function RecordLoops()
Dim r As DAO.Recordset
 
Set r = CurrentDb.OpenRecordset("Select * from yourtablename") ' Query or Table records to loop th
  'Check to see if the recordset actually contains rows
     If Not (r.EOF And r.BOF) Then
       r.MoveFirst
     r.Edit
   Do Until r.EOF = True
'Start code for each record to do here
    r.Edit
      r![ColumnName] = "whatever you want "
        r![ColumnName] = Date ' This will put in today's date... incase you needed it.
      r.Update
    r.MoveNext
  Loop
Else
    ' if there are no records in the table, you can place something here if you'd like
End If


r.Close
Set r = Nothing
End Function

This will loop thru each record and allow you to add in whatever you'd like on the table. I have to give June7 a big shout out here, he helped me a lot of with this.

I have this as a function just so I can call it from a macro that runs daily.

[JoeFootball91]

Thanks redbull. Your code is way more advanced than what I'm used to, so I'm not really following it.

Below is the code I was trying. It populates every record with 1/1/2011, however, it's text format instead of a date format.

Select
TABLE01.*,
'1/1/2011' as DTE
From TABLE01;

Do you know I could alter it so the DTE column is formatted as a date?

[redbull]

Go to the table properties and change the column format to date

[JoeFootball91]

Yep, that works. Thanks!

Just out of curiosity, is there a way to change the format to date in SQL? I was trying the code below, but it keeps resulting in text format.

Select
TABLE01.*,
Format(#1/1/2011#,"Short Date") as DTE
From TABLE01;

[redbull]

I am not sure... might want to spin off into a new thread. Glad I could help.

From looking at it... it might have something to do with DD/MM/YYYY but im sketchy about it lol