Automatic Date Entry

[Al77]

Can anyone tell me how to design a Query that will add a specific date to a Table every month?



In my case, I would like to add / append new record entries on the first day of each month - for example.

Do I have to start with a table that has ALL the future dates required, or is there a sweeter solution?

Thanks, in advance.

Al

[RayMilhon]

You could do this. Create an Autoexec Macro that calls a Function in a module.

Public function add_a_date() As boolean
Dim dtval as date
Dim rs As new adodb.recordset
Dim strsql as string, adocmd as new ADODB.Command
strsql = "Select Max(Datefield) as stdate from your_table "
rs.open strsql, currentproject.connection,
If Month(rs!stdate) = Month(Now()) and Year(rs!stdate) = Year(Now()) Then
add_a_date = False
Else
strsql = "Insert Into yourtable Set datefield = Date()"
adocmd.activeconnection = currentproject.connection
adocmd.commandtext = strsql
adocmd.execute
add_a_date = True 'To show it completed

End if
End function

This is only the base you would also have to add some error handling and other fields that are required for your table.

[Al77]

VERY impressed Ray - I just have to spend sometime trying to understand your coding now!

Al

[RayMilhon]

The initial recordset get's the maximum date stored in that field. You then compare the month and year of that field to the current month and year. If they are the same the routine has already run and doen't need to execute again. If they are different then it inserts a new record into the table

Note: As written it inserts the date the procedure is executed so if the procedure is run on the second of the month and there is no record for the current month it would insert the second. You could modify that line from
Set datefield = date() to Set datefield = CVDate(Month(now()) & "/01/" & Year(now()))
That will put the first of the current month regardless of what the run date is.