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

Posted on 2004-11-22
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
    LVL 14

    Accepted Solution

    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

    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

    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)

    LVL 1

    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    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…
    1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now