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
  #21  
Old 10-09-2006, 01:05 PM
IUnknown's Avatar
IUnknown IUnknown is offline
Senior Contributor

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


What did you input in the textboxes?
__________________
win7 : vs 2008 : .Net 3.5
Reply With Quote
  #22  
Old 10-10-2006, 01:10 AM
LauraL LauraL is offline
Regular
 
Join Date: Jun 2005
Posts: 97
Default

for example 22.10.2006 and 30.10.2006, I also tried 30/10/2006 and 10/30/2006.

Laura
Reply With Quote
  #23  
Old 10-10-2006, 09:09 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 you entered 22.10.2006 and 30.10.2006, what error did you get and on which line did it occur?

Did you find a format that will work using MS Access?
__________________
win7 : vs 2008 : .Net 3.5
Reply With Quote
  #24  
Old 10-10-2006, 09:21 AM
LauraL LauraL is offline
Regular
 
Join Date: Jun 2005
Posts: 97
Default

I get the following error, and somewhy it doesn't show the line in which it occurs (it usually always does):

Syntax error in date in query expression '[RecordDate] BETWEEN #00.08.2006# AND #00.17.2006#'

How can I find the right format in Access

Laura
Reply With Quote
  #25  
Old 10-10-2006, 09:36 AM
IUnknown's Avatar
IUnknown IUnknown is offline
Senior Contributor

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

I wonder why you get an error that has "." and not "/" even though you have used .ToString("mm/dd/yyyy") in your SQL statement.
Quote:
Syntax error in date in query expression '[RecordDate] BETWEEN #00.08.2006# AND #00.17.2006#'
And I noted that the dates are illegal (00 for day or month?).

Can you post the value of cmd?

> How can I find the right format in Access

Open up your query editor in SQL View and type in an SQL statement, something like this:
Select [RecordDate] From data_praak Where [RecordDate] > #12/1/2006#;
or
Select [RecordDate] From data_praak Where [RecordDate] > #12.1.2006#;
__________________
win7 : vs 2008 : .Net 3.5
Reply With Quote
  #26  
Old 10-11-2006, 09:19 AM
IUnknown's Avatar
IUnknown IUnknown is offline
Senior Contributor

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

Hi,
I realised the letter casing for the format I suggested for ToString() is not correct . One way to avoid this is use the DateTimeFormatInfo instead with the right culture.
Code:
' ToString("MM/dd/yyyy") Dim dtfi As Globalization.DateTimeFormatInfo = New Globalization.CultureInfo("en-US", False).DateTimeFormat Dim s As String = MyStartDate.ToString(dtfi.ShortDatePattern)
__________________
win7 : vs 2008 : .Net 3.5
Reply With Quote
  #27  
Old 10-14-2006, 07:11 AM
LauraL LauraL is offline
Regular
 
Join Date: Jun 2005
Posts: 97
Default

Still same error: '[RecordDate] BETWEEN #10.10.2006# AND #26.10.2006#'."}
Now I have tried the following code:

Code:
        Dim MyStartDate As Date
        Dim MyEndDate As Date

        Dim MyStartDateA As String
        Dim MyEndDateA As String

        MyStartDate = TextBox2.Text
        MyEndDate = TextBox3.Text

        MyStartDateA = Replace(MyStartDate, ".", "/")
        MyEndDateA = Replace(MyEndDate, ".", "/")

        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 [Praagi tekkimise kuupäev], [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 adapter1 As New OleDbDataAdapter(cmd, conn)
        Dim data_praak2 As New DataSet
        adapter1.Fill(data_praak2)
        DataGridView1.DataSource = data_praak2.Tables(0)
It's the first time I don't get and error, but the rows it sorts are really confusing (they are not always between my dates in my textboxes)

For example I have rows with dates (dd.mm.yyyy) in my database:
2.10.2006
5.10.2006
20.10.2006
30.11.2006

When I type into textboxes the dates 6.10.2006 and 21.10.2006, it selects out the following rows:
2.10.2006
20.10.2006

This is only example, sometimes it also leaves entries after my enddate.

I have also tried to switch day and month in textboxes but it doesn't help also.

I have been struggling with this for many days now. I have also read tens of treads concerning the same problem. None of them provide a solution. IS IT REALLY A PROBLEM THAT DOES'NT HAVE A PROPER SOLUTION AT ALL

Laura
Reply With Quote
  #28  
Old 10-15-2006, 12:00 PM
LauraL LauraL is offline
Regular
 
Join Date: Jun 2005
Posts: 97
Default

I think I am now a little closer to solution, but still haven't figured out the final solution. I have now the following code:

Code:
Dim MyDate As String
MyDate = Format(MonthCalendar1.SelectionStart.Date, "dd/MM/yyyy")


Dim MyNewDate As String


MyNewDate = Replace(MyDate, ".", "/")

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] = #" & MyNewDate & "#" 
Dim adapter1 As New OleDbDataAdapter(cmd, conn)
Dim data_praak2 As New DataSet
adapter1.Fill(data_praak2)
DataGridView1.DataSource = data_praak2.Tables(0)
It selects the rows where the date (dd.mm.yyyy) is for example:
15.10.2006
21.10.2006
24.10.2006

but not the dates:
5.10.2006
7.10.2006
9.10.2006

It seems that when day is smaller than 12, then it switches day and month and doesn't select the correct row. How can it be possible that with some dates its ok, but with some not.

Desperate Laura
Reply With Quote
  #29  
Old 10-16-2006, 12:28 PM
IUnknown's Avatar
IUnknown IUnknown is offline
Senior Contributor

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

It's good that you're using the MonthCalendar to ensure you get proper values for the dates. However, the Date has the method ToString() which you could use to convert the dates in to string values without having to use Replace(). As already mentioned in my previous post, you can use the DateTimeFormatInfo to get the dates converted into the format you need, corresponding to the culture you used to create the DateTimeFormatInfo.
Code:
Dim conn As String = "Provider=Mic... ' Create a DateTimeFormatInfo variable dtfi, using the en-US culture ' which has this date format: MM/dd/yyyy accepted by MS Access Dim dtfi As Globalization.DateTimeFormatInfo = New Globalization.CultureInfo("en-US", False).DateTimeFormat ' I will assume here that MonthCalendar1 is for the start date ' and MonthCalendar2 is for the end date Dim cmd As String = "SELECT ... FROM data_praak WHERE [RecordDate] " cmd += "BETWEEN #" & MonthCalendar1.SelectionStart.ToString(dtfi.ShortDatePattern) & "# " cmd += "AND #" & MonthCalendar2.SelectionStart.ToString(dtfi.ShortDatePattern) & "#" Dim adapter1 As New ...
Hope this helps!
__________________
win7 : vs 2008 : .Net 3.5
Reply With Quote
  #30  
Old 10-17-2006, 02:54 AM
LauraL LauraL is offline
Regular
 
Join Date: Jun 2005
Posts: 97
Default

Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you IUNKNOWN,... I finally got it working now.

Happy 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
 
-->