Date query 2

[mespa]

Hello everyone
Getting back into working with Access and I'm rusty

I need to create a query where I pull employees 90 days from current date and change their status to inactive.

Date is defined as mm/dd/yyyy
Thanks

 

[Golom]

Try
[blue][tt]
UPDATE tbl SET Status = 'InActive'
WHERE tbl.DateField <= DateAdd ( "d", -90, Date() )
[/tt][/blue]

 

[mespa]

Did I goof ....
On the date criteria Status is ok but
Why does it come back as a parameter value ??
this is what I have in my expression
[Tbl_MeEval].[Hire Date]<=DateAdd("d",-90,Date())

 

[Golom]

The expression that you posted looks OK ... assuming that your table and field names are correct. When you say "... come back as a parameter value ...", what do you mean exactly? Is it asking you to supply a parameter? If so, which one?

Can you post the full SQL statement?

 

[mespa]

Paramater value?
is on the
HireDate

My goal is to just report those with the date 90days from current sysdate and update the status and give the report to supervisors

today is 03/25/04 need all since 12/25/03. Don't want to hardcode the date this way when I create the form anyone can run the report for the future. Thanks again for your help

 

[mespa]

Here is the complete SQL statement.. I started over, saw my error on why I got the parameter value??

SELECT [Tbl_MeEval].[ID#], [Tbl_MeEval].[Last], [Tbl_MeEval].[First], [Tbl_MeEval].[Location], [Tbl_MeEval].[Assignment], [Tbl_MeEval].[Hire Date], [Tbl_MeEval].[Last Eval], [Tbl_MeEval].[Eval Due], [Tbl_MeEval].[Status]
FROM Tbl_MeEval
WHERE ((([Tbl_MeEval].[Hire Date])<=DateAdd("d",-90,Date())));

Now my output is giving me More than 90 days out. If today's date is 3/25/04 I only want to report on those here for 90 days.. Thanks again

 

[Golom]

Hard-code the date? You aren't.

I assume that [Tbl_MeEval].[Hire Date] is the field from your table that contains the [Hire Date]. If it is asking you to supply that field then check your field name. Is it [Hire Date] or [HireDate]? If you don't spell it exactly as it is defined in the table then Access will assume that it's a parameter that the user is supposed to supply.

The function Date() returns the current date whenever it is run. It is not a "hard coded" March 25 2004.

 

[mespa]

Thank you Thank You.


Ok, my brain is coming back to life here..

I said only reporting employees here for 90 days...

No ... I need to see all those from 90 days through Current Sysdate, we will flag these as on Probation.

director just came back and made it clear on what he wants to see

 

[Golom]

OK. Change your query to
[blue][tt]
SELECT [ID#], [Last], [First], [Location], [Assignment],
[Hire Date], [Last Eval], [Eval Due], [Status]

FROM Tbl_MeEval

WHERE [Hire Date] >= DateAdd("d",-90,Date())
[/tt][/blue]

 

[mespa]

again Thank you... All set...

I promise not to be a bother all evening..

On this DatePart I want to see all records with date of 1998 . I am not getting the results I know I should have aprox 90 records.. getting error message with expression error

here is what I have
DatePart("yyyy",[Last Eval)=DatePart("yyyy",1998())

thanks

 

[Golom]

Try
[blue][tt]
Year([Last Eval]) = 1998
[/tt][/blue]

 

[BobJacksonNCI]

In case Golom is buwy, this should work:

Year([Last Eval])= "1998"

Or, adjusting your code:

DatePart("yyyy",[Last Eval])="1998"



HTH,
Bob

 

[mespa]

Thanks to Bob and Golom for your help on yesterday.

Ok, What is the magic trick for this part or am I way off
.
90 records
All Dates from the Last Eval= 1998 I attempted to
Update field Datepart("yyyy",[Eval Due] = "2001" and
all the dates changed to 12/30/1899

 

[BobJacksonNCI]

I'm here...
But I'm on vacation (wife still sleeping) and don't have Access available - I prefer testing my suggestions before posting. Hopefully, Golom will be checking in.

It would help if you posted the SQL for your update query. Seeing the SQL as opposed to the design view might make the problem obvious. I'm suspecting you can't update to an expression, but must just specify the field. If you want to change only the year, your replacement criteria would need to specify something like:
datevalue("2001") & datepart("mm",[Eval Due]) & datepart("dd",[Eval Due])



HTH,
Bob

 

[Golom]

BobJacksonNCI
On vacation? Envious it is that I am.

mespa
I infer that you are attempting to change the year in a date. There are some functions that can appear as the target of an assignment (Left, Mid for example) but it is not possible with the date related ones. If you want to change the year of a date and leave the month and day alone then you will need to be a bit fanicer.
[blue][tt]
[Eval Due] = DateSerial ( 2001, Month([Eval Due], Day[Eval Due])
[/tt][/blue]
This works mostly except for the Feb 29 thing.

If the date field also has a time associated with it then you need a further elaboration.

 

[mespa]

Hi Goldom
Would this go right into the Update to ??
the date in Table is defined as mmddyyyy

When i entered this , it came back with "has a function containing the wrong number of arguments"

Thanks

 

[Golom]

That's because it's too early in the morning, I was up too late last night, and I can't type.
[blue][tt]
[Eval Due] = DateSerial ( 2001, Month([Eval Due]), Day([Eval Due]) )
[/tt][/blue]
If you are attempting to update fields in a table then your SQL should look something like
[blue][tt]
UPDATE tbl SET [Eval Due] =
DateSerial ( 2001, Month([Eval Due]), Day([Eval Due]) )
WHERE Year[Eval Due] = 1999
[/tt][/blue]

 

[mespa]

I'm not getting something

Only thing I had to change was the 1999 to 1998
here is the code
UPDATE tbl SET [Eval Due] =
DateSerial ( 2001, Month([Eval Due]), Day([Eval Due]) )
WHERE Year[Eval Due] = 1998
Comes back with expression you entered has invalid syntax
you may have entered an operand without an operator

I appreciate all your help, I really thought this would be a rather easy task. I have other years to update, but was taking it 1 year at a time to understand the concept.

Thanks again.

 

[Golom]

OK ... one more time with the parentheses
[blue][tt]
UPDATE tbl SET [Eval Due] =

DateSerial ( 2001, Month([Eval Due]), Day([Eval Due]) )

WHERE Year([Eval Due]) = 1999
[/tt][/blue]

 

[JonFer]

You are missing parentheses for the Year() function in the Where clause:

UPDATE tbl SET [Eval Due] =
DateSerial ( 2001, Month([Eval Due]), Day([Eval Due]) )
WHERE Year([Eval Due]) = 1998

If you have multiple years to do and they are all moving the same number of years, you can do this:

UPDATE tbl SET [Eval Due] =
DateSerial ( Year([Eval Due]) + 3, Month([Eval Due]), Day([Eval Due]) )
WHERE Year([Eval Due]) In (1998, 1999, 2000)

- OR -

UPDATE tbl SET [Eval Due] = DateAdd("y", 3, [Eval Due])
WHERE Year([Eval Due]) In (1998, 1999, 2000)