Auto Fill date fields - one for each day of the year...

[adkapechuk]

This may be the simpilest of questions, but we have an exsisting
database form that holds information on a daily basis for travel
purposes dating back to 1999. We want to continue to have a form for
each and every day of the calendar year but as it stands we have to go
in and input each date manually. We run Windows XP

For example now that it is 2006 our receptionist is going in and adding
1/1/2006, 1/2/2006, 1/3/2006 (one by one) - there must be an easier way
Please help!

Thanks!!

 

[Amy Blankenship]

Base the form on a query, and use a LEFT JOIN to "fake" records that don't
exist yet.

Here's how I did something similar...
http://tinyurl.com/apu3v

HTH;

Amy

 

[Guest]

One way is to build a table name Count with number - integer. Fill with one
through 100 or 365 based on if you want to run a query once or three times.

INSERT INTO YourTable.YourDateField
SELECT Date()+[Count] AS Expr1
FROM [Count]
WHERE (((Date()+[Count])>=DateAdd("y",1,Date())));

 

[Guest]

Not an elegant way to do it, but effective:

Go to Excel, blank worksheet. Put 1/1/2006 in first cell. Move down one
cell. Put 1/2/2006 in second cell. Now move back up to first cell.
Highlight cells 1 through 365. Click on Edit / Fill / Series. Click on the
[OK] button. You have now filled the column with all of the dates for 2006.
Insert one row at the top of your worksheet, and put in the name of the field
from your Access database, that you use for putting dates into. Save the
Excel file as something you can remember. Go into your Access database, and
import the Excel file to be appended to your table where you need the dates
put in.

 

[Rick B]

Why is she doing this? Your data structure sounds flawed.

You should be adding exactly one record for each travel "event". If a day
has none, then you would add none. If a day has ten, you would add ten
records.

Not sure I understand your data structure and why you'd want to ad one
"form" for each day. I assume that even though you say one "form" that you
actually mean one record?

 

[Douglas J. Steele]

Realistically, you shouldn't have a row for a day unless there's data
associated with that date. In other words, you should only have to add days
as you have data.

 

[Guest]

I have to sometimes print out stuff for the bean counters that shows blanks
where nothing was done. In that case, it is easier to just populate all the
dates, then go back and fill in information as it comes in. Saves typing in
the long run. It is amazing how much money and time gets used up just to
show we're not wasting time and money . . .

 

[Douglas J. Steele]

Simplest way, then, would be to have a loop in VBA:

Dim dtmCurr As Date
Dim strSQL As String

For dtmCurr = #1/1/2006# To #12/31/2006#
strSQL = "INSERT INTO MyTable(MyDateField) " & _
"VALUES (" & Format(dtmCurr, "\#mm\/dd\/yyyy\#") & ")"
CurrentDb.Execute strSQL, dbFailOnError
Next dtmCurr

Replace MyTable and MyDateField with the appropriate names.

 

[adkapechuk]

I guess I should have been more specific - the database contains tour
information for a musician - a day off is as important as a day
traveling or a show day - every day of the year needs to be represented
and it needs to be added in advance so we can put future information in
as we get it.

Thanks for all of your suggestions - this was my first time on the
message board and I really appreciate all the suggestions and hints!

 

[adkapechuk]

I guess I should have been more specific - the database contains tour
information for a musician - a day off is as important as a day
traveling or a show day - every day of the year needs to be represented
and it needs to be added in advance so we can put future information in
as we get it.

Thanks for all of your suggestions - this was my first time on the
message board and I really appreciate all the suggestions and hints!

 

[John Vinson]

I guess I should have been more specific - the database contains tour
information for a musician - a day off is as important as a day
traveling or a show day - every day of the year needs to be represented
and it needs to be added in advance so we can put future information in
as we get it.

At the end of the year, what use will be made of a record containing a
date and ABSOLUTELY NOTHING ELSE?

Creating empty "placeholder" records like this is rarely or never
necessary. If nothing happened on April 8, there was still an April 8
- it's not necessary to store an empty record in a table to prove it!


John W. Vinson[MVP]