Query worked now it doesn't

[AngiW]

I have a query that is supposed to update the number of years someone has
worked according to their hire date. It used to work great until one day it
decided to stop updating. Nothing has been edited so I can't figure out the
problem unless it's all of the sudden a syntax error, but I can't find it.
Hopefully someone on here can! This is driving me crazy! TIA! FYI: Hire Date
is, obviously, a date and TimeWorked is set to number (single).

UPDATE [Employee Main] INNER JOIN [Accrual Main] ON [Employee Main].[Employee
ID] = [Accrual Main].employeeID
SET [Accrual Main].TimeWorked = DateDiff("yyyy",[Employee Main]![Hire
date],Now())+Int(Format(Now(),"mmdd")<Format([Employee Main]![Hire
date],"mmdd"));

 

[John Vinson]

I have a query that is supposed to update the number of years someone has
worked according to their hire date. It used to work great until one day it
decided to stop updating. Nothing has been edited so I can't figure out the
problem unless it's all of the sudden a syntax error, but I can't find it.
Hopefully someone on here can! This is driving me crazy! TIA! FYI: Hire Date
is, obviously, a date and TimeWorked is set to number (single).

UPDATE [Employee Main] INNER JOIN [Accrual Main] ON [Employee Main].[Employee
ID] = [Accrual Main].employeeID
SET [Accrual Main].TimeWorked = DateDiff("yyyy",[Employee Main]![Hire
date],Now())+Int(Format(Now(),"mmdd")<Format([Employee Main]![Hire
date],"mmdd"));

This appears to be the very common References bug. Open any
module in design view, or open the VBA editor by typing
Ctrl-G. Select Tools... References from the menu. One of the
..DLL files required by Access will probably be marked
MISSING. Uncheck it, recheck it, close and open Access.

If none are MISSING, check any reference; close and open
Access; then uncheck it again. This will force Access to
relink the libraries.

Sometimes installing some altogether unrelated software can mess up
Access' program library references. This will make functions such as
Now(), DateDiff() and Format() stop working. The above procedure
should fix it.

 

[AngiW]

John,
Ok...I did everything you said. None were missing, so I did the second option.
It's still not working. I tried to open the copy I made of the database back
when it worked and it's not working either. Does the syntax look ok?

Thanks,
Angi

 

[John Vinson]

John,
Ok...I did everything you said. None were missing, so I did the second option.
It's still not working. I tried to open the copy I made of the database back
when it worked and it's not working either. Does the syntax look ok?

Thanks,
Angi

I'd STRONGLY suggest that you NOT RUN THIS QUERY AT ALL, and that you
remove the TiemWorked field from Accrual Main. It will contain the
number of years worked *as of the day that the query runs*; this means
that *every single record in the table* will contain incorrect data a
year from today. Simply use the expression in a Query to calculate the
TimeWorked dynamically whenever you need it!

 

[AngiW]

John,
Ok, I see what you're saying, but I need that number for display purposes too.
Won't it update each time it's run since Now() is always different and the hire
date stays constant? As I said, so far it's worked great and I've changed my
system date to two years ago and run it through for every month and it worked.
If this isn't the right formula for this, what do I need to change? And....why
is still not working?

Thanks,
Ang

 

[Angi]

Forget it...I'm an idiot!! I DID change something in the main table and forgot
about it. Sorry for wasting your time, but the DLL info is great to know for
future reference. Thanks so much!

 

[Chris]

John, you missed the obvious "Why are you storing a
calculated field?" question.

So, I'll ask it. Why are you storing a calculated field?
Why not just use a query to show that TimeWorked field,
then base everything off that field?

Select *, DateDiff("yyyy",[Hire date],Now())+Int(Format(Now
(),"mmdd")<Format([Hire date],"mmdd")) as TimeWorked from
[Employee Main]

Now, I'll admit, sometimes there is a good reason for
saving calculated results. I'm not sure this is one of
them, however.


Also, if you removed spaces from the fields/table names,
you wouldn't have to worry about the brackets.


Chris

-----Original Message-----

I have a query that is supposed to update the number of years someone has
worked according to their hire date. It used to work great until one day it
decided to stop updating. Nothing has been edited so I can't figure out the
problem unless it's all of the sudden a syntax error, but I can't find it.
Hopefully someone on here can! This is driving me crazy! TIA! FYI: Hire Date
is, obviously, a date and TimeWorked is set to number (single).

UPDATE [Employee Main] INNER JOIN [Accrual Main] ON [Employee Main].[Employee
ID] = [Accrual Main].employeeID
SET [Accrual Main].TimeWorked = DateDiff("yyyy", [Employee Main]![Hire
date],Now())+Int(Format(Now(),"mmdd")<Format([Employee Main]![Hire
date],"mmdd"));

This appears to be the very common References bug. Open any
module in design view, or open the VBA editor by typing
Ctrl-G. Select Tools... References from the menu. One of the
..DLL files required by Access will probably be marked
MISSING. Uncheck it, recheck it, close and open Access.

If none are MISSING, check any reference; close and open
Access; then uncheck it again. This will force Access to
relink the libraries.

Sometimes installing some altogether unrelated software can mess up
Access' program library references. This will make functions such as
Now(), DateDiff() and Format() stop working. The above procedure
should fix it.


.