recordset limiting

[badboybrown]

Hello,

I am trying to limit the records in a recordset between two dates.
With a view to count the records and use that count later.

So, to test my code I created a continuous form with two unbound text
boxes to enter the dates and a button to apply the dates to the
recordsource.

Using the tips and hints from various sources, I figured the dates had
to be in American format. Thought I had cracked it, but testing has
proven that I get very anomalous results.

The table:

HolidayDate HolidayName
02-Jan-06 New Years Day
14-Apr-06 Good Friday
17-Apr-06 Easter Monday
01-May-06 Early May Bank Holiday
29-May-06 Spring Bank Holiday
28-Aug-06 Summer Bank Holiday
25-Dec-06 Christmas Day
26-Dec-06 Boxing Day
01-Jan-07 New Years Day

and the code:
__________________________________________________ ___________________________
Private Sub cmdDateLimiter_Click()
On Error GoTo Err_cmdDateLimiter_Click

Dim datStart As Date
Dim datEnd As Date

datStart = Format$(Me.txtStartDate, "mm\/dd\/yyyy")
datEnd = Format$(Me.txtEndDate, "mm\/dd\/yyyy")

Me.RecordSource = "Select * from tblHolidays where HolidayDate between
#" & datStart & "# and #" & datEnd & "#"

Me.Requery

Exit_cmdDateLimiter_Click:
Exit Sub

Err_cmdDateLimiter_Click:
MsgBox Err.Description
Resume Exit_cmdDateLimiter_Click

End Sub
__________________________________________________ ___________________________
When I enter "14-Apr-06" for txtStartDate and "01-May-06" for
txtEndDate

I get the last six records from "01-May-06 Early May Bank Holiday"
onwards. I would expect to get 3 records, "14-Apr-06" to "01-May-06"
inclusive.

Have I done something obviously wrong? or have I wandered down the
wrong track?

I appreciate any comments.

SEAN

[Keith Wilby]

"badboybrown" <se*****@gmail.com> wrote in message
news:11*********************@t31g2000cwb.googlegro ups.com...

Hello,

I am trying to limit the records in a recordset between two dates.
With a view to count the records and use that count later.

So, to test my code I created a continuous form with two unbound text
boxes to enter the dates and a button to apply the dates to the
recordsource.

Using the tips and hints from various sources, I figured the dates had
to be in American format. Thought I had cracked it, but testing has
proven that I get very anomalous results.

The table:

HolidayDate HolidayName
02-Jan-06 New Years Day
14-Apr-06 Good Friday
17-Apr-06 Easter Monday
01-May-06 Early May Bank Holiday
29-May-06 Spring Bank Holiday
28-Aug-06 Summer Bank Holiday
25-Dec-06 Christmas Day
26-Dec-06 Boxing Day
01-Jan-07 New Years Day

and the code:
__________________________________________________ ___________________________
Private Sub cmdDateLimiter_Click()
On Error GoTo Err_cmdDateLimiter_Click

Dim datStart As Date
Dim datEnd As Date

datStart = Format$(Me.txtStartDate, "mm\/dd\/yyyy")
datEnd = Format$(Me.txtEndDate, "mm\/dd\/yyyy")

Me.RecordSource = "Select * from tblHolidays where HolidayDate between
#" & datStart & "# and #" & datEnd & "#"

Me.Requery

Exit_cmdDateLimiter_Click:
Exit Sub

Err_cmdDateLimiter_Click:
MsgBox Err.Description
Resume Exit_cmdDateLimiter_Click

End Sub
__________________________________________________ ___________________________
When I enter "14-Apr-06" for txtStartDate and "01-May-06" for
txtEndDate

I get the last six records from "01-May-06 Early May Bank Holiday"
onwards. I would expect to get 3 records, "14-Apr-06" to "01-May-06"
inclusive.

Have I done something obviously wrong? or have I wandered down the
wrong track?

I appreciate any comments.

SEAN

I would have expected these

datStart = Format$(Me.txtStartDate, "mm\/dd\/yyyy")
datEnd = Format$(Me.txtEndDate, "mm\/dd\/yyyy")

to read

datStart = Format(Me.txtStartDate, "mm/dd/yyyy")
datEnd = Format(Me.txtEndDate, "mm/dd/yyyy")

Also, try declaring your date containers as Variant types instead of dates.

Regards,
Keith.
www.keithwilby.com

[badboybrown]

Thank you.

Declaring the containers as Variant types has done the trick. Would it
be too much to ask if you could explain why? I would've thought that
declaring them as dates would be required as they were being used
against dates.

as for the formatting of the dates, I used the tips from Allen Browne's
site and it states:

"Unfortunately, Format() replaces the slashes with the date separator
character defined in Control Panel | Regional Settings, so you must
specify literal slashes in the format string by preceding the slash
with backslashes."

Thanks once again.

I'd never get anything done without reading this site... I also get
nothing done because I'm reading this site.

[Keith Wilby]

"badboybrown" <se*****@gmail.com> wrote in message
news:11**********************@t31g2000cwb.googlegr oups.com...

Thank you.

Declaring the containers as Variant types has done the trick. Would it
be too much to ask if you could explain why? I would've thought that
declaring them as dates would be required as they were being used
against dates.
To be honest I can't remember, I think it's related to the time element of
the date data but I'm not sure. What I do remember is having endless
trouble trying to deal with date values with date type containers. Perhaps
one of the MVPs could jump in and explain why ...

as for the formatting of the dates, I used the tips from Allen Browne's
site and it states:

"Unfortunately, Format() replaces the slashes with the date separator
character defined in Control Panel | Regional Settings, so you must
specify literal slashes in the format string by preceding the slash
with backslashes."

Duly noted, thanks.

Keith.

[Tim Marshall]

badboybrown wrote:

Dim datStart As Date
Dim datEnd As Date

datStart = Format$(Me.txtStartDate, "mm\/dd\/yyyy")
datEnd = Format$(Me.txtEndDate, "mm\/dd\/yyyy")

Actually, the reason Keith's solution works is because you've made your
variables variants and access is correctly interpreting the format
functions as variant strings.

The format function returns a "variant (string)" (from the help on format).

Keith's solution seesm to be working for you. My own solution would
have been to keep the variables as dates and use either of the following
in place of format:

1) dateserial - see help on this function - if you can make sure you can
reliably breakdown what is entered into the correct year, month and
date, regardless of the order, you've got it licked

or

2) datevalue - again, see help. I like this one for Access dates
myself, as the user will be able to enter the date in the way their
computer is used to handling them and datevalue will correctly interpret it.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me