Re: Earliest Date
From: Randal (rlwnr_at_hotmail.com)
Date: 02/03/04
- Next message: Allen Browne: "Re: Make tab control transparent?"
- Previous message: James Ham: "Re: Comments in Access SQL"
- In reply to: MGFoster: "Re: Earliest Date"
- Next in thread: MGFoster: "Re: Earliest Date"
- Reply: MGFoster: "Re: Earliest Date"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 3 Feb 2004 11:09:15 -0500
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?
"MGFoster" <me@privacy.com> wrote in message
news:YdyTb.8945$F23.3803@newsread2.news.pas.earthlink.net...
> -----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,
> >
> >
>
- Next message: Allen Browne: "Re: Make tab control transparent?"
- Previous message: James Ham: "Re: Comments in Access SQL"
- In reply to: MGFoster: "Re: Earliest Date"
- Next in thread: MGFoster: "Re: Earliest Date"
- Reply: MGFoster: "Re: Earliest Date"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
