runtime error -2147217900(80040e14). on search tool

Hello all

I have an issue on this code when i try to search in my access dbas in 5 diffrent column, when there is a date between DTPicker1 and DTPicker2.


I have a runtime error -2147217900(80040e14).

It says, syntax error.


If ComTypederecherche.Text = "Date d'étapes" Then
sSQL2 = "SELECT [Date_of_day],[Client],[Adresse],[Ville],[Province],[Code_Postal] ,[Telephone] ,[Cellulaire] ,[Courriel] ,[Permis_Conduire],[Type_Boisson],[No_Lot],[Date_Depart],[Date_Chartalisation] ,[Date_Soutirage] ,[Date_Stabilisation] ,[Date_Filtration] ,[Date_Embouteillage],[Genre_Boisson] ,[Notes]   FROM [DBase_Vignoble] Where " _
& "Date_Chartalisation BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#" _
 & "Date_Soutirage BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#" _
 & "Date_Stabilisation BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#" _
  & "Date_Filtration BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#" _
  & "Date_Embouteillage BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#"
 
 sSQL2 = sSQL2 & " ORDER BY [Date_Soutirage]"

oRST2.Open sSQL2, oConnect2

If oRST2.BOF = False Then

Do Until oRST2.EOF
  MSHFlexGrid1.AddItem oRST2("Date_of_day") & vbTab & oRST2("Client") & vbTab & oRST2("Adresse") & vbTab & oRST2("Ville") & vbTab & oRST2("Province") & vbTab & oRST2("Code_Postal") & vbTab & oRST2("Telephone") & vbTab & oRST2("Cellulaire") & vbTab & oRST2("Courriel") & vbTab & oRST2("Permis_Conduire") & vbTab & oRST2("Type_Boisson") & vbTab & oRST2("No_Lot") & vbTab & oRST2("Date_Depart") & vbTab & oRST2("Date_Chartalisation") & vbTab & oRST2("Date_Soutirage") & vbTab & oRST2("Date_Stabilisation") & vbTab & oRST2("Date_Filtration") & vbTab & oRST2("Date_Embouteillage") & vbTab & oRST2("Genre_Boisson") & vbTab & oRST2("Notes") & vbTab
  oRST2.MoveNext
Loop

End If

Open in new window


Thanks again for your help
LVL 11
Wilder1626Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Éric MoreauSenior .Net ConsultantCommented:
it is hard to read.

to debug, i would start with the query itself and the order by clause and add the where clause line by line
0
kbireckiCommented:
Is it failing on the execution of the SQL query?  Can you output the value of the sSQL2 variable right before it executes "oRST2.Open sSQL2, oConnect2" and post it here?
0
Wilder1626Author Commented:
Hello kbirecki

how can i Can you output the value of the sSQL2 variable right before it executes "oRST2.Open sSQL2, oConnect2"?

I also did some test with single where clause like this:
& "Date_Chartalisation BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#"

All of them where working perfectly well.

It's really when they are all together that they dont work.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Wilder1626Author Commented:
just to clarify some more.

I need to search all this column separately. for sure, the same date cannot be in all columns on the same row. So i did try "or" instead if of &. but no good result.

Where " _
& "Date_Chartalisation BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#" _

Open in new window


and

Where " _
 & "Date_Soutirage BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#" _

Open in new window



and

Where " _
 & "Date_Stabilisation BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#" _

Open in new window

 

and

Where " _
  & "Date_Filtration BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#" _
  

Open in new window



and

Where " _
  & "Date_Embouteillage BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#"

Open in new window

0
Éric MoreauSenior .Net ConsultantCommented:
you are missing AND:

sSQL2 = "SELECT [Date_of_day],[Client],[Adresse],[Ville],[Province],[Code_Postal] ,[Telephone] ,[Cellulaire] ,[Courriel] ,[Permis_Conduire],[Type_Boisson],[No_Lot],[Date_Depart],[Date_Chartalisation] ,[Date_Soutirage] ,[Date_Stabilisation] ,[Date_Filtration] ,[Date_Embouteillage],[Genre_Boisson] ,[Notes]   FROM [DBase_Vignoble] Where " _
& "Date_Chartalisation BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#" _
 & " AND Date_Soutirage BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#" _
 & " AND Date_Stabilisation BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#" _
  & " AND Date_Filtration BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#" _
  & "AND Date_Embouteillage BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#"
 
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
kbireckiCommented:
@wilder1626, emoreau may be right about your AND's.  Those are important.  You can check your sql by adding a statement like this right before it executes:
debug.print sSQL2

Open in new window


