Earliest Date

  • Thread starter Thread starter Randal
  • Start date Start date
R

Randal

I have 6 fields in a table that contain dates. I need to pull the earliest
date from all 6 fields into one field in a query. Is there a function in
access to do this? thanks,
 
Hi Randal,

Here's a way without using VBA (code):

- Say the fields in your table "Table3" are
Field1 Field2 Field3 Field4 Field5 Field6

- Create following UNION Query

SELECT DMin("[field1]","[Table3]") AS Expr1, "FIELD1"
FROM Table3
GROUP BY DMin("[field1]","[Table3]")

UNION

SELECT DMin("[field2]","[Table3]") AS Expr1, "FIELD2"
FROM Table3
GROUP BY DMin("[field2]","[Table3]")

UNION

SELECT DMin("[field3]","[Table3]") AS Expr1, "FIELD3"
FROM Table3
GROUP BY DMin("[field3]","[Table3]")

UNION

SELECT DMin("[field4]","[Table3]") AS Expr1, "FIELD4"
FROM Table3
GROUP BY DMin("[field4]","[Table3]")

UNION

SELECT DMin("[field5]","[Table3]") AS Expr1, "FIELD5"
FROM Table3
GROUP BY DMin("[field5]","[Table3]")

UNION

SELECT DMin("[field6]","[Table3]") AS Expr1, "FIELD6"
FROM Table3
GROUP BY DMin("[field6]","[Table3]");

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."




--------------------
| From: "Randal" <[email protected]>
| Subject: Earliest Date
| Date: Mon, 2 Feb 2004 10:31:28 -0500
| Lines: 5
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.access.queries
| NNTP-Posting-Host: 12.28.226.67
| Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.
phx.gbl
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.queries:188408
| X-Tomcat-NG: microsoft.public.access.queries
|
| I have 6 fields in a table that contain dates. I need to pull the earliest
| date from all 6 fields into one field in a query. Is there a function in
| access to do this? thanks,
|
|
|
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

For .mdb/e files - perhaps something like this:

SELECT ID,
Least(Date1, Date2, Date3, Date4, Date5, Date6) As SmallestDate
FROM TableName


The VBA function Least() that gets the Minimum date:

Public Function Least(ParamArray var()As Variant) As Variant

Dim min As Variant
Dim i As Integer

' Initialize
min = LBound(var)
For i = LBound(var) to UBound(var)
If var(i) < min Then min = var(i)
Next i

Least = min

End Function

This function can be more refined so it doesn't run when there is an
empty array and you can add error handlers.

You could also put Nz() around the Date fields in the SELECT clause to
avoid getting NULLs in the Date fields. ***** This raises the
question of what to do when there is a null date? You might want to
put the default date at a very hi value - then it won't be selected
when run thru the Least() function. Or, you may want to know when the
date value is NULL - then allow Nz() to change the value to ZERO,
which would be the lowest value.

In either case use the Nz() function:

