473,321 Members | 1,877 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

A simple query problem

Rex
Hi,

I have a table in which for a particular record there are different
dates in each column. I want to find out the latest date of all.. so
for example

ID | date1 | date2 | date3 | date4 |
-----------------------------------------------------------------------
1 | 1/12/06 | 5/12/06 | 10/12/06 | 4/12/06 |

I want to create a query that would show:

ID | date1 | date2 | date3 | date4 | latestDate
---------------------------------------------------------------------------------------
1 | 1/12/06 | 5/12/06 | 10/12/06 | 4/12/06 | 10/12/06

any help would be greatly appreciated

Rex.

Dec 10 '06 #1
2 1420
On 10 Dec 2006 16:09:53 -0800, "Rex" <ra*******@gmail.comwrote:

One elegant solution is to write a public function in a standard
module:
public function GetMaxDate(byval d1 as Date, d2 as Date, d3 as date,
d4 as date) as Date
dim d as date
d=d1
if d2>d then d=d2
if d3>d then d=d3
if d4>d then d=d4
GetMaxDate=d
end function
and then call it from your query:
select *, GetMaxDate(date1, date23, date3, date4)
from SomeTable

If you must, you can probably do it with nested iif function calls as
well. See help file for iif (that was not a typo).

Another elegant one is to figure out if your database design is
normalized. Typically one could write:
select Max(SomeDate) from SomeTable where ForeignKey = xxx

-Tom.
>Hi,

I have a table in which for a particular record there are different
dates in each column. I want to find out the latest date of all.. so
for example

ID | date1 | date2 | date3 | date4 |
-----------------------------------------------------------------------
1 | 1/12/06 | 5/12/06 | 10/12/06 | 4/12/06 |

I want to create a query that would show:

ID | date1 | date2 | date3 | date4 | latestDate
---------------------------------------------------------------------------------------
1 | 1/12/06 | 5/12/06 | 10/12/06 | 4/12/06 | 10/12/06

any help would be greatly appreciated

Rex.
Dec 11 '06 #2
Create a function in VB, either using IF statements or a SELECT CASE
statement.

The function would simply be something like this if you used IFs:

Public Function findDate(date1 Aas Date, date2 As Date, date3 As Date,
date4 As Date)
If date1>date2 and date1>date3 and date1>date4 Then
findDate = date1
ElseIf date2>date1 and date2>date3 and date2>date4 Then
findDate = date2
ElseIf
'...put in date 3 here
Else
findDate = date4
End If
End Function

-Mal

Rex wrote:
Hi,

I have a table in which for a particular record there are different
dates in each column. I want to find out the latest date of all.. so
for example

ID | date1 | date2 | date3 | date4 |
-----------------------------------------------------------------------
1 | 1/12/06 | 5/12/06 | 10/12/06 | 4/12/06 |

I want to create a query that would show:

ID | date1 | date2 | date3 | date4 | latestDate
---------------------------------------------------------------------------------------
1 | 1/12/06 | 5/12/06 | 10/12/06 | 4/12/06 | 10/12/06

any help would be greatly appreciated

Rex.
Dec 11 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Patchwork | last post by:
Hi Everyone, Please take a look at the following (simple and fun) program: //////////////////////////////////////////////////////////////////////////// ///////////// // Monster Munch, example...
0
by: unixman | last post by:
As usual, it is 2:00am, and I'm pulling my hair out, finally resorting to posting in the newsgroups for help. :) Simple problem, in theory. Given table "map": CREATE TABLE map ( entry_id...
20
by: max | last post by:
Hi! I'm having a problem with a very simple drop-down list since the page comes out with no elements in the drop-down but giving no errors. This is the code: <form name="year_search_form"...
2
by: Mrs Howl | last post by:
I have a query that just reads one table and appends to an output table, one-for-one. No criteria. It's not a Total query (i.e. no group by). It normally run run in minutes, but gets horribly...
1
by: Tero Partanen | last post by:
Hi, I have a small problem with MS ACCESS query. I want to be able to have a field, where I can input the search parameter, and then it will show me all the entires that match the parameter....
15
by: Richard Hollenbeck | last post by:
For example, one college course has only 24 students in it, but the following code says there are zero As, 20 Bs, 16 Cs, 4 Ds, and 8 Fs. When it prints it then says 0 As, 40 Bs, 32 Cs, 8 Ds, and...
3
by: John Baker | last post by:
Hi:7 Newby here to ASP, and using the ASP.NET Web Matrix development tool. While that tool looks great for a Newby, I have run into a snag. I have an HTML Text Box which I have named HireInput,...
2
by: Deano | last post by:
Problem is that there are lots and lots of fields in an employee record. I specify a form control as the criterion for one of those fields which is the value of the primary key for that record. ...
27
by: one man army | last post by:
Hi All- I am new to PHP. I found FAQTS and the php manual. I am trying this sequence, but getting 'no zip string found:'... PHP Version 4.4.0 $doc = new DomDocument; $res =...
9
by: Emin | last post by:
Dear Experts, I have a fairly simple query in which adding a where clause slows things down by at least a factor of 100. The following is the slow version of the query ...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.