problem with VBA code.. need help

arian29 · November 29th, 2008, 03:35 PM

hi, can someone help me whats wrong in the below code. i need to display all data from a database(access 2000) where the records should fall between the two selected date ranges. When i run the code i don't get any error but only a blank sheet is generated. Its in VBA.

=====CODE=====

dta = TextBox1.Text ' start date
dtb = TextBox2.Text ' end date

rs.Open "select * from Table1 where date between ' " & dta & " 'and ' " & dtb & " '", con
Sheet1.Range("a2").CopyFromRecordset (rs)
rs.Close

Old Pedant · November 29th, 2008, 07:44 PM

Date (and time) constants in Access are *ALWAYS* specified by surrounding the values with #...#.

Access is the only DB to user #...#. All others indeed use '...' as you show, though often with strict requirements of the format of the date within the apostrophes.

Also, since DATE is a keyword in Access (meaning "today's date"), when you use it as the name of a field, as you have, you need to enclose it in [...] to tell Access that it is *NOT* a keyword.

The other mistake you have is that you have a space between each ' and the succeeding or preceding " mark. Nope. No spaces allowed.

Finally, you probably really should validate those textbox values to be sure they *ARE* dates.

Code:

dta = CDate(TextBox1.Text)    ' start date
dtb = CDate(TextBox2.Text)    ' end date

rs.Open "select * from Table1 where [date] between #" & dta & "# and #" & dtb & "#", con

FINALLY...

A bit of caution here: If your [date] field actually holds a date *and* time, then you might not get the results you expect. For example, if you have
WHERE [date] BETWEEN #12/1/2008# AND #12/5/2008#
and your [date] field contains #12/5/2008 3:15:22 PM#, then it will *NOT* be BETWEEN the values, because a date without a time is implicitly a time of 0:00:00 AM.

So if you want to be SUPER safe, you could do:

Code:

dta = DateValue(CDate(TextBox1.Text))    ' start date
dtb = DateValue(CDate(TextBox2.Text))    ' end date

rs.Open "select * from Table1 where DateValue([date]) between #" & dta & "# and #" & dtb & "#", con

The DATEVALUE() function strips the time off of any date-and-time value that it is given and gives back just the date.

Okay?