URGENT HELP required for a query

  • Thread starter Thread starter Paulette
  • Start date Start date
P

Paulette

I just started a new job and received a request for an
urgent report which is required from our Access Database
and my Access course isn't until next month..ack! I just
can't seem to get the information I'm trying to query for.

Here is what I'm trying to do:

We do searches in 4 stages and a form is submitted for
each of those stages (A,B,C,and D) which each have a date
field in our database. When form D is complete, the
search is over. My mission is to generate separate
reports for the months of July and August indicating which
searches were current i.e. not at the form D stage.

So in my July report, if form B was in July, that record
would be reported indicating both the dates for the A and
B. Here's the tricky part. If form C, in that same
record, was dated in August, I want to surpress the August
date so that my form C field is blank but the record and
the dates for A and B are still in my report.

For example:

Unit: CS
Type: PT
A: May 31st, 2003
B: July 10th, 2003
C: August 12th, 2003 (I would want to surpress the August
date while leaving the rest of the record intact)

By indicating Form D is Null I can indicate which records
are current, but I need to back track so that each of
these reports look current as of the date given i.e.
active searches up to and including July 31st, 2003.

I've tried less than and greater than queries but I think
the problem I'm having is that I want to surpress certain
fields within a record rather than the whole record. Does
anyone know what kind of criteria formula I would use to
do this?
 
Is your table structure like that below?
Unit:
Type:
A
B
C
D

Or is it some more normalized structure. From what you posted, I am guessing
that it is roughly as above

If so, you can't suppress the contents of the field without using an Immediate
If statement. Your query MIGHT look something like:

Parameters [StartDate] DateTime, [EndDate] DateTime;
SELECT Unit, Type,
IIF(A>=[StartDate] And A<=[EndDate],A,Null),
IIF(B>=[StartDate] And B<=[EndDate],B,Null),
IIF(C>=[StartDate] And C<=[EndDate],C,Null),
IIF(D>=[StartDate] And D<=[EndDate],D,Null)
FROM TableName
WHERE A Between [StartDate] and [EndDate] OR
B Between [StartDate] and [EndDate] OR
C Between [StartDate] and [EndDate] OR
D Between [StartDate] and [EndDate]
 
Back
Top