473,890 Members | 1,354 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 5691
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 "intuitivel y" 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>=St artdate and InvoiceDate<(En dDate+1)

Gadzukes, so many choices!

In message <eD************ **@TK2MSFTNGP12 .phx.gbl>, Siv
<ms**********@r emoveme.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.d emon.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*******@hotm ail.com> wrote in message
news:11******** *************@z 14g2000cwz.goog legroups.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.d emon.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 "intuitivel y" 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>=St artdate and InvoiceDate<(En dDate+1)

Gadzukes, so many choices!



In message <eD************ **@TK2MSFTNGP12 .phx.gbl>, Siv
<ms**********@r emoveme.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 misunderstandin g 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*****@reidDA SHhome.com> wrote in message
news:uE******** ******@TK2MSFTN GP15.phx.gbl...
"Chris Petchey" <ch****@soltec. demon.co.uk> wrote in message
news:PG******** ******@soltec.d emon.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******** ******@TK2MSFTN GP11.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

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

Similar topics

3
4987
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" you can be pretty tough on querystrings, if you use them at all. But user input could have a name like Mc'Donald, and we would not like quotes (wether single or double) in input to a database or an asp script. Though I beleive more dangerous in...
6
4771
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 appreciated. Thanks in advance
3
2772
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 get an error on this line as it looks for the query. Here's the first piece of the code where I'm able to get display calendar and the input boxes. Just trying to write back to database. Thanks in advance. Public Sub Page_Load(ByVal Sender...
22
6312
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 client (MS Access vs ..NET Windows Forms) would be preferred over the other. While I have some good arguments on both sides, I would appreciate your points of view on the topic.
16
2988
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 difference). No warning / confirmation messages are issued (eg. when running action queries, deleting records from a datasheet, deleting database objects such as tables).
6
6399
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 running under Windows Server 2003 with 30 virtual users, although the app falls over after a few mins and 5 users. It also takes COM+ out and the dllhost process needs to be stopped via 'end process' in task manager. Any help appreciated. ...
3
1226
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 roleplaying character. well, i think i have cracked it. but theres one slight problem i cant seem to get my head wrapped around. i have an IF-statement in my script. well, i have a rediculous ammount actually. but my script skips one of them, and its...
1
1604
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 trying to do an analysis on the words from a database. So i want to incrementally read in each word from the array into a select query Which i can do easily enough using this piece of code public function checkwords() { $db = new...
39
4302
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 just now, I am getting the subject error message (specifically: PHP has encountered an access violation at 00F76E21). The error is NOT occurring on every page request (but it is on most of them) and, when I get the error, simply pressing <F5to...
0
9980
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11236
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10836
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10926
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10468
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9643
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7172
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5856
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
3
3287
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.