473,320 Members | 2,104 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Access BETWEEN statement seems faulty in VB.NET

Siv
Hi,
I just discovered that if in an ADO.NET query I use:

"Select * from Invoices Where InvoiceDate BETWEEN StartDate AND EndDate;"

In this case StartDate would be 1st of month and EndDate would be the last
day of the month, e.g. 31/08/2005 (UK DD/MM/YYYY format)
I find that the query is missing off any invoices that were done on the 31st
of the Month, in other words if in the between statement I don't specify a
time, ADO.NET or Jet is assuming I mean 31/08/2005 00:00:00.

This is counter intuitive and I'm sure is different to how ADO worked with
VB6, I am finding I now must use 31/08/2005 23:59:59 as the end date, or am
I missing something here??

--
Siv
Martley, Near Worcester, UK.
Nov 21 '05 #1
14 5650
And vb.net syntax in general is intuitive?
It may seem "counter intuitive" to you, but it makes good sense if you
really take the time to stop and think about it. You asked for invoices
between 1/8/2005 00:00:00 and 31/8/2005 00:00:00
If the query worked "intuitively" what would I do if I wanted to include
all invoices put on at midnight, but no further?

Perhaps the flaw lies in your invoice date. If your invoicedate field
data didn't carry the time element your query would give the result you
expect. Do you really need to know the time of the invoice?
Alternatively, dont use the between operator. It is inherently evil and
only included for backward compatibility for the abacus.
Instead consider InvoiceDate>=Startdate and InvoiceDate<(EndDate+1)

Gadzukes, so many choices!

In message <eD**************@TK2MSFTNGP12.phx.gbl>, Siv
<ms**********@removeme.sivill.com> writes
Hi,
I just discovered that if in an ADO.NET query I use:

"Select * from Invoices Where InvoiceDate BETWEEN StartDate AND EndDate;"

In this case StartDate would be 1st of month and EndDate would be the last
day of the month, e.g. 31/08/2005 (UK DD/MM/YYYY format)
I find that the query is missing off any invoices that were done on the 31st
of the Month, in other words if in the between statement I don't specify a
time, ADO.NET or Jet is assuming I mean 31/08/2005 00:00:00.

This is counter intuitive and I'm sure is different to how ADO worked with
VB6, I am finding I now must use 31/08/2005 23:59:59 as the end date, or am
I missing something here??


--
Chris Petchey
Nov 21 '05 #2

Siv wrote:
Hi,
I just discovered that if in an ADO.NET query I use:

"Select * from Invoices Where InvoiceDate BETWEEN StartDate AND EndDate;"

In this case StartDate would be 1st of month and EndDate would be the last
day of the month, e.g. 31/08/2005 (UK DD/MM/YYYY format)
I find that the query is missing off any invoices that were done on the 31st
of the Month, in other words if in the between statement I don't specify a
time, ADO.NET or Jet is assuming I mean 31/08/2005 00:00:00.
That's what you said, so you can hardly blame the system for taking
that to be what you meant.

Is 3.5 'between' 1 and 3 ? No.

This is counter intuitive and I'm sure is different to how ADO worked with
VB6,
Youre sureness is misplaced. Check it yourself and see!
I am finding I now must use 31/08/2005 23:59:59 as the end date, or am
I missing something here??


This root of your difficulty is, I would guess, treating DateTime
fields in Access as if they were Date fields (note that the latter do
not actually exist). The field you name 'InvoiceDate' actually holds a
date-and-time, and should be treated as such. Either be sure to only
store pure dates if you don't care about times; or explicitly drop the
time part of date-and-time values when you don't care about it.

--
Larry Lard
Replies to group please

Nov 21 '05 #3

Chris Petchey wrote:
[snippage]
Alternatively, dont use the between operator. It is inherently evil and
only included for backward compatibility for the abacus.


Why do you say this?

--
Larry Lard
Replies to group please

Nov 21 '05 #4
"Chris Petchey" <ch****@soltec.demon.co.uk> wrote in message news:PG**************@soltec.demon.co.uk...
Alternatively, dont use the between operator. It is inherently evil and
only included for backward compatibility for the abacus.

--
Chris Petchey


I assume this comment was made in jest.

Between is a perfectly legitimate and useful SQL predicate and is included in the current ANSI SQL standard. Because one doesn't
know how to use something does not make it evil.

--
Al Reid

Nov 21 '05 #5
Siv
Larry,

Thanks for the comments, I use the Access Date/Time field as it is the field
of choice for dates, but as you rightly surmise I am only interested in the
date.
The reason the issue has come up and caught me out is that in nearly all
locations in the software, I have used just the date, and the query does
work for those. I have slipped up somewhere and must be inserting the date
and time in some of my records. I think the routine that allows the user to
create a credit note must be where it is as the only items that are slipping
out of the query are credit notes.

