sql syntax in visual basic

Posted on 2006-06-01
Medium Priority
Last Modified: 2011-09-20
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?
Question by:Servo
LVL 34

Accepted Solution

Sancler earned 500 total points
ID: 16806592

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

For MS Access, dates have to be enclosed in '#', and that applies equally when VB.NET passes a query string to Access.


LVL 12

Expert Comment

ID: 16806593
queryString = "SELECT * FROM toDoData WHERE toDoDate BETWEEN #" & todaysDate & "# AND #"  & futureDate & "#"
LVL 12

Expert Comment

ID: 16806597
ah, seconds too late ... :-)
LVL 71

Expert Comment

by:Éric Moreau
ID: 16806634
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") & "#"


Author Comment

ID: 16806680
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...

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Is your organization moving toward a cloud and mobile-first environment? In this transition, your IT department will encounter many challenges, such as navigating how to: Deploy new applications and services to a growing team Accommodate employee…
If you are looking for an automated solution for backup single or multiple Office 365 user mailboxes to Outlook data file, then you can use Kernel Office 365 Backup & Restore tool. Go through the video to check out the steps to backup single or mult…

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question