Advanced Query Question

[Tom Fries]

I am using windows xp pro and office xp pro
I have a table called hiring in my database that consists
of the following fields: Name ( a text field), Hire Date (
a date time field), Union ( a text field indicating
whether this is a union employee or not...it could be
changed to a logical field), and New Hire ( a logical
field)


I am trying to make a query that will show only newly
hired persons that are not union employees and first of
all plot 6 months from the hire date to determine when
their probationary period ends { I can do that) and
secondly I want to show only those people whose
probationary period is over next month. For reporting
purposes in May I want to generate a list of people who
are due to have a probationary period expire any time in
June.


In my query I have the following fields from the hiring
table: Name, Hire Date, New Hire ( under criteria I have
True) and Union ( under criteria I have "no" ) .

I then make a calculated field called Probation Date by
using the following line in the first blank column

Probation DateDateAdd("m",6,[Hire Date]))

That works fine

Then I create a field called 6 month probation using the
following in the next blank column:

6 Month Probation:Month([Probation Date])

in the criteria of the 6 Month Probation field
I enter the following

Month(DateAdd("m",1,Date()))

that should read the system clock extract the month from
it add one to that month and make it the criteria for that
column. Does seem correct to you? I am just wanting to
have a query find me only those new non union employees
that have probationary periods expiring next month.

When I run the query, it tells me to enter the Parameter
Value "Probation Date". I created the parameter Probation
Date in the previous column. Can any one you help?
Thanks so much

 

[Graham R Seach]

Tom,

[Probation Date] isn't a *real* column, so Jet doesn't know anything about
it. Use this instead:
6 Month Probation: Month(DateAdd("m", 6, [Hire Date]))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[John Vinson]

When I run the query, it tells me to enter the Parameter
Value "Probation Date". I created the parameter Probation
Date in the previous column.

Unfortunately you can't usually reuse a calculated field in a further
calculation.

I'd suggest instead using a criterion on HireDate of

= DateSerial(Year(Date()), Month(Date()) - 6, 1) AND <DateSerial(Year(Date()), Month(Date()) - 5, 1)

and no calculated fields at all.