Filtering a query

[Guest]

Hi all

Hope someone can help and please excuse ignorance as very new to access

I have a query made from a table which has 4 columns.

NAME start date review1 completed review2
completed
a n other 15 jun 06 20 sep 06 19 sep 06 20 nov 06
and so on 20 aug 06 20 nov 06 13 jan 07
me 18 sep 06 12 dec 06 05 feb 07

What i am hoping for is something (query,report ?) to list when a date in
review1 column or review2 column is within 30 days of today and the completed
date has not been filled

So would look like (all in red)

AND SO ON REVIEW1 20 NOV 06
ME REVIEW1 12 DEC 06

 

[Allen Browne]

Three steps:

1. In query design view, enter this into the Criteria under the Review1
field:
Between (Date() - 30) And (Date() + 30)

2. Beneath the Criteria row in query design is another row labelled "Or".
Enter the same criteria on this row under the Review2 field.

3. Under the Completed field, enter:
Is Null
on both rows (i.e. on the Criteria row and the first Or row.)

 

[Guest]

Hi

thanks for taking the time to help me

I did what you said and it is not what i had in mind i didnt explain it
correct so sorry

It should have said
i have a table with NAME, START DATE, FDA1, OTMR1, FDA2, INTERIM as headings

underneath the headings dates are inserted when completed by means of a form

Now, i have a query that pulls the names from the table and adds 6 months
to the start date and puts the date in fda1 column, 12 months for otmr1
column (this is the due by date) in the query (not the table)

what i require is a report that only pulls the name, type and date when its
due within 30 days of today for e.g.

me fda1 20 nov 06
another otmr1 19 nov 06

and not the whole record

Hope this explained it better

thanks again for your help

 

[Allen Browne]

You can create the query, with the criteria under the fda1 and otmr1 fields.

You can then create a report based on the query. In the report, you will
need to use IIf() expressions in the ControlSource of your text boxes, e.g.:
=IIf(Abs(DateDiff("d",[fdma1], Date())) < 30, "fda1", "otmr1")

 

[Guest]

Thanks again for helping a complete fool

i missed something out again doh!

I will start again and maybe you could suggest a better way than the way iam
doing it

i have a form that users enter a name, pay number and start date and this
goes into a table called "details". (Just a bit of background here employers
have a two year competency cycle and must have following assessments "fda1"
within 6 months of start date, otmr1 12 months of start date, fda2 within 12
months of start date but after first 6 months basically 6 - 12 months after
start, interim within 11th and 12th month from start date, and the same for
second year only fda3, otmr2, fda4 and summary headings.

The table has the above headings but the field for each employee is blank
where no assessment has been done. so for e.g

name start date fda1 otmr1 etc
fred 10/04/06 12/06/06 etc

Now i have made a query "details" that has all the names and has calculated
the dates each assessment needs to be done by.

e.g
name start date fda1 otmr1 etc
fred 10/04/06 10/10/06 10/04/07 etc

What i require is a report when table (details) has a blank entry and the
date in the query "details" is within 30 days of today the report will only
show name, assessment type and date needed to be done and not the whole
record.

hope this helps and thanks again

You can create the query, with the criteria under the fda1 and otmr1 fields.

You can then create a report based on the query. In the report, you will
need to use IIf() expressions in the ControlSource of your text boxes, e.g.:
=IIf(Abs(DateDiff("d",[fdma1], Date())) < 30, "fda1", "otmr1")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi

thanks for taking the time to help me

I did what you said and it is not what i had in mind i didnt explain it
correct so sorry

It should have said
i have a table with NAME, START DATE, FDA1, OTMR1, FDA2, INTERIM as
headings

underneath the headings dates are inserted when completed by means of a
form

Now, i have a query that pulls the names from the table and adds 6 months
to the start date and puts the date in fda1 column, 12 months for otmr1
column (this is the due by date) in the query (not the table)

what i require is a report that only pulls the name, type and date when
its
due within 30 days of today for e.g.

me fda1 20 nov 06
another otmr1 19 nov 06

and not the whole record

Hope this explained it better

thanks again for your help

 

[Allen Browne]

I'm not really clear what's happening here.

If fda1 is blank you want to use 6 months after starting date as the value?
If so, you could substitute:
CVDate(Nz([fda1], DateAdd("m", 6, [start date])))
in place of fda1 in the expression already given.

At some point you will need a related table of "reviews", with a ReviewType
field (fda1, or otmr1, or whatever it is), and a ReviewDate. For each type,
you can then calculate the last time this review was held for a particular
employee, and therefore when it will be due again. I think you will find it
much more flexible to use related records rather than repeating fields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks again for helping a complete fool

i missed something out again doh!

I will start again and maybe you could suggest a better way than the way
iam
doing it

i have a form that users enter a name, pay number and start date and this
goes into a table called "details". (Just a bit of background here
employers
have a two year competency cycle and must have following assessments
"fda1"
within 6 months of start date, otmr1 12 months of start date, fda2 within
12
months of start date but after first 6 months basically 6 - 12 months
after
start, interim within 11th and 12th month from start date, and the same
for
second year only fda3, otmr2, fda4 and summary headings.

The table has the above headings but the field for each employee is blank
where no assessment has been done. so for e.g

name start date fda1 otmr1 etc
fred 10/04/06 12/06/06 etc

Now i have made a query "details" that has all the names and has
calculated
the dates each assessment needs to be done by.

e.g
name start date fda1 otmr1 etc
fred 10/04/06 10/10/06 10/04/07 etc

What i require is a report when table (details) has a blank entry and the
date in the query "details" is within 30 days of today the report will
only
show name, assessment type and date needed to be done and not the whole
record.

hope this helps and thanks again

You can create the query, with the criteria under the fda1 and otmr1
fields.

You can then create a report based on the query. In the report, you will
need to use IIf() expressions in the ControlSource of your text boxes,
e.g.:
=IIf(Abs(DateDiff("d",[fdma1], Date())) < 30, "fda1", "otmr1")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi

thanks for taking the time to help me

I did what you said and it is not what i had in mind i didnt explain
it
correct so sorry

It should have said
i have a table with NAME, START DATE, FDA1, OTMR1, FDA2, INTERIM as
headings

underneath the headings dates are inserted when completed by means of a
form

Now, i have a query that pulls the names from the table and adds 6
months
to the start date and puts the date in fda1 column, 12 months for otmr1
column (this is the due by date) in the query (not the table)

what i require is a report that only pulls the name, type and date when
its
due within 30 days of today for e.g.

me fda1 20 nov 06
another otmr1 19 nov 06

and not the whole record

Hope this explained it better

thanks again for your help