Select records between two dates
Select records between two dates
Select records between two dates
Select records between two dates
Select records between two dates
Select records between two dates Select records between two dates Select records between two dates Select records between two dates Select records between two dates Select records between two dates Select records between two dates Select records between two dates
Select records between two dates Select records between two dates
Select records between two dates
Go Back  Xtreme Visual Basic Talk > > > Select records between two dates


Reply
 
Thread Tools Display Modes
  #1  
Old 10-06-2006, 08:36 AM
LauraL LauraL is offline
Regular
 
Join Date: Jun 2005
Posts: 97
Default Select records between two dates


I'm still struggling with my selection strings. I have the following code for selecting record between two dates:

Code:
 Dim MyStartDate As Date
        Dim MyEndDate As Date
 
        MyStartDate = TextBox2.Text
        MyEndDate = TextBox3.Text
 
        Dim conn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\data.mdb;Persist Security Info=False"
        Dim cmd As String = "SELECT [RecordDate], [Toote kood], [Kliendi kood], [Toote nimetus], Klient, Tooraine, [Kogus (kg)], Värvaine, [Värvaine %], Lisaaine, [Lisaaine %], [Detail / Jääk], [Taaskasutus (JAH/EI)] FROM data_praak WHERE [RecordDate] BETWEEN '" & MyStartDate & "' AND '" & MyEndDate & "'"
        Dim adapter As New OleDbDataAdapter(cmd, conn)
        Dim ds As New DataSet
        adapter.Fill(ds)
        DataGridView1.DataSource = ds.Tables(0)
I get an error "Data type mismatch in criteria expression". What could cause this problem

Laura
Reply With Quote
  #2  
Old 10-06-2006, 09:16 AM
wayneph's Avatar
waynephSelect records between two dates wayneph is offline
Web Junkie

Retired Moderator
* Expert *
 
Join Date: Apr 2004
Location: D/FW, Texas, USA
Posts: 8,393
Default

In Access you put ## around dates instead of ''.

I really suggest buying a good book on SQL. It will help you a ton on questions like these...
__________________
-- wayne, MSSM Retired
> SELECT * FROM users WHERE clue > 0
0 rows returned
Reply With Quote
  #3  
Old 10-06-2006, 09:22 AM
LauraL LauraL is offline
Regular
 
Join Date: Jun 2005
Posts: 97
Default

Hello again, thanks, I will try that.

Could you suggest some good book on sql. I think it should consist of manipulating access data mainly. Or is there some good e-book available (for free maybe)

Laura
Reply With Quote
  #4  
Old 10-06-2006, 09:26 AM
wayneph's Avatar
waynephSelect records between two dates wayneph is offline
Web Junkie

Retired Moderator
* Expert *
 
Join Date: Apr 2004
Location: D/FW, Texas, USA
Posts: 8,393
Default

SQL in a NutShell or Teach Yourself SQL in 21 days are pretty good.

As far as ADO.NET you can't beat the ADO.NET Core Reference from MS Press. (I've only used the one for .NET 1.1, but I assume the 2.0 version is pretty good too.)
__________________
-- wayne, MSSM Retired
> SELECT * FROM users WHERE clue > 0
0 rows returned
Reply With Quote
  #5  
Old 10-06-2006, 12:47 PM
LauraL LauraL is offline
Regular
 
Join Date: Jun 2005
Posts: 97
Default

I have changed " to # but i still got the same error. Do you have any clue what else could cause it

Code:
...BETWEEN '# & MyStartDate & #' AND '# & MyEndDate & #'"
Laura
Reply With Quote
  #6  
Old 10-06-2006, 02:32 PM
wayneph's Avatar
waynephSelect records between two dates wayneph is offline
Web Junkie

Retired Moderator
* Expert *
 
Join Date: Apr 2004
Location: D/FW, Texas, USA
Posts: 8,393
Default

you changed the wrong thing. you changed to double quotes to pound signs. you should have changed the apostrophes.

