Prev: Help with expression builder in a select Query
Next: Access 2007 Append Query � Issue with automatically inserted brack
From: Access Rookie on 18 May 2010 15:59
I have 4 columns/fields of dates in a table: Field A, Field B, Field C &
Field D. These fields can be either filled with a date or left blank. ABCD
are all "Dates".

I want to design a query or report to retrieve dates from Field A. The
criteria is:
Field A must be filled (i.e. there is a date, not a blank); and
when dates in any field of B or C or D are not blank, A must be more recent
than this/those date(s).

How do I write it? I made "A is not null" in the first criteria. And then
I think I should write something like when B or C or D are not blank, A >B or
C or D.....

I would use expression builder. But I am still new. I tried a couple of
them I was not able to make any of them work.

Anyone can give me some suggestions or advice? If I did not make myself
clear, please ask me questions. I really need this to be figured out.

BTW, I am using Access 2010. The expression builder has symbols like "&" and
"AND", what's the difference?

Thanks in advance!!!!!!!!
From: Mackster66 on 19 May 2010 10:58
There may be a more efficient way to do it, but the only method I can think
of is as follows (don't include any of the quotes):

Open your query in Design view. In the row that says "Criteria:" enter
">[Field B] And >[Field C] And >[Field D]". This will capture all records
where all have dates, but Field A is the most recent of all of them.

In the "or:" row below the "Criteria:" row, enter "Is Not Null" under Field
A and then "Is Null" under Fields B, C, and D. This will capture all records
where only Field A has a date.

Now you need to capture the rest of the possible combinations. You can
continue adding criteria in the rows below the "Criteria:" row. This is what
all of the rows should look like when you're done. Do not add the column
headers - I put them there to help visualize the concept. Hopefully, it
won't word wrap when I post it.

Field A Field B
Field C Field D
>[Field B] And >[Field C] And >[Field D]
Is Not Null Is Null
Is Null Is Null
>[Field B] Is Null Is Null
>[Field C] Is Null Is Null
>[Field D] Is Null Is Null
>[Field B] And >[Field C] Is Null
>[Field C] And >[Field D] Is Null
>[Field B] And >[Field D] Is Null

This should return every result where Field A has a date entered that is
more recent than any other dates that may be entered in any of the other
columns. I hope that helps.


"Access Rookie" wrote:

> I have 4 columns/fields of dates in a table: Field A, Field B, Field C &
> Field D. These fields can be either filled with a date or left blank. ABCD
> are all "Dates".
>
> I want to design a query or report to retrieve dates from Field A. The
> criteria is:
> Field A must be filled (i.e. there is a date, not a blank); and
> when dates in any field of B or C or D are not blank, A must be more recent
> than this/those date(s).
>
> How do I write it? I made "A is not null" in the first criteria. And then
> I think I should write something like when B or C or D are not blank, A >B or
> C or D.....
>
> I would use expression builder. But I am still new. I tried a couple of
> them I was not able to make any of them work.
>
> Anyone can give me some suggestions or advice? If I did not make myself
> clear, please ask me questions. I really need this to be figured out.
>
> BTW, I am using Access 2010. The expression builder has symbols like "&" and
> "AND", what's the difference?
>
> Thanks in advance!!!!!!!!
From: John W. Vinson on 19 May 2010 21:30
On Tue, 18 May 2010 12:59:19 -0700, Access Rookie
<AccessRookie(a)discussions.microsoft.com> wrote:

>I want to design a query or report to retrieve dates from Field A. The
>criteria is:
>Field A must be filled (i.e. there is a date, not a blank); and
>when dates in any field of B or C or D are not blank, A must be more recent
>than this/those date(s).

You can make use of the NZ() function:

[A] > NZ([B], [A]) AND [A] > NZ([C], [A]) AND [A] > NZ([C], [A])

Since date A will not be later than itself, this criterion will only be true
if there is a date in A (otherwise the whole expression would be NULL and
treated as false), and if there is a date in one of the other fields it must
be earlier than A.
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Help with expression builder in a select Query
Next: Access 2007 Append Query � Issue with automatically inserted brack