By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,779 Members | 1,291 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,779 IT Pros & Developers. It's quick & easy.

Finding dates from a query

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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


Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.