Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB Databases Basics
|
VB Databases Basics Beginning-level VB coding questions specific to using VB with databases. Issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB Databases Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 29th, 2008, 03:35 PM
Authorized User
 
Join Date: Aug 2008
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default problem with VBA code.. need help

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 November 29th, 2008, 07:44 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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?





Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA Code D0MiN0 Access VBA 2 March 31st, 2008 03:55 AM
Code works in Excel VBA but not Access VBA fossx Access VBA 2 May 21st, 2007 08:00 AM
problem understaing this vba code! method Access VBA 1 August 8th, 2005 02:58 AM
Help with VBA Code KevinN Excel VBA 1 April 22nd, 2005 06:50 AM
VBA code desprate Access VBA 7 April 12th, 2005 08:50 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.