Searching between 2 DateTime Values using SELECT stmt in Vb.Net

I’m using VB.net 2003 application program.

I am trying to do a select statement whereby I'm searching between 2 datetime values that are being stored as datetime. records are stored inside Access.

For example, I’m searching between 2 datetime
So I need to find all the records in between 10:00 AM and 12:30 AM on 2/23/2009.


i tried this code
but i got the error showing below

Then i tried this code
But when I use DATEVALUE, it takes the date from the string and set time as midnight (00:00:00). So it returns no records between 10:00 AM and 12:30 AM on 2/23/2009, but I can see there are records.


Then i tried this code
And when I use TimeValue, it returns the time from the string and set date as jan 1st. so it returns no records between 10:00 AM and 12:30 AM on 2/23/2009, but I can see there are records.

Then i tried this code
but none of the codes above is returning records between 10:00 AM and 12:30 AM on 2/23/2009, but I can see there are records.

i searched and found all those examples. but that didn't work. Is there anyway i can search between 2 datetime values. i need to find all the records that lies between that time period (for example: between 10:00 AM and 12:30 AM on 2/23/2009).

If you have any idea how to do this, please let me know. if you can provide an example, then that will be great help for me.

Thanks in advance.

re: Searching between 2 DateTime Values using SELECT stmt in Vb.Net

when i tried these codes and it start working...
But when i try
Then its showing the below error
but when i try this
Then its returns the values.

Is it possible to use COUNT(*) or SUM(gTotal) while i'm checking DateTime Value? If you have any idea please help me.

Thanks in advance.

re: Searching between 2 DateTime Values using SELECT stmt in Vb.Net

Is OrderDate a varchar or a Date?

re: Searching between 2 DateTime Values using SELECT stmt in Vb.Net

OrderDate is Date/Time field.

re: Searching between 2 DateTime Values using SELECT stmt in Vb.Net

Check out Literal DateTimes and Their Delimiters (#) and let me know afterwards if you are still experiencing any problems.

re: Searching between 2 DateTime Values using SELECT stmt in Vb.Net

Hi. The main problem with your first try at this one is that you are passing date/time literals as if they are text within single quotes, and you do the same later when using DateValue and TimeValue, which take date/time arguments, not text strings.

The convention for the JET database engine when referring to date literals is to use # (the pound or hash character) as a delimiter. Substituting in your original SQL string:

You can use the Between operator to shorten this a little:
Within VB itself if you need to use DateValue or TimeValue in code you would not use delimiters if the arguments are already of date/time type. However, you are building a SQL string that is passed outside of the VB environment and interpreted by the database engine. In this case you are not actually passing the variable to the function as a date/time type; you are passing the value of that variable as a literal. For that reason you'd include the # delimiters in your SQL string:

However, this last statement is just a much more complicated way of doing the first one shown above.

In summary, you need the # delimiters if you are passing date literals to the operators or function concerned. When you build SQL strings you cannot reference VB variables outside of the VB environment, so you have to pass their literal values enclosed within delimiters as part of the SQL string.

For more on date/time values in ANSI SQL and Access see our insight article date-time literals and their delimiters

-Stewart

ps take note of the format for dates to which SQL statements should conform, as listed in the insight article. I note that you have already specified that your date component is in m/d/y format, which should be compliant with the ANSI standard.