How to query all record between two date?
Don't worry about Convert(). It is a SQL Server funcytion, not a Jet
function.
The issue about whether or not the OrderDate column needs to be a Date/Time
column is rather critical to what you are trying to achieve.
If it is a Date/Time column then you can use the Date/Time syntax to query
the column.
If is text then you always have to format the date in your query exactly the
way the OrderDate values are formatted in the table, but even so, getting it
right will be difficult. To be able to do <, > or between comparisons on a
date stored as Text you would have to have the values stored in YYYYMMDD
format otherwise the comparisons will not work.
I would recommend that you change the column to Date/Time and use the
following:
WHERE OrderDate BETWEEN #" & selectedDateStart & "# And #" & selectedDa{*filter*}d
& "#"
On top of that, if you don't want to muck about with times then it would be
better to use:
WHERE OrderDate >= #" & format$(selectedDateStart, "MM/dd/yyyy") & "# And
OrderDate < #" & format$(dateadd("d", selectedDa{*filter*}d,1), "MM/dd/yyyy") &
"#"
The big point here is that if you are not careful, when using between you
will get everything on the selectedDateStart, but only get records where the
time part is exactly 00:00:00, because any other time part will be greater
than your selectedDa{*filter*}d and therefore be excluded.
If you use between you need to:
WHERE OrderDate BETWEEN #" & format$(selectedDateStart, "MM/dd/yyyy") & "#
And #" & format$(selectedDa{*filter*}d, "MM/dd/yyyy") & " 23:59:59" & "#"
This will ensure that you get everything for both the start and end days
and, of course, everything for any days in between.
Confused? Try it and you won't be.
Quote:
> Convert() function you talking about is it a VB function
> or SQL function, any example for how to use it?
> Thanks,
> dave
> >-----Original Message-----
> >If you surround the date in pound signs (#) within your
> >select query, SQL will treat the value as a date. For
> >the field, you will need to use the Convert() function
> in
> >your query if you do not use date/time format. I don't
> >think OrderDate needs to be Date/Time, but it does make
> >things easier.
> >Glen Kowalski, Elena Arzac, Ed Smith
> >--
> >This posting is provided "AS IS" with no warranties, and
> >confers no rights.
> >>-----Original Message-----
> >>Hello,
> >>I created a database using Access and it is simple:
> >>It has four fields.
> >>OrderNumber, OrderDate, OrderpayType, Amount
> >>THe orderdate is something like 1/1/2002
> >>and now I want to get all the record between 1/1/2002
> >and
> >>2/2/2002.
> >>Following is my query:
> >> myOleDbConnection = New OleDbConnection
> >>(myConnectionString)
> >> myOleDbCommand = New OleDbCommand("Select
> >>OrderNumber, OrderType, PayType, TotalAmount, OrderDate
> >>FROM Orders WHERE OrderDate BETWEEN '" &
> >>selectedDateStart & "' And '" & selectedDa{*filter*}d & "'",
> >>myOleDbConnection)
> >> Try
> >> myOleDbConnection.Open()
> >> myDataReader =
> >>myOleDbCommand.ExecuteReader()
> >> Do While (myDataReader.Read())
> >>......
> >>It does not work as expected. I mean it return wrong
> >>records.
> >>My questions are:
> >>Does Orderdate had to be the Date/Time ?
> >>If I already using Text on this field, Could I still be
> >>able to get what I want?
> >>Thanks for your help.
> >>dave
> >>.
> >.
