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

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.

LVL 1
drcyrus3dAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ptakjaCommented:
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 & "#)"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
planoczCommented:
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"

0
ptakjaCommented:
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)

0
drcyrus3dAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.