Then out wil show up in the debug window and you can post it here as well as copy it to SQL Mgmt Studio and execute/test it directly and get syntax help from Mgmt Studio.
0
HooKooDooKuCommented:
Is the format of your dates correct?

Back when I was programming against an Access database and using '#' to enclose dates, our dates were formatted #mm/dd/yyyy#.  Are you sure "#yyyy-mm-dd#" a valid date format for your database/SQL?
0
Wilder1626Author Commented:
Oh!!! that make sense.

let me try this tonight and i will let you know the result.

Thanks again.

I will also try the debug.print sSQL2 just before fixing the code. I will have to remember that step for next time.
0
Wilder1626Author Commented:
Hello all

This is the result i have with the debug.print.sSQL2

SELECT [Date_of_day],[Client],[Adresse],[Ville],[Province],
 ,[Telephone] ,[Cellulaire] ,[Courriel] ,[Permis_Conduire],[Type_Boisson],[No_Lot],[Date_Depart],[Date_Chartalisation] ,[Date_Soutirage] ,[Date_Stabilisation] ,[Date_Filtration] ,[Date_Embouteillage],[Genre_Boisson] ,[Notes]   FROM [DBase_Vignoble] Where Date_Chartalisation BETWEEN #2011-12-29# AND #2012-03-17#Date_Soutirage BETWEEN #2011-12-29# AND #2012-03-17#Date_Stabilisation BETWEEN #2011-12-29# AND #2012-03-17#Date_Filtration BETWEEN #2011-12-29# AND #2012-03-17#Date_Embouteillage BETWEEN #2011-12-29# AND #2012-03-17# ORDER BY [Date_Soutirage]

You will see the date format in my access dbase in the picture bellow.

Now if i use this code, i have something only if i have a huge range:
[code]sSQL2 = "SELECT [Date_of_day],[Client],[Adresse],[Ville],[Province],[Code_Postal] ,[Telephone] ,[Cellulaire] ,[Courriel] ,[Permis_Conduire],[Type_Boisson],[No_Lot],[Date_Depart],[Date_Chartalisation] ,[Date_Soutirage] ,[Date_Stabilisation] ,[Date_Filtration] ,[Date_Embouteillage],[Genre_Boisson] ,[Notes]   FROM [DBase_Vignoble] Where " _
& "Date_Chartalisation BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#" _
 & " AND Date_Soutirage BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#" _
 & " AND Date_Stabilisation BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#" _
  & " AND Date_Filtration BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#" _
  & "AND Date_Embouteillage BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#"
 

Open in new window





SELECT [Date_of_day],[Client],[Adresse],[Ville],[Province],[Code_Postal] ,[Telephone] ,[Cellulaire] ,[Courriel] ,[Permis_Conduire],[Type_Boisson],[No_Lot],[Date_Depart],[Date_Chartalisation] ,[Date_Soutirage] ,[Date_Stabilisation] ,[Date_Filtration] ,[Date_Embouteillage],[Genre_Boisson] ,[Notes]   FROM [DBase_Vignoble] Where Date_Chartalisation BETWEEN #2011-12-29# AND #2012-02-24# AND Date_Soutirage BETWEEN #2011-12-29# AND #2012-02-24# AND Date_Stabilisation BETWEEN #2011-12-29# AND #2012-02-24# AND Date_Filtration BETWEEN #2011-12-29# AND #2012-02-24#AND Date_Embouteillage BETWEEN #2011-12-29# AND #2012-02-24# ORDER BY [Date_Soutirage]



date-format.jpg
0
Wilder1626Author Commented:
oh!!!!!

I found something here.

It look's like it"s working like this:

sSQL2 = "SELECT [Date_of_day],[Client],[Adresse],[Ville],[Province],[Code_Postal] ,[Telephone] ,[Cellulaire] ,[Courriel] ,[Permis_Conduire],[Type_Boisson],[No_Lot],[Date_Depart],[Date_Chartalisation] ,[Date_Soutirage] ,[Date_Stabilisation] ,[Date_Filtration] ,[Date_Embouteillage],[Genre_Boisson] ,[Notes]   FROM [DBase_Vignoble] Where " _
& "Date_Chartalisation BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#" _
 & " or Date_Soutirage BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#" _
 & " or Date_Stabilisation BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#" _
  & " or Date_Filtration BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#" _
  & "or Date_Embouteillage BETWEEN #" & Format(DTPicker1, "yyyy-mm-dd") & "# AND " _
 & "#" & Format(DTPicker2, "yyyy-mm-dd") & "#"

Open in new window


Switch the AND with OR

Let me do some test
0
Wilder1626Author Commented:
Hello All

Thanks again for your help.

Everything is now working great.

Happy new year!!!
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 Classic

From novice to tech pro — start learning today.