asked on

sql syntax in visual basic

i am developing a custom calendar/daytimer type thing, that stores appointments in an access database, so i got it to access the database and display the data ok, but the trouble is i am trying to get it to filter the query so that it only displays appointments that are going to happen within the next week so that the list isnt 3 miles long, and if the date has passed it wont be displayed to, once again conserve space.

so, here's what i have so far, this displays all the data in the database:

queryString = "SELECT * FROM toDoData"

this is what i thought it would look like with the filter:

Dim futureDate, todaysDate As Date
todaysDate = Today()
futureDate = DateAdd(DateInterval.Day, 7, Today())
queryString = "SELECT * FROM toDoData WHERE toDoDate BETWEEN " & todaysDate & " AND " & futureDate

the queryString should end up being something like:
SELECT * FROM toDoData WHERE toDoDate BETWEEN 06/05/31 AND 06/06/06

even when i try to hard code the dates without the variables, it does not seem to work out as planned, so i am thinking it has something to do with my syntax. any ideas?

ASKER CERTIFIED SOLUTION

Sancler

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vb_jonas

queryString = "SELECT * FROM toDoData WHERE toDoDate BETWEEN #" & todaysDate & "# AND #" & futureDate & "#"

vb_jonas

ah, seconds too late ... :-)

Éric Moreau

Hi Servo,

You should always format your date not to have problems with localisation:

queryString = "SELECT * FROM toDoData WHERE toDoDate BETWEEN #" & todaysDate.tostring("yyyy/MM/dd") & "# AND #" & futureDate.tostring("yyyy/MM/dd") & "#"

Cheers!

Servo

ASKER

fabulous! man, i barely had time to sneeze, and there in the email was my answer... thanks for your quick help! better luck next time vb_jonas :)

yea, i dont even think seconds...