A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Help with expression builder in a Query to retrieve wanted dates



 
 
Thread Tools Display Modes
  #1  
Old May 18th, 2010, 08:59 PM posted to microsoft.public.access.queries
Access Rookie[_3_]
external usenet poster
 
Posts: 6
Default Help with expression builder in a Query to retrieve wanted dates

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!!!!!!!!
  #2  
Old May 19th, 2010, 03:58 PM posted to microsoft.public.access.queries
Mackster66
external usenet poster
 
Posts: 63
Default Help with expression builder in a Query to retrieve wanted dates

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!!!!!!!!

  #3  
Old May 20th, 2010, 02:30 AM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Help with expression builder in a Query to retrieve wanted dates

On Tue, 18 May 2010 12:59:19 -0700, Access Rookie
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]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:28 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.