Re: Earliest Date
From: Randal (rlwnr_at_hotmail.com)
Date: 02/04/04
- Next message: G.Noseworthy: "finding missing numbers in a sequence"
- Previous message: DIOS: "Import Records from and external database"
- In reply to: MGFoster: "Re: Earliest Date"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 4 Feb 2004 11:33:35 -0500
This works great. Thanks so much for your help.
"MGFoster" <me@privacy.com> wrote in message
news:DE_Tb.11355$F23.6219@newsread2.news.pas.earthlink.net...
> -----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-----
>
>
>
> Randal wrote:
> > 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: G.Noseworthy: "finding missing numbers in a sequence"
- Previous message: DIOS: "Import Records from and external database"
- In reply to: MGFoster: "Re: Earliest Date"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
