storing week no after automatically inserted..

[ramrod]

Hi

Access 2003

1. I have a query, in which the current week number, is auotmatically
inserted. (weekno: format(now(), "ww")

2. I would now like to create another field which will take this week
number and store it ! ie so it will not change when the week no
changes in (item 1) above.

Any help... ideas...please

Thank you

Ramrod

 

[Jeff Boyce]

Actually, if you already have a date/time value, you really (REALLY) don't
want to store the "weekno" as well. Since you can (and do) calculate it in
a query, just use that.

Now, if you are saying that you don't have the date/time value already in
your table, add a field to store that. And since working directly in the
tables is not a very good use of Access' features and functions, make sure
that a form referring to that table (or a query thereof) is how data is
entered/edited. You wouldn't even need to have a control on that form that
shows today's date ... you could use a hidden control and update it with
Now() before you save the data entered via the form.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[ramrod]

Actually, if you already have a date/time value, you really (REALLY) don't
want to store the "weekno" as well. Since you can (and do) calculate it in
a query, just use that.

Now, if you are saying that you don't have the date/time value already in
your table, add a field to store that. And since working directly in the
tables is not a very good use ofAccess' features and functions, make sure
that a form referring to that table (or a query thereof) is how data is
entered/edited. You wouldn't even need to have a control on that form that
shows today's date ... you could use a hidden control and update it with
Now() before you save the data entered via the form.

--
Regards

Jeff Boycewww.InformationFutures.net

Microsoft Office/AccessMVPhttp://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentorhttp://microsoftitacademy.com/










- Show quoted text -

Hi Jeff .. thank you for your help....

Maybe I should explain myself a little more:

I have a weekly Rota Monday to Sunday of people doing jobs ( paid by
the hour). hardly ever changes except for sick/holidays. The
individual employee may work 2 hrs one, day 4hrs the next....all
varey, but the same pattern for each employee each week.

I have a Form Showing the Individual employee details(ie name address
contact tel) as 'employee'(from a table), and a sub form in this
showing what they are doing for that week(subform 'Schedule')(from a
table/query).

What I am looking to do is transfer, at a point, (either end of day,
or the end of a week) all of their work details form subform
'schedule', to an Orders table(for tracking payments etc). However I
also need to have it dated in the orders table of the date they
worked. Leaving the 'schedule form unchanged , so I can use it the
following week .. make minor adjustments when needed.

....not having much experience .. am I on the right lines ...ie need a
working date .. that needs to be fixed when it hits a table(orders) .
ie taking the current date automatically place on the form and fixing
it when the data is transfered !

Any help would be great ... or pointer to an example ..

Thank you

Ramrod

 

[Jeff Boyce]

I may not be following yet, but it sounds like you could use the date the
record is originally entered (or, if needed, transfered to your Orders
table) and do so automatically.

Or, if you are basically saying that the "Order" info would need to
duplicate the original entry data, why bother?! Simply store the ID of the
original data entry record in your Order table, and use that relationship
via a query/join to retrieve the original data.

Still not clear...

Regards

Jeff Boyce
Microsoft Office/Access MVP