Finding dates from a query

[Stewart Allen]

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

[Douglas J. Steele]

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" <sa****@ThisPartNotVailid.wave.co.nz> wrote in message
news:bs**********@news.wave.co.nz...

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

[Stewart Allen]

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:Ze********************@news04.bloor.is.net.ca ble.rogers.com...

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" <sa****@ThisPartNotVailid.wave.co.nz> wrote in message
news:bs**********@news.wave.co.nz...

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