Servo
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
queryString = "SELECT * FROM toDoData WHERE toDoDate BETWEEN #" & todaysDate & "# AND #" & futureDate & "#"
ah, seconds too late ... :-)
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!
You should always format your date not to have problems with localisation:
queryString = "SELECT * FROM toDoData WHERE toDoDate BETWEEN #" & todaysDate.tostring("yyyy/
Cheers!
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...
yea, i dont even think seconds...