Generate Date

[needmoremoney]

Hello,

I have a date field inside a table. This is the only field inside this table. Does any know how to automatically generate dates into the field? For example, I need the table to list all dates from 2004-2010 in format of 01/01/2004 and etc.

The end result would be:

datefield
01/01/2005
01/02/2005
01/03/2005
...etc..
12/31/2010

Any ideas let me know. Thanks much.

 

[jadams0173]

Put this in a command buttons click event. This assumse that the table name is test. sdate is the date you want to start. you will need to make a reference to your Microsoft DAO 3.x Object Library if you already haven't.

Private Sub Command5_Click()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim sdate As Date

Set db = CurrentDb

Set rs = db.OpenRecordset("test")

With rs
    sdate = #1/1/2004#
     Do Until sdate = #12/31/2004#
        .AddNew
         !Date = sdate
         sdate = sdate + 1
         Debug.Print sdate
        .Update
    Loop
End With
End Sub

 

[lupins46]

Run this sub to enter ther records. Make sure you use your own table and field names.

Sub EnterDates()
Dim dt As Date
Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tablename", dbOpenDynaset)
For dt = #1/1/2004# To #12/31/2010#
rs.AddNew
rs!datefieldname = dt
rs.Update
Next dt
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

 

[needmoremoney]

thanks lupins46..

I'm such an idiot.. I just imported from excel a static date listing. Thanks for the post though.

 

[MichaelRed]

On the other side of reality, what use is such a thing? A table consisting on only dates (in any valid MS date range) is just a sequential set of numbers which can be dynamically generated about as quickly as they could be retrieved from a static table. Most aps would not usea all of the records (dates) and the excess storage (and retrievial and selection / filtering) just seems like overhead.




MichaelRed

 

[needmoremoney]

There is no "reality" issue here. I just needed this field for security purpose in the db that I created.

In a nutshell, if date does not exist, application closes. If table does not exist application closes. Different keys loads new dates and etc... hope that gives you an idea of where I'm headed.

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmmmmm

Date doesn't exist is easily covered by any of several different Dte/Time functions:

e.g. to see if the "DATE" (from whatever source you impose) is in the "table", you already know the start (min) and end (max) points of the range so it is just the DATE is between the two (or not). You have selected a four year interval (range), so it COULD look more like an attempt at job security (assuming you keep the back door entry in mind). As for wheather the table "exists" as a criteria, any olde table (or any other "object" serves the same overall purpose. Particularly if you cobble together some obscure and generally meaningless object which could appear to the casual user / programmer to have SOME use and a bit more confused and confusing object which deletes the sought after object (many low level / beginning 'hacksters' even have the obscure code buried in teh object itself, hoping to even erase the 'evidence' of hteir skulldrudgery. So, again I see little or no point or purpose to where you seem to be headed. What "security" I do see is, at best, far from failproof and may approach criminal on the other end.



MichaelRed

 

[needmoremoney]

Thanks for the post Michael. I hope most users see it as you do. Maybe, that would make the data more secure when they are not able to congegate the true aspect of what is in place.
To elaborate on the security issue. If the db was copied from work and taken home, he/she would not be able to used it after a period of time. It's a minor effort in securing the db.

 

[GDGarth]

"To elaborate on the security issue. If the db was copied from work and taken home, he/she would not be able to used it after a period of time. It's a minor effort in securing the db. "

Not to pick on you, but I think there are lots of better ways to accomplish the same thing.

First of all, set up security so nobody can see your code. As far as I'm concerned, the built-in Access security is better than anything you're likely to create on your own, and it's usually adequate for most things. You might as well create an MDE file, so your code isn't even visible if someone defeats the security.

If you want to do something like you're talking about, I'd create a file on installation, hide it in another directory (maybe the Windows directory), and have your application check for it on startup. If the file's not there (or not the right size, date, or some other attribute), pop up a message saying something like "Unregistered copy, please see the database administrator", and close the app.

 

[needmoremoney]

Hello GDGarth,

Thanks for the post. You opened my mind there. I have the db as a mde and had disabled all tool bars and incorporated a login.

I think the installation of an extra file is a good idea. Any ideas on how to get it going?

And by the way.. you're not picking on me.. I'm open to all suggestion. The more the better. It makes me think from a different point of view.