Nz(NULL,0) = 0
Nz(NULL, #1/1/9999#) = 1/1/9999

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQB6wAIechKqOuFEgEQJ/HgCfVzv/mD+41xLXksyQEHIVCrx15PgAnjzI
GUkv1XbRnKYnd1sCt6DzMZ/1
=tR/l
-----END PGP SIGNATURE-----
 
I must have something wrong because I always get zero. I copied the VBA
function into a module just as you have it below. Here is the query I used
to test. There are no null values.

SELECT Least([RptDate]![RptBegin],[RptDate]![RptEnd]) AS SmallestDate1
FROM RptDate;

Once it works, I think I still have a problem that Nz won't help, because
the VFP table I am using does not have null values. It uses some value
which Access sees as 12:00:00 AM (or Saturday, December 30, 1899 in long
date format) in query results. I am not sure how the Nz or Least function
will view this date?
 
Thanks.
This returns the min date in each field - one min date per field. Is there
a way to get the min date of 6 different fields on one record using a union
query - one min date per record?
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I made a mistake in my code. This line:

min = LBound(var)

should be corrected to this:

min = var(LBound(var))


If the VFP data is returning 0 (zero) then that will be the lowest
date value. As I said previously, you have to distinguish between no
date (zero in your case - NULL in my previous example) and a true
date. For the no-date value you have to substitute a default - or do
something so that you are getting reasonable results (whatever that
means to your or your customers).

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQCB2oIechKqOuFEgEQLqGgCgmNVt/C1Tppt/gi+g5vfOXS+cHjMAoMSb
qJyCD0hOt0OhqUPfQ5+qZ+Lc
=WSp+
-----END PGP SIGNATURE-----


I must have something wrong because I always get zero. I copied the VBA
function into a module just as you have it below. Here is the query I used
to test. There are no null values.

SELECT Least([RptDate]![RptBegin],[RptDate]![RptEnd]) AS SmallestDate1
FROM RptDate;

Once it works, I think I still have a problem that Nz won't help, because
the VFP table I am using does not have null values. It uses some value
which Access sees as 12:00:00 AM (or Saturday, December 30, 1899 in long
date format) in query results. I am not sure how the Nz or Least function
will view this date?


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

For .mdb/e files - perhaps something like this:

SELECT ID,
Least(Date1, Date2, Date3, Date4, Date5, Date6) As SmallestDate
FROM TableName


The VBA function Least() that gets the Minimum date:

Public Function Least(ParamArray var()As Variant) As Variant

Dim min As Variant
Dim i As Integer

' Initialize
min = LBound(var)
For i = LBound(var) to UBound(var)
If var(i) < min Then min = var(i)
Next i

Least = min

End Function

This function can be more refined so it doesn't run when there is an
empty array and you can add error handlers.

You could also put Nz() around the Date fields in the SELECT clause to
avoid getting NULLs in the Date fields. ***** This raises the
question of what to do when there is a null date? You might want to
put the default date at a very hi value - then it won't be selected
when run thru the Least() function. Or, you may want to know when the
date value is NULL - then allow Nz() to change the value to ZERO,
which would be the lowest value.

In either case use the Nz() function:

Nz(NULL,0) = 0
Nz(NULL, #1/1/9999#) = 1/1/9999

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQB6wAIechKqOuFEgEQJ/HgCfVzv/mD+41xLXksyQEHIVCrx15PgAnjzI
GUkv1XbRnKYnd1sCt6DzMZ/1
=tR/l
-----END PGP SIGNATURE-----


Randal wrote:


earliest

in
Click to expand...
Click to expand...
 
This works great. Thanks so much for your help.

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I made a mistake in my code. This line:

min = LBound(var)

should be corrected to this:

min = var(LBound(var))


If the VFP data is returning 0 (zero) then that will be the lowest
date value. As I said previously, you have to distinguish between no
date (zero in your case - NULL in my previous example) and a true
date. For the no-date value you have to substitute a default - or do
something so that you are getting reasonable results (whatever that
means to your or your customers).

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQCB2oIechKqOuFEgEQLqGgCgmNVt/C1Tppt/gi+g5vfOXS+cHjMAoMSb
qJyCD0hOt0OhqUPfQ5+qZ+Lc
=WSp+
-----END PGP SIGNATURE-----


I must have something wrong because I always get zero. I copied the VBA
function into a module just as you have it below. Here is the query I used
to test. There are no null values.

SELECT Least([RptDate]![RptBegin],[RptDate]![RptEnd]) AS SmallestDate1
FROM RptDate;

Once it works, I think I still have a problem that Nz won't help, because
the VFP table I am using does not have null values. It uses some value
which Access sees as 12:00:00 AM (or Saturday, December 30, 1899 in long
date format) in query results. I am not sure how the Nz or Least function
will view this date?


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

For .mdb/e files - perhaps something like this:

SELECT ID,
Least(Date1, Date2, Date3, Date4, Date5, Date6) As SmallestDate
FROM TableName


The VBA function Least() that gets the Minimum date:

Public Function Least(ParamArray var()As Variant) As Variant

Dim min As Variant
Dim i As Integer

' Initialize
min = LBound(var)
For i = LBound(var) to UBound(var)
If var(i) < min Then min = var(i)
Next i

Least = min

End Function

This function can be more refined so it doesn't run when there is an
empty array and you can add error handlers.

You could also put Nz() around the Date fields in the SELECT clause to
avoid getting NULLs in the Date fields. ***** This raises the
question of what to do when there is a null date? You might want to
put the default date at a very hi value - then it won't be selected
when run thru the Least() function. Or, you may want to know when the
date value is NULL - then allow Nz() to change the value to ZERO,
which would be the lowest value.

In either case use the Nz() function:

Nz(NULL,0) = 0
Nz(NULL, #1/1/9999#) = 1/1/9999

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQB6wAIechKqOuFEgEQJ/HgCfVzv/mD+41xLXksyQEHIVCrx15PgAnjzI
GUkv1XbRnKYnd1sCt6DzMZ/1
=tR/l
-----END PGP SIGNATURE-----


Randal wrote:


I have 6 fields in a table that contain dates. I need to pull the
earliest

date from all 6 fields into one field in a query. Is there a function
in

access to do this? thanks,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top