• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

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
0
Wilder1626
Asked:
Wilder1626
  • 6
  • 2
  • 2
  • +1
2 Solutions
 
É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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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
 
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
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 6
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now