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

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

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?

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.

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") & "#" _

Select allOpen in new window

and

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

Select allOpen in new window

and

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

Select allOpen in new window

 

and

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

Select allOpen in new window

and

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

Select allOpen in new window

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?

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.

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

Select allOpen 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

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

Select allOpen in new window

Switch the AND with OR

Let me do some test

Hello All

Thanks again for your help.

Everything is now working great.

Happy new year!!!