Finding dates from a query

Hi There

I have a function that does some calculations depending on the 2 date
arguments passed into it:
Function GetPeriods(dteStart As Date, dteEnd As Date) As Integer

The function has 2 more arguments but they are not causing the problems I'm
encountering.

The 2 date arguments are used to build a dynamic SQL statement for a
recordset. If the date in the query doesn't match the dteStart it must find
the date in the query closest to this date without going over it, I did this
by using the DLast function
dteStart = DLast("[DateStart]", "qryPeriods", "[DateStart]<=#" & dteStart &
"#")
The DLast function works fine until I pass through a date earlier than the
earliest date in the table, Invalid use of null error.

I want to do the same sort of thing with the second argument, dteEnd, but to
find the same date or a later date from the query than the dteEnd argument
or find the last date if the dteEnd is greater than the last date in the
query. To complicate things more the dteEnd looks in a totally different
date field called DateTo. A period has a StartDate and an EndDate so the
need for the 2 separate date fields.

Can somebody guide me in the right direction in how to find these dates?

Stewart

re: Finding dates from a query

DLast will return a Null if there isn't data to satisfy it, and the only
variable type that can accept a Null is a Variant.

You can either store what's returned into a Variant, and then assign it to
dteStart if that variant isn't null, or you can set a default value, using
the Nz function:

dteStart = Nz(DLast("[DateStart]", "qryPeriods", "[DateStart]<=#" & dteStart
& "#"), #1/1/1970#)



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)



"Stewart Allen" <sagasu@ThisPartNotVailid.wave.co.nz> wrote in message
news:bsed7o$toj$1@news.wave.co.nz...[color=blue]
> Hi There
>
> I have a function that does some calculations depending on the 2 date
> arguments passed into it:
> Function GetPeriods(dteStart As Date, dteEnd As Date) As Integer
>
> The function has 2 more arguments but they are not causing the problems[/color]
I'm[color=blue]
> encountering.
>
> The 2 date arguments are used to build a dynamic SQL statement for a
> recordset. If the date in the query doesn't match the dteStart it must[/color]
find[color=blue]
> the date in the query closest to this date without going over it, I did[/color]
this[color=blue]
> by using the DLast function
> dteStart = DLast("[DateStart]", "qryPeriods", "[DateStart]<=#" & dteStart[/color]
&[color=blue]
> "#")
> The DLast function works fine until I pass through a date earlier than the
> earliest date in the table, Invalid use of null error.
>
> I want to do the same sort of thing with the second argument, dteEnd, but[/color]
to[color=blue]
> find the same date or a later date from the query than the dteEnd argument
> or find the last date if the dteEnd is greater than the last date in the
> query. To complicate things more the dteEnd looks in a totally different
> date field called DateTo. A period has a StartDate and an EndDate so the
> need for the 2 separate date fields.
>
> Can somebody guide me in the right direction in how to find these dates?
>
> Stewart
>
>
>[/color]

re: Finding dates from a query

Thanks Douglas. Didn't think about using the second part of the Nz function.

Stewart


"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:ZeAGb.82426$2We1.58629@news04.bloor.is.net.ca ble.rogers.com...[color=blue]
> DLast will return a Null if there isn't data to satisfy it, and the only
> variable type that can accept a Null is a Variant.
>
> You can either store what's returned into a Variant, and then assign it to
> dteStart if that variant isn't null, or you can set a default value, using
> the Nz function:
>
> dteStart = Nz(DLast("[DateStart]", "qryPeriods", "[DateStart]<=#" &[/color]
dteStart[color=blue]
> & "#"), #1/1/1970#)
>
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (No private e-mails, please)
>
>
>
> "Stewart Allen" <sagasu@ThisPartNotVailid.wave.co.nz> wrote in message
> news:bsed7o$toj$1@news.wave.co.nz...[color=green]
> > Hi There
> >
> > I have a function that does some calculations depending on the 2 date
> > arguments passed into it:
> > Function GetPeriods(dteStart As Date, dteEnd As Date) As Integer
> >
> > The function has 2 more arguments but they are not causing the problems[/color]
> I'm[color=green]
> > encountering.
> >
> > The 2 date arguments are used to build a dynamic SQL statement for a
> > recordset. If the date in the query doesn't match the dteStart it must[/color]
> find[color=green]
> > the date in the query closest to this date without going over it, I did[/color]
> this[color=green]
> > by using the DLast function
> > dteStart = DLast("[DateStart]", "qryPeriods", "[DateStart]<=#" &[/color][/color]
dteStart[color=blue]
> &[color=green]
> > "#")
> > The DLast function works fine until I pass through a date earlier than[/color][/color]
the[color=blue][color=green]
> > earliest date in the table, Invalid use of null error.
> >
> > I want to do the same sort of thing with the second argument, dteEnd,[/color][/color]
but[color=blue]
> to[color=green]
> > find the same date or a later date from the query than the dteEnd[/color][/color]
argument[color=blue][color=green]
> > or find the last date if the dteEnd is greater than the last date in the
> > query. To complicate things more the dteEnd looks in a totally different
> > date field called DateTo. A period has a StartDate and an EndDate so the
> > need for the 2 separate date fields.
> >
> > Can somebody guide me in the right direction in how to find these dates?
> >
> > Stewart
> >
> >
> >[/color]
>
>[/color]