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
|
|||
|
|||
compariing and listing certain dates
I am using Access 2003 and I have a database that lists vision date, hearing
date and consent date on individuals. I would like to develop a query that will compare those three dates and provide the lastest date and list it as "Date Ready" column. For example, I have John Smith, his hearing date 10/20/06, his vision date 11/20/06 and consent date 12/1/06. I need the last date (in this case, 12/1/06) to show up as "Date Ready". I know that I can use Date Ready[query]), but I don't know how to compare the dates and select the "latest" date. |
#2
|
|||
|
|||
compariing and listing certain dates
And what do you want to do if 0, 1 or 2 of the dates is filled in? Assuming
that all three dates must be filled in, the logic would be DateReady: IIF(A is Not Null and B is Not Null and C is Not Null,IIF(AB, IIF(BC,C,B),IIF(AC,C,A)),Null) Replace A, B, and C with the names of your fields. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Lee" wrote in message ... I am using Access 2003 and I have a database that lists vision date, hearing date and consent date on individuals. I would like to develop a query that will compare those three dates and provide the lastest date and list it as "Date Ready" column. For example, I have John Smith, his hearing date 10/20/06, his vision date 11/20/06 and consent date 12/1/06. I need the last date (in this case, 12/1/06) to show up as "Date Ready". I know that I can use Date Ready[query]), but I don't know how to compare the dates and select the "latest" date. |
#3
|
|||
|
|||
compariing and listing certain dates
Lee,
When I want to compare the values in multiple fields, I use a function I wrote that will accept any number of values and return the maximum from among these values. It works with dates, strings, or numbers. My function ignores Null values, so if one of the fields is NULL, it just ignores is. But you could modify it so that if any of the elements were NULL, it would return a value or a NULL value, like below. You might want to change the name of the function to fnDateReady for your purposes. Public Function fnMaximum(ParamArray MyArray()) As Variant Dim intLoop As Integer fnMaximum = Null For intLoop = LBound(MyArray) To UBound(MyArray) If IsNull(MyArray(intLoop)) Then 'do nothing fnMaximum = NULL Exit Function ElseIf IsNull(fnMaximum) Then fnMaximum = MyArray(intLoop) ElseIf MyArray(intLoop) fnMaximum Then fnMaximum = MyArray(intLoop) End If Next intLoop End Function HTH Dale -- Email address is not valid. Please reply to newsgroup only. "Lee" wrote: I am using Access 2003 and I have a database that lists vision date, hearing date and consent date on individuals. I would like to develop a query that will compare those three dates and provide the lastest date and list it as "Date Ready" column. For example, I have John Smith, his hearing date 10/20/06, his vision date 11/20/06 and consent date 12/1/06. I need the last date (in this case, 12/1/06) to show up as "Date Ready". I know that I can use Date Ready[query]), but I don't know how to compare the dates and select the "latest" date. |
#4
|
|||
|
|||
compariing and listing certain dates
thank you that helped. I also tried to enter another date and I had to use
the original query and build another query using that query. Is it possible to use 4 dates without having to build a query on a query? For example: I have A (MRT Date), B(Vision Date), C (HEaring Date), D (Consent Date) dates and I used the IIf(AB,IIf(BC,C,B), IIF(AC,C,A)),Null) and then labeled that query "Ready", when I tried to add 'D' date, I had to use IIF ([ready][Consent Date],[Consent Date],[Ready]) AS [Date Ready]. I was thinking IIf([MRT date] Is Not Null And [vision date] Is Not Null And [hearing date] Is Not Null And [consent date] Is Not Null,IIf([MRT date][vision date],IIf([vision date][hearing date],IIf([hearing date][consent date],[consent date],[hearing date]),IIf([mrt date][consent date],[consent date],[mrt date])),Null)) AS [date ready] - but it didn't work. The query built on a query worked. "John Spencer" wrote: And what do you want to do if 0, 1 or 2 of the dates is filled in? Assuming that all three dates must be filled in, the logic would be DateReady: IIF(A is Not Null and B is Not Null and C is Not Null,IIF(AB, IIF(BC,C,B),IIF(AC,C,A)),Null) Replace A, B, and C with the names of your fields. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Lee" wrote in message ... I am using Access 2003 and I have a database that lists vision date, hearing date and consent date on individuals. I would like to develop a query that will compare those three dates and provide the lastest date and list it as "Date Ready" column. For example, I have John Smith, his hearing date 10/20/06, his vision date 11/20/06 and consent date 12/1/06. I need the last date (in this case, 12/1/06) to show up as "Date Ready". I know that I can use Date Ready[query]), but I don't know how to compare the dates and select the "latest" date. |
#5
|
|||
|
|||
compariing and listing certain dates
What you need to do is chage the structure of your tables. It seems
that you should have an additional table with a field that stores the value of the primary key in your current table, a field to store the event date, and another field to store the event type. With that structure, getting the latest date is fairly simple even if you have 50 events associated with your main table. As posted elsewhere, if you are going to keep the current structure, then you will probably be better off writing a VBA function that does what you need. Using IIF can get fairly complex. You might use the switch function instead, but every time you add another date, you are going to have to modify this in the query. SWITCH(A Is Null Or B is Null Or C is Null or D is Null, Null, A=B and A=C and A=D, A, B=C and B=D, B, C=D, C, True, D) '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === Lee wrote: thank you that helped. I also tried to enter another date and I had to use the original query and build another query using that query. Is it possible to use 4 dates without having to build a query on a query? For example: I have A (MRT Date), B(Vision Date), C (HEaring Date), D (Consent Date) dates and I used the IIf(AB,IIf(BC,C,B), IIF(AC,C,A)),Null) and then labeled that query "Ready", when I tried to add 'D' date, I had to use IIF ([ready][Consent Date],[Consent Date],[Ready]) AS [Date Ready]. I was thinking IIf([MRT date] Is Not Null And [vision date] Is Not Null And [hearing date] Is Not Null And [consent date] Is Not Null,IIf([MRT date][vision date],IIf([vision date][hearing date],IIf([hearing date][consent date],[consent date],[hearing date]),IIf([mrt date][consent date],[consent date],[mrt date])),Null)) AS [date ready] - but it didn't work. The query built on a query worked. "John Spencer" wrote: And what do you want to do if 0, 1 or 2 of the dates is filled in? Assuming that all three dates must be filled in, the logic would be DateReady: IIF(A is Not Null and B is Not Null and C is Not Null,IIF(AB, IIF(BC,C,B),IIF(AC,C,A)),Null) Replace A, B, and C with the names of your fields. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Lee" wrote in message ... I am using Access 2003 and I have a database that lists vision date, hearing date and consent date on individuals. I would like to develop a query that will compare those three dates and provide the lastest date and list it as "Date Ready" column. For example, I have John Smith, his hearing date 10/20/06, his vision date 11/20/06 and consent date 12/1/06. I need the last date (in this case, 12/1/06) to show up as "Date Ready". I know that I can use Date Ready[query]), but I don't know how to compare the dates and select the "latest" date. |
Thread Tools | |
Display Modes | |
|
|