469,602 Members | 1,751 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,602 developers. It's quick & easy.

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

Nov 12 '05 #1
2 1821
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
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.

Similar topics

2 posts views Thread by Jason Tudisco | last post: by
6 posts views Thread by Bill R via AccessMonster.com | last post: by
18 posts views Thread by PC Datasheet | last post: by
2 posts views Thread by ElkGroveR | last post: by
1 post views Thread by pitfour.ferguson | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.