[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Problem with an SQL + MS Access DB. 500 Points.

Posted on 2004-11-22
Medium Priority
Last Modified: 2010-04-23
I have an Access database to wich i connect through Dim DBConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\mydb.mdb".

I have this SQL query: SELECT a.* FROM Fisa a,Exp f WHERE a.nou_ID=f.nou_id AND (StartDate BETWEEN f.data1 and f.data2) AND (EndDate BETWEEN f.data1 AND f.data2)

where StartDate=6/15/2002 and EndDate=9/24/2002
          f.data1 is 1/20/2000 and f.data2=1/20/2005

I tryed these two variables as (date and as string ) dim StartDate,AndDate As Date.

The result i get is null, there is no record between those two dates. (In my databse there are about 10 records between those dates).

What do I do wrong???

Thanks a lot.

Question by:drcyrus3d
  • 2
LVL 14

Accepted Solution

ptakja earned 1000 total points
ID: 12646215
Typically, date strings in Select statements need to be surrounded by "#" characters. Try this line:

"SELECT a.* FROM Fisa a,Exp f WHERE a.nou_ID=f.nou_id AND (StartDate BETWEEN #" & f.data1 & "# and #" & f.data2 & "#) AND (EndDate BETWEEN #" & f.data1 & "# AND #" & f.data2 & "#)"
LVL 27

Assisted Solution

planocz earned 1000 total points
ID: 12646251
Sample ...

         Dim sStartDate As String
         Dim sEndDate As String
         Dim sTableName As String

        sTableName = "Orders"    
        sStartDate = "04-Jul-1996"
        sEndDate = "19-Jul-1996"

        sSql = ""
        sSql = "SELECT * "
        sSql += "FROM " & sTableName & " "
        sSql += "WHERE OrderDate between #" & sStartDate & "# and #" & sEndDate & "# "
        sSql += "ORDER BY OrderID"

LVL 14

Expert Comment

ID: 12646306
Expanding on planocz's post...

I prefer using the String.Format method as:

         Dim sStartDate As String
         Dim sEndDate As String
         Dim sTableName As String
         Dim sOrderBy as String

        sTableName = "Orders"    
        sStartDate = "04-Jul-1996"
        sEndDate = "19-Jul-1996"
        sOrderBy = "OrderID"

Dim sql as String = String.Format("SELECT * FROM {0} WHERE OrderDate BETWEEN #{1}# AND #{2}# ORDER BY {3}", sTableName, sStartDate, sEndDate, sOrderBy)


Author Comment

ID: 12647337
It worked perfectly. but the right answer is:

SELECT a.* FROM Fisa a,Exp f WHERE a.nou_ID=f.nou_id  AND (#" & StartDate & "# BETWEEN f.data1 and f.data2) AND (#" & EndDate & "# BETWEEN f.data1 and f.data2).

You both where close. I'll split the points.

Thanks a lot guys.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month19 days, 19 hours left to enroll

873 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