If you print out the SQL before you try executing the command, You'll notice that the dates aren't even in it.
__________________
-- wayne, MSSM Retired
> SELECT * FROM users WHERE clue > 0
0 rows returned
Reply With Quote
  #7  
Old 10-06-2006, 03:47 PM
reboot's Avatar
rebootSelect records between two dates reboot is offline
Keeper of foo

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Graceland
Posts: 15,614
Default

in other words

Code:
...BETWEEN #" & MyStartDate & "# AND #" & MyEndDate & "#"
__________________
~ Quod non mortiferum, fortiorem me facit ~

Avatar by lebb
Reply With Quote
  #8  
Old 10-07-2006, 03:19 AM
LauraL LauraL is offline
Regular
 
Join Date: Jun 2005
Posts: 97
Default

I'm desperate here, I changed my code according to your suggestion but now I have this error:

{"Syntax error in date in query expression '[RecordDate] BETWEEN #10.10.2006# AND #26.10.2006#'."}

Laura
Reply With Quote
  #9  
Old 10-07-2006, 07:32 AM
wayneph's Avatar
waynephSelect records between two dates wayneph is offline
Web Junkie

Retired Moderator
* Expert *
 
Join Date: Apr 2004
Location: D/FW, Texas, USA
Posts: 8,393
Default

No Apostrophes.
__________________
-- wayne, MSSM Retired
> SELECT * FROM users WHERE clue > 0
0 rows returned
Reply With Quote
  #10  
Old 10-07-2006, 07:55 AM
IUnknown's Avatar
IUnknown IUnknown is offline
Senior Contributor

Forum Leader
* Expert *
 
Join Date: Oct 2004
Location: Montréal
Posts: 1,135
Default

Try changing the "." to "/".
__________________
win7 : vs 2008 : .Net 3.5
Reply With Quote
  #11  
Old 10-07-2006, 08:28 AM
LauraL LauraL is offline
Regular
 
Join Date: Jun 2005
Posts: 97
Default

Hi guys,

I have no apostrophes, my select string is exactly like Reboot suggested. Now i have also tried to type the dates into textboxes like dd/mm/yyyy, but it still gives me the same error: {"Syntax error in date in query expression '[RecordDate] BETWEEN #10.10.2006# AND #26.10.2006#'."}

Reply With Quote
  #12  
Old 10-07-2006, 08:34 AM
IUnknown's Avatar
IUnknown IUnknown is offline
Senior Contributor

Forum Leader
* Expert *
 
Join Date: Oct 2004
Location: Montréal
Posts: 1,135
Default

Try swapping the month and day, e.g. #mm/dd/yyyy#
__________________
win7 : vs 2008 : .Net 3.5
Reply With Quote
  #13  
Old 10-07-2006, 08:46 AM
LauraL LauraL is offline
Regular
 
Join Date: Jun 2005
Posts: 97
Default

I tried swapping day and month. Still error, now: Conversion from string "10/22/2006" to type 'Date' is not valid.

I don't think that the problem is in "/" and "." My dates are in database in format like dd.mm.yyyy (and also my computer regional setting are like that but I think this doesn't affect this matter).

Laura

Quote:
Originally Posted by IUnknown
Try swapping the month and day, e.g. #mm/dd/yyyy#
Reply With Quote
  #14  
Old 10-07-2006, 08:52 AM
IUnknown's Avatar
IUnknown IUnknown is offline
Senior Contributor

Forum Leader
* Expert *
 
Join Date: Oct 2004
Location: Montréal
Posts: 1,135
Default

When I used ".", I got the same error as you did, after changing it back to "/" it worked again.
__________________
win7 : vs 2008 : .Net 3.5
Reply With Quote
  #15  
Old 10-07-2006, 08:58 AM
IUnknown's Avatar
IUnknown IUnknown is offline
Senior Contributor

Forum Leader
* Expert *
 
