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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|