Populating fields

[Noreene Patrick]

Please, Please help!!!!Populating fields

I am trying to figure out how to populate fields in between 2 dates.

I have a startdate and enddate that I fill in through a form. But, I want a query? or VBA? to populate the dates in between with the same info in that record.

HOw do I do this, anyone???

Thanks in advance.

 

[The_Doc_Man]

Let me ask a question and then give you the answer if your answer is favorable.

You have a table. You have a date field in this table. You have some other field.

Your form specifies two dates. You want to put in new records for as many days as there are between (including) the two dates. You want the same information for each record other than stepping through the date range.

Is that correct?

If so, I would use VBA. I would try to work from an unbound form because otherwise, the binding to the record will get in the way.

I would have a bunch of controls for the two dates, the data to be inserted, and a command button.

Under command button, I might put some VBA code as an onclick event routine.

First, I would compute the number of days between the starting and ending dates including both dates. DateDiff would do this for you. (Look it up in Help) I would copy the starting date to a date-typed variable. I would copy the days difference to another variable.

Next I would open the table as a table-type recordset. (You can update that type pretty easily.)

Then I would write a for loop based on the computed number of days. Inside the loop I would

Do an .AddNew to the recordset
Store data for the new record including the date you set aside as the starting date.
Do an .Update to the recordset
Add one (using DateAdd, units of days) to the stored date.

continue or exit the loop based on the counter you determined earlier.

when you exit the loop on the last day of the time range, you are done.

NOTE: When you store the data, remember that you MUST specify values for any field in the table for which the field definition properties include "Required=Yes" OR "AllowZeroLength=No" - either one would block you if you left it out. Also, any field for which you have "Indexed=Yes (No Duplicates)" requires a unique value. The ONLY fields you can leave out would be an Autonumber or any field that allows zero length, is not required, and doesn't require a unique value because of its indexing properties.

 

[Noreene Patrick]

First of all, thank you Doc Man for answering my post. I am very limited in Access, but am very eager to learn. So, your answer is great, I just dont' know what to do with it.

So, let me ask this another way. I am thinking maybe a pivot table would work for me. I have a table that holds empname, shift, startdate, enddate. I am trying to track employee time off. So, when I put in startdate, I want it to put empname in every day between startdate and enddate. Now, I could eliminate enddate and use numberofdays (same difference, instead of putting an enddate, I would put the number of days forward to fill in).

I have another table that has dates(every day until 2005) and dayofweek (Sun, Mon,etc).

I dont know how to set up a query? to make new table holding info with days filled in automatically. Then, I suppose I could use that table to make pivot table. OR am I way off course.

Could you please help me straighten this out? Or, show me a new way of thinking?

All help will be appreciated.

Thanks, Noreene

 

[The_Doc_Man]

Your problem is that you need to decide how you wish to approach the determination of off time.

The first thing is to remember this about Access. It is a business modeling tool. So in order to build a good model, you need to know the business rules.

If you were to do this problem by hand, how would you approach it? What rules would you apply?

Until you have a good answer to this question, you are going to confuse yourself. Consult reality before trying to make the model do something. Maybe you have to modify what you store for this purpose. Maybe you have to think of it at levels of detail that you normally take for granted.

One possible approach is the "calendar counter" method. You look at the calendar and count the working days between two dates in question.

In this method, you have that table you mentioned that lists all dates from date X to date Y. In that table, you need yes/no fields to indicate whether persons are expected to work on that date. Like, check NO on holidays and weekends if that is your business model.

Then if you have the first and last days for which the employee missed, the time off might be just the count of the number of working days in your calendar between (including) the first and last off days for that employee. Where 'working days' are defined by having the [ExpectedToWork] field set to Yes.

Your query might be tricky to write, but it is basically just a Count function on the Yes/No values between two dates.

See what I mean about moving from the business practise to the Access model?

 

[Noreene Patrick]

Yes, I see what you are saying. I will take this info and read over it and THINK!!

You have been very helpful. Thanks again, Doc Man.