Join Date: Oct 2004
Location: Montréal
Posts: 1,135
Default

What does your localized version of MS Acccess help say about Date and its use in an SQL statement? On mine it is:
Between #2/2/1999# And #12/1/1999# -- For an OrderDate field, dates from 2-Feb-99 through 1-Dec-99 (ANSI-89)
__________________
win7 : vs 2008 : .Net 3.5
Reply With Quote
  #16  
Old 10-07-2006, 09:00 AM
LauraL LauraL is offline
Regular
 
Join Date: Jun 2005
Posts: 97
Default

hmm. Couldn't it be that your date format is in database something like mm/dd/yyyy, thats why it works in your pc when you change . to /

I have tried this also but as I said in my earlier posts, it didn't help.


Quote:
Originally Posted by IUnknown
When I used ".", I got the same error as you did, after changing it back to "/" it worked again.
Reply With Quote
  #17  
Old 10-07-2006, 10:33 AM
IUnknown's Avatar
IUnknown IUnknown is offline
Senior Contributor

Forum Leader
* Expert *
 
Join Date: Oct 2004
Location: Montréal
Posts: 1,135
Default

You have used Date variables to convert the values in the textboxes,
Code:
Dim MyStartDate As Date Dim MyEndDate As Date MyStartDate = TextBox2.Text MyEndDate = TextBox3.Text
It should therefore accept dates in your local format, i.e., dd.mm.yyyy but would give a conversion error with this format mm/dd/yyyy.

When you insert the dates back in the SQL statement, you aren't using any formatting, so the dates are converted to string in the format of your local regional settings, i.e., dd.mm.yyyy, which I think Jet.OLEDB.4.0 doesn't like. Try to format the dates when converting them to string, e.g.
Code:
...BETWEEN #" & MyStartDate.ToString("mm/dd/yyyy") & "# AND #" & MyEndDate.ToString("mm/dd/yyyy") & "#;"
If this fails, you can try to build up a simple query in the MS Access query editor and experiment with different date formats to find one that'll work for you.

Hope this helps!
__________________
win7 : vs 2008 : .Net 3.5

Last edited by IUnknown; 10-07-2006 at 10:41 AM.
Reply With Quote
  #18  
Old 10-09-2006, 04:23 AM
LauraL LauraL is offline
Regular
 
Join Date: Jun 2005
Posts: 97
Default

Still nothing! I changed MyStartDate.ToString("mm/dd/yyyy"), now it shows the same error (Conversion from string "10/22/2006" to type 'Date' is not valid). I also tried ToString("dd/mm/yyyy"), ToString("dd.mm.yyyy") and some else combinations but with no luck. I also tried changing date format in access database, but this also didn't help.

I'm desperate here
Reply With Quote
  #19  
Old 10-09-2006, 08:08 AM
IUnknown's Avatar
IUnknown IUnknown is offline
Senior Contributor

Forum Leader
* Expert *
 
Join Date: Oct 2004
Location: Montréal
Posts: 1,135
Default

Can you post the changes you made to your code? I'm not sure where you've added the ToString to.
__________________
win7 : vs 2008 : .Net 3.5

Last edited by IUnknown; 10-09-2006 at 09:03 AM.
Reply With Quote
  #20  
Old 10-09-2006, 10:21 AM
LauraL LauraL is offline
Regular
 
Join Date: Jun 2005
Posts: 97
Default

I added it to select string like this:

Code:
...BETWEEN #" & MyStartDate.ToString("mm/dd/yyyy") & "# AND #" & MyEndDate.ToString("mm/dd/yyyy") & "#"
Laura
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Select records between two dates
Select records between two dates
Select records between two dates Select records between two dates
Select records between two dates
Select records between two dates
Select records between two dates Select records between two dates Select records between two dates Select records between two dates Select records between two dates Select records between two dates Select records between two dates
Select records between two dates
Select records between two dates
 
Select records between two dates
Select records between two dates
 
-->