At least I now know why I have always assumed VB6 was doing it differently
to dot net. It must be because I have always stored just the date and
ignored the time part and this is the first time I have noticed it??
--
Siv
Martley, Near Worcester, UK.
"Larry Lard" <la*******@hotmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

Siv wrote:
Hi,
I just discovered that if in an ADO.NET query I use:

"Select * from Invoices Where InvoiceDate BETWEEN StartDate AND EndDate;"

In this case StartDate would be 1st of month and EndDate would be the
last
day of the month, e.g. 31/08/2005 (UK DD/MM/YYYY format)
I find that the query is missing off any invoices that were done on the
31st
of the Month, in other words if in the between statement I don't specify
a
time, ADO.NET or Jet is assuming I mean 31/08/2005 00:00:00.


That's what you said, so you can hardly blame the system for taking
that to be what you meant.

Is 3.5 'between' 1 and 3 ? No.

This is counter intuitive and I'm sure is different to how ADO worked
with
VB6,


Youre sureness is misplaced. Check it yourself and see!
I am finding I now must use 31/08/2005 23:59:59 as the end date, or am
I missing something here??


This root of your difficulty is, I would guess, treating DateTime
fields in Access as if they were Date fields (note that the latter do
not actually exist). The field you name 'InvoiceDate' actually holds a
date-and-time, and should be treated as such. Either be sure to only
store pure dates if you don't care about times; or explicitly drop the
time part of date-and-time values when you don't care about it.

--
Larry Lard
Replies to group please

Nov 21 '05 #6
Siv
Chris,
Thanks for the "ticking off". My reason for saying it is counter intuitive is that in the definition of the between statement I looked up at

http://www.techonthenet.com/sql/between.php

in the date section "Example 2", they say:

"Example #2 - Dates
You can also use the BETWEEN function with dates.

SELECT *
FROM orders
WHERE order_date between to_date ('2003/01/01', 'yyyy/mm/dd')
AND to_date ('2003/12/31', 'yyyy/mm/dd');

This SQL statement would return all orders where the order_date is between Jan 1, 2003 and Dec 31, 2003 (inclusive).
It would be equivalent to the following SQL statement:

SELECT *
FROM orders
WHERE order_date >= to_date('2003/01/01', 'yyyy/mm/dd')
AND order_date <= to_date('2003/12/31','yyyy/mm/dd');"

The bit that makes the way it works seem counter intuitive is that word in brackets (inclusive), to me inclusive would mean from the start of the period to the end of the period and by end I mean the end of the last date of the between statement. If you say the statement is "inclusive" then only going to 31/08/2005 00:00:00 if I don't specifically quote a time seems contrary to the statement "inclusive".

Now that I am aware why it has behaved differently (because some bit of my code that deals with credit notes is using a date and a time and storing that in the record has made me fall foul of this where everywhere else I do not use the time), I can work round it.

Please note I have been using between for years, and have always believed "it did what it said on the tin" because I have always kept the time out of the equation (without realising that was important). The records that were being missed by the between statement were in fact the only ones that were in the table containing a time element. This is down to a blunder on my part in the Credit Note code, but as I often say to other developers, you often learn more from your mistakes than always getting it right.

--
Siv
Martley, Near Worcester, UK.
"Chris Petchey" <ch****@soltec.demon.co.uk> wrote in message news:PG**************@soltec.demon.co.uk...
And vb.net syntax in general is intuitive?
It may seem "counter intuitive" to you, but it makes good sense if you
really take the time to stop and think about it. You asked for invoices
between 1/8/2005 00:00:00 and 31/8/2005 00:00:00
If the query worked "intuitively" what would I do if I wanted to include
all invoices put on at midnight, but no further?

Perhaps the flaw lies in your invoice date. If your invoicedate field
data didn't carry the time element your query would give the result you
expect. Do you really need to know the time of the invoice?
Alternatively, dont use the between operator. It is inherently evil and
only included for backward compatibility for the abacus.
Instead consider InvoiceDate>=Startdate and InvoiceDate<(EndDate+1)

Gadzukes, so many choices!



In message <eD**************@TK2MSFTNGP12.phx.gbl>, Siv
<ms**********@removeme.sivill.com> writes
Hi,
I just discovered that if in an ADO.NET query I use:

"Select * from Invoices Where InvoiceDate BETWEEN StartDate AND EndDate;"

In this case StartDate would be 1st of month and EndDate would be the last
day of the month, e.g. 31/08/2005 (UK DD/MM/YYYY format)
I find that the query is missing off any invoices that were done on the 31st
of the Month, in other words if in the between statement I don't specify a
time, ADO.NET or Jet is assuming I mean 31/08/2005 00:00:00.

This is counter intuitive and I'm sure is different to how ADO worked with
VB6, I am finding I now must use 31/08/2005 23:59:59 as the end date, or am
I missing something here??


--
Chris Petchey

Nov 21 '05 #7
Siv
Al,
Thanks for sticking up for me/the between statement. I have always found it
useful.

As you will see in my response to Chris's post, the misunderstanding about
how it really works has caught me out because I have accidentally added some
code that is storing the time as well as the date in the table. After
reading the comments here I checked the table and sure enough, the records
that were being missed out of the query all had a time element whereas the
ones that worked did not.

--
Siv
Martley, Near Worcester, UK.
"Al Reid" <ar*****@reidDASHhome.com> wrote in message
news:uE**************@TK2MSFTNGP15.phx.gbl...
"Chris Petchey" <ch****@soltec.demon.co.uk> wrote in message
news:PG**************@soltec.demon.co.uk...
Alternatively, dont use the between operator. It is inherently evil and
only included for backward compatibility for the abacus.

--
Chris Petchey


I assume this comment was made in jest.

Between is a perfectly legitimate and useful SQL predicate and is included
in the current ANSI SQL standard. Because one doesn't
know how to use something does not make it evil.

--
Al Reid

Nov 21 '05 #8
Siv,

In addition to the others,

SQL statements are not a part of VB.Net itself.

It is just given to the database to execute conform its standards and return
a resultset.

By the way, the method in Adonet is to use parameters.

http://www.windowsformsdatagridhelp.com/default.aspx

I hope this helps,

Cor
Nov 21 '05 #9
Siv
Cor,
You mention that it is best to use parameters, do you mean parameter
queries? If so could you explain why that is so?
I tend to create SQL strings and execute them, that way there is nothing in
the database itself that a user might delete and thus break the program
(note: I am building single user applications on non networked PCs so they
are just running a local copy of an MS Access database and a VB.NET
application) so that the setup is easy. All the users of the application
are completely non-technical so ease of setup is paramount.

--
Siv
Martley, Near Worcester, UK.
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:Ou**************@TK2MSFTNGP11.phx.gbl...
Siv,

In addition to the others,

SQL statements are not a part of VB.Net itself.

It is just given to the database to execute conform its standards and
return a resultset.

By the way, the method in Adonet is to use parameters.

http://www.windowsformsdatagridhelp.com/default.aspx

I hope this helps,

Cor

Nov 21 '05 #10
Siv,

There is one simple answer.

If you use the parameters, than especially with dates, the conversion
between SQL datetime types and the Net datetime is done in VBNet.

Especially because there are a lot of cultures, than by instance a datetime
typed in the US as 08/31/2005 is for the system the same as that you or I
type 31/08/2005, while if it is a time, than you can use your 'pm', while I
use a 24 hour clock and that all with the same program, just depending on
the used culture settings.

(With SQL server it prevent as well for hackers, however you are talking
about Jet.)

It change for the rest nothing, you can use it with dynamic SQL (sqlstrings)
and with stored procedures.

I hope this gives some idea's

Cor
"Siv" <ms**********@removeme.sivill.com> schreef in bericht
news:OY*************@tk2msftngp13.phx.gbl...
Cor,
You mention that it is best to use parameters, do you mean parameter
queries? If so could you explain why that is so?
I tend to create SQL strings and execute them, that way there is nothing
in the database itself that a user might delete and thus break the program
(note: I am building single user applications on non networked PCs so they
are just running a local copy of an MS Access database and a VB.NET
application) so that the setup is easy. All the users of the application
are completely non-technical so ease of setup is paramount.

--
Siv
Martley, Near Worcester, UK.
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:Ou**************@TK2MSFTNGP11.phx.gbl...
Siv,

In addition to the others,

SQL statements are not a part of VB.Net itself.

It is just given to the database to execute conform its standards and
return a resultset.

By the way, the method in Adonet is to use parameters.

http://www.windowsformsdatagridhelp.com/default.aspx

I hope this helps,

Cor


Nov 21 '05 #11
Indeed it was a jest. In my haste I forgot the obligatory smiley symbol.
It is Cor Ligthert that is included for backward compatibility with the
abacus

:)

In message <uE**************@TK2MSFTNGP15.phx.gbl>, Al Reid
<ar*****@reidDASHhome.com> writes
"Chris Petchey" <ch****@soltec.demon.co.uk> wrote in message
news:PG**************@soltec.demon.co.uk...
Alternatively, dont use the between operator. It is inherently evil and
only included for backward compatibility for the abacus.

--
Chris Petchey


I assume this comment was made in jest.

Between is a perfectly legitimate and useful SQL predicate and is
included in the current ANSI SQL standard. Because one doesn't
know how to use something does not make it evil.

--
Al Reid


--
Chris Petchey
Nov 21 '05 #12
Chris,

I have always had my interest in the abacus. A pity is that I never worked
with that, can you enlighten me a little bit about that backwards
compatibility you are talking about.

I get the idea that you are an expert in using the abacus. (I have heard
that some people still use that to check the results of the computer so
maybe you are one of those).

Cor
Nov 21 '05 #13
For sure, how else do you check?

In message <##**************@TK2MSFTNGP10.phx.gbl>, "Cor Ligthert [MVP]"
<no************@planet.nl> writes
Chris,

I have always had my interest in the abacus. A pity is that I never worked
with that, can you enlighten me a little bit about that backwards
compatibility you are talking about.

I get the idea that you are an expert in using the abacus. (I have heard
that some people still use that to check the results of the computer so
maybe you are one of those).

Cor


--
Chris Petchey
Nov 21 '05 #14
Siv
Cor,
Thanks for the clarification, this will be useful in many projects.
--
Siv
Martley, Near Worcester, UK.
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:O7**************@TK2MSFTNGP14.phx.gbl...
Siv,

There is one simple answer.

If you use the parameters, than especially with dates, the conversion
between SQL datetime types and the Net datetime is done in VBNet.

Especially because there are a lot of cultures, than by instance a
datetime typed in the US as 08/31/2005 is for the system the same as that
you or I type 31/08/2005, while if it is a time, than you can use your
'pm', while I use a 24 hour clock and that all with the same program, just
depending on the used culture settings.

(With SQL server it prevent as well for hackers, however you are talking
about Jet.)

It change for the rest nothing, you can use it with dynamic SQL
(sqlstrings) and with stored procedures.

I hope this gives some idea's

Cor
"Siv" <ms**********@removeme.sivill.com> schreef in bericht
news:OY*************@tk2msftngp13.phx.gbl...
Cor,
You mention that it is best to use parameters, do you mean parameter
queries? If so could you explain why that is so?
I tend to create SQL strings and execute them, that way there is nothing
in the database itself that a user might delete and thus break the
program (note: I am building single user applications on non networked
PCs so they are just running a local copy of an MS Access database and a
VB.NET application) so that the setup is easy. All the users of the
application are completely non-technical so ease of setup is paramount.

--
Siv
Martley, Near Worcester, UK.
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:Ou**************@TK2MSFTNGP11.phx.gbl...
Siv,

In addition to the others,

SQL statements are not a part of VB.Net itself.

It is just given to the database to execute conform its standards and
return a resultset.

By the way, the method in Adonet is to use parameters.

http://www.windowsformsdatagridhelp.com/default.aspx

I hope this helps,

Cor



Nov 21 '05 #15

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Mats | last post by:
It's good practice to validate input, not only where it should be coming from, but from anywhere it's possible to change or add input for a "client". If all user input is transfered using "post"...
6
by: Peter Frost | last post by:
Please help I don't know if this is possible but what I would really like to do is to use On Error Goto to capture the code that is being executed when an error occurs. Any help would be much...
3
by: Hutty | last post by:
I'm trying create a calendar control that updates an access database with events. I have some code I managed to piece together. First error I'm running into is the Mycommand.ExecuteNonQuery(). I...
22
by: Jordan S. | last post by:
SQL Server will be used as the back-end database to a non trivial client application. In question is the choice of client application: I need to be able to speak intelligently about when one...
16
by: RichardP | last post by:
Hi there everyone - I'm new to this forum. I am having an issue when running an application from an instance of Access which has been started through automation (early or late bound, makes no...
6
by: SteveB | last post by:
Hello All I'm getting this chain of errors from an app. I was wondering if anyone recognised what the problem might relate to. I really need to know if they point to hardware or software. We are...
3
by: vegtard | last post by:
by now, you have no doupt replied to many of mine and my buddy (børntard)'s questions about our faulty programming concerning the over-complicated mega-script to design your dungeons and dragons...
1
by: boyindie86 | last post by:
Hi I have been fighting with this bit of code for two days I just can't get it to work properly, i am reading a set of words that hav been taking from a web page and stored in an array, I am now...
39
by: Martin | last post by:
I have an intranet-only site running in Windows XPPro, IIS 5.1, PHP 5.2.5. I have not used or changed this site for several months - the last time I worked with it, all was well. When I tried it...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.