query exactly a year from now

  • Thread starter Thread starter AngiW
  • Start date Start date
A

AngiW

I'm trying to update a table based on two dates...hire date and now. If now is
between 12 and 13 mos to the day, then update.

I've used datediff() with year, didn't work. I used datediff() with
month...didn't work. I need it to the actual hire date (ie. 1/30/94). Keeps
coming up as 10, when it shouldn't be 10 until the 30th. I tried now - hire
date and then divide by 365, but that won't work either, because of leap years.
Any suggestions???

Thanks!
 
I'm trying to update a table based on two dates...hire date and now. If now is
between 12 and 13 mos to the day, then update.

I've used datediff() with year, didn't work. I used datediff() with
month...didn't work. I need it to the actual hire date (ie. 1/30/94). Keeps
coming up as 10, when it shouldn't be 10 until the 30th. I tried now - hire
date and then divide by 365, but that won't work either, because of leap years.
Any suggestions???

Thanks!
Click to expand...

Now is not a good function for this.
Now includes a time value as well as a date value.
Do you really want to exclude a record because the hire date one year
ago was at an earlier time of day then whatever the current time is?

Try:
Where DateAdd("yyyy",1,[HireDate]) >=Date and
DateAdd("yyyy",1,[HireDate]<= DateAdd("m",1,Date)
 
You first paragraph described the difference in months.

You second paragraph seemed to describe you want to work out the different
in years.

I am not sure what you actually require but it seems that you want to select
Records where the *anniversary* of the [HireDate] falls between today and
same day next month. If this is the case, try:

....
WHERE DateSerial( Year(Date()), Month([HireDate]), Day([HireDate]) )
BETWEEN Date()
AND DateAdd( "m", 1, Date() )
 
Ok, thanks to both of you, but it's still not doing what i need. It's giving
me a value of 0 or -1, which isn't right. Let me explain a little better and
hopefully, someone can tell me what i need to do.

A person gets vacation hours on their hire date. Or after their hire date, if
it's before the next months equivalent date (ie. hire date = 1/21...update as
long as it's before 2/21). Now, depending on how many years they've been with
the co., they get 2, 3 or 4 weeks vacation. I've created a field called Time.
I'm using that as my factor field. If Time = 1, 2, 3 or 4, then give them 2
weeks....and so on. My problem is the exact year thing if it's not run exactly
on their hire date and the next month issue. Between mos. 12 & 13, 24 &
25...blah blah blah as long as the dates match. it's working for month, but
not to the day. Like it will give me a year for any date in January whether
the date has already occured or not. I have a feeling this isn't real
complicated, i'm just making it that way. Thanks for all your help!!!
 
Two tables:
Employee Main:
EmployeeID (primary key)
Status (full time or part time)
Hire Date

Accural Main:
EmployeeID (primary key)
Time
VacAvail

The ultimate goal is to update VacAvail to 80, 120 or 160 for full-time
employees on their anniversay date (Hire date) depending on how long they've
been with the company. The reason it has to be BETWEEN mos 12 and 13, 24 and
25 and so on, is because the month after their anniversary date they start
earning for the next year and it's not avail until the next anniversary date.
I was going to use the Time field as the factor to decide whether it was 80,
120 or 160 to be updated.

This is the current sql string I'm using that isn't giving me the values I
need:

UPDATE [Employee Main] INNER JOIN [Accrual Main] ON [Employee Main].[Employee
ID] = [Accrual Main].employeeID SET [Accrual Main].[Time] =
DateSerial(Year(Date()),Month([Employee Main]![Hire date]),Day([Employee
Main]![Hire date])) Between Date() And DateAdd("m",1,Date())
WHERE ((([Employee Main]![Status])='Full time'));

Would it be better for me to make two fields...Month and Day and then compare
them? I'm at loss and grasping for straws here.
 
PMFJI, but I don't see the rest of the thread.

What are you expecting to be stored in the Time field? (BTW, that's a
terrible name for a field: it's a reserved word, and shouldn't be used for
your own purposes)

As it's written, the query is going to try and put True into it if their
anniversary date is between today and one month from today, or False
otherwise. If Time is a Date field, that means you'll get 29 Dec, 1899 for
True, and 30 Dec, 1899 for False.
 
First of all, what is PMFJI? Second, I changed the name to TimeWorked. It is
a number field. As i said in my first post, I want a number value of the time
they have been with the company. 1, 2, 3, 4...so on. I have it set to two
decimals, but it only gives me whole numbers. The sql string i posted is the
complete string. It's the one that Van gave me. As I said, I was using
DateDiff, but it still wasn't working. As far as the true/false, that makes
sense because it's only giving me 0's and -1's. So ...now what?
 
PMFJI: Pardon Me or Jumping In.

I gave you the DateDiff as a *selection criteria*, not as an expression for
you to use in the assignment part of the Query. Note the keyword WHERE in
the String I posted.

If my understanding of what you described so far is correct:

1. Whenever you run the Query, you only want to update the [Accrual Main]
Records whose hire date *anniversary* fall between today and a month from
today. In addition, you only want to update Records for the full-time
employees.

2. On those EmployeeIDs selected according to point 1 above, you want to
give each Employee either 80, 120 or 160 hours rec leave depending on
whether the Employee has 1 year, 2 years or 3+ years of service (with
respect to the *anniversary date*, NOT today's date) with the company.

Your SQL String seems to get mixed up between the *selection criteria* and
*calculation of the [VacAvail]*. You need to separate them out logically
and construct the SQL String accordingly.

For the [years of service], you will need to use code similar to the one
posted in The Access Web article:

http://www.mvps.org/access/datetime/date0001.htm
 
Van,
First, i would like to apologize if i seemed abrupt in that last posting. I'm
extremely frustrated!! Second, that link you sent me works great!!! It solved
the exactly a year problem. Now, there's still the other problem...making sure
it doesn't update the VacAvail field more than a month after the anniversary
date. I thought about doing something along the lines of WHERE date() - [hire
date] <30...but 30 won't work because not all months are 30 days...so am I back
to the dateserial??? Thanks everyone for all your help!!! I REALLY appreciate
it!
 
making sure
it doesn't update the VacAvail field more than a month after the anniversary
date. I thought about doing something along the lines of WHERE date() - [hire
date] <30...but 30 won't work because not all months are 30 days...so am I back
to the dateserial???
Click to expand...

yep:

DateSerial(Year(Date()), Month([Hiredate]) + 1, Day([Hiredate]))

will return the day one month after the hiredate. Note that if the
hiredate is January 31, there is no February 31 - you'll get March 2.
 
I would recommend looking at the algorithm you used first. There seems to
be a few problems in your algorithm if my understanding is correct. In
fact, these problems make it harder for you to construct the Update Query
correctly. For examples:

1. Let's say if you run the update today 25/Jan/2004. With the Update Query
you are constructing, you are forced to run the Update Query again exactly
on the 26/Feb/2004! If you run it too early,then some employees may get
the credit *twice*. If you run it too late, some employees will miss out
the annual vacation credit for the year. Clearly, you cannot rely on the
Query being run on the exact day month after month since the required dates
may be weekend or the person who is supposed to run it may be away on the
dates.

Clearly, you cannot afford the hit & miss like this. You need to devise an
algorithm that make sure the employees get vacation credit exactly *once* a
year with some flexibility when the Update Query will be run. You may even
need to modify the Table Structure to achieve this.

2. Your algorithm doesn't seen to cater for the case where the employee
still has some credit from the previous year and the current year's credit
is due.

3. You will also need to be very clear about the selection criteria to
cater for point 1 about.

4. Note that the age calculation in the link I posted uses *today* as the
reference point. In your case, you need whole years of service with the
*coming anniversary of hire date* as the reference point and these 2 may be
different.

Suggest you think carefully how the algorithm should work and do trial runs
with different anniversary dates on paper and see if your algorithm works
correct from month to month with early / late processing and different
anniversary dates. Try with border-line cases to ensure *everything* is
covered. If fact, if you enumerate the border-line cases, it will help you
to think clearly.

Only after you think, devise & test your algorithm thoroughly like this, you
should then try to construct the Update Query and codes if required. You
may even need more than one Query.

You probably will need to use DateSerial and other DateTime functions but
this is implementation to be done *after* you devising the correct algorithm
for your requirements / set-up.

--
HTH
Van T. Dinh
MVP (Access)



AngiW said:
Van,
First, i would like to apologize if i seemed abrupt in that last posting. I'm
extremely frustrated!! Second, that link you sent me works great!!! It solved
the exactly a year problem. Now, there's still the other problem...making sure
it doesn't update the VacAvail field more than a month after the anniversary
date. I thought about doing something along the lines of WHERE date() - [hire
date] <30...but 30 won't work because not all months are 30 days...so am I back
to the dateserial??? Thanks everyone for all your help!!! I REALLY appreciate
it!
Click to expand...
 
BTW, TimeWorked is a *Calculated Value* and it should NOT be stored in the
Table. Every value you store in this Field will be wrong sooner or later.
From the value in [Hire Date], you can *always* work out the TimeWorked (to
any reference point including "today"). Thus you should not store
calculated values in Tables. In addition, it is likely that re-calculation
is actually more efficient than calculating, storing & retrieving not to
mention that re-calculation returns up-to-date / correct values while stored
calculated values may be wrong.

Storing calculated values violates the Relational Database Design Principles
but I guessed you haven't look at Relational Database Design Theory.
Unfortunate, most Access courses don't seem to cover RDDT while it really
should be the *first* lessons. Suggest you check out RDDP & the Database
Normalization technique.

In fact, it seems that you don't need the Update Query whose SQL String you
posted, after all.
 
Van and everyone,
It's done and works GREAT!!! Thank you so very very much for all your help! I
did have to create a yes/no field to make sure it didn't update more than once,
but that's not in here yet. Van, I'm not sure what you mean by not using the
year # as a field since I need a reference point, but it works...at least for
now! :) Here's the string I ended up with:

UPDATE Positions INNER JOIN ([Employee Main] INNER JOIN [Accrual Main] ON
[Employee Main].[Employee ID] = [Accrual Main].employeeID) ON
Positions.Position = [Employee Main].Position SET [Accrual Main].VacAvail = 120
WHERE ((([Accrual Main]![TimeWorked])=1 Or ([Accrual Main]![TimeWorked])=2 Or
([Accrual Main]![TimeWorked])=3 Or ([Accrual Main]![TimeWorked])=4) AND
((Date()) Between DateSerial(Year(Date()),Month([Employee Main]![Hire
date]),Day([Employee Main]![Hire date])) And
DateSerial(Year(Date()),Month([Employee Main]![Hire date])+1,Day([Employee
Main]![Hire date]))) AND (([Employee Main].Position)="LPN" Or ([Employee
Main].Position)="RN"));

THANKS AGAIN!!!

Sincerely,
Angi
 
I was referring to the Field [TimeWorked] which is a calculated / derived
value and should NOT be stored in the Table. If the explanation in my
previous post didn't make sense, you should perhaps find a book on
Relational Database Design Theory (RDDT) for more detailed explanation (I
can't type long post because I am a 2-finger typist.) Some Access books
also has brief coverage of RDDT and Database Normalization (a formal set of
rules to ensure the Table Structure is suitable/efficient for later
processing during the running of database).
 
Back
Top