Searching For Times Only In DateTime Field

  • Thread starter Thread starter Tim Wilson
  • Start date Start date
T

Tim Wilson

Is there a way to search (using a standard SQL Select statement) only the
time portion of a DateTime field? So search for any records that fall
between, lets say, 5:00 PM and 11:00 PM but on any date.

Thanks,
Tim Wilson
 
Tim Wilson said:
Is there a way to search (using a standard SQL Select statement) only
the time portion of a DateTime field? So search for any records that
fall between, lets say, 5:00 PM and 11:00 PM but on any date.
Click to expand...

You can do it in an Access database by using the VBA TimeValue()
function. It's not really "standard SQL", but the following statement
is perfectly legitimate when executed from Access:

SELECT * FROM MyTable
WHERE TimeValue(MyField) Between #5:00PM# And #11:00PM#;
 
Thanks for the information. Do you know off hand if Access is required to be
installed (or some Access components) in order for this function to be used.
I am using .Net and the OLE DB data provider to select data from an Access
database. Would installing MDAC and the Jet 4.0 (service pack 7) be enough
to utilize this function from code or do I need something more?

Thank-you kindly,
Tim Wilson
 
Tim Wilson said:
Thanks for the information. Do you know off hand if Access is
required to be installed (or some Access components) in order for
this function to be used. I am using .Net and the OLE DB data
provider to select data from an Access database. Would installing
MDAC and the Jet 4.0 (service pack 7) be enough to utilize this
function from code or do I need something more?
Click to expand...

I'm not sure, but I think you can run this so long as Jet and VBA are
installed. I was able to run it from Visual Basic using DAO:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine.OpenDatabase("C:\Documents and Settings\Dirk\My
Documents\Test.mdb")
Set rs = db.OpenRecordset( _
"SELECT * From tblEvents " & _
"WHERE TimeValue([WhenOccurred]) " & _
"Between #1:00PM# And #8:00PM#;")

Do Until rs.EOF
Debug.Print rs!ID, rs!WhenOccurred, rs!EventDesc
rs.MoveNext
Loop
rs.Close

Set rs = Nothing
db.Close
Set db = Nothing

I'm not familiar enough with ADO, much less ADO.Net, to test it as
quickly as no doubt you can. May I suggest you try it out and report
the results? Then we'll both know. :-)
 
I have tried it already in a .Net application and it works perfectly. But I
also have Access 2000 installed so I am still unsure as to whether or not
Access has installed something that is being used to add this functionality.
But the information you have given me so far is enough to push forward,
thanks.

--
Tim Wilson
..Net Compact Framework MVP

Dirk Goldgar said:
Tim Wilson said:
Thanks for the information. Do you know off hand if Access is
required to be installed (or some Access components) in order for
this function to be used. I am using .Net and the OLE DB data
provider to select data from an Access database. Would installing
MDAC and the Jet 4.0 (service pack 7) be enough to utilize this
function from code or do I need something more?
Click to expand...

I'm not sure, but I think you can run this so long as Jet and VBA are
installed. I was able to run it from Visual Basic using DAO:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine.OpenDatabase("C:\Documents and Settings\Dirk\My
Documents\Test.mdb")
Set rs = db.OpenRecordset( _
"SELECT * From tblEvents " & _
"WHERE TimeValue([WhenOccurred]) " & _
"Between #1:00PM# And #8:00PM#;")

Do Until rs.EOF
Debug.Print rs!ID, rs!WhenOccurred, rs!EventDesc
rs.MoveNext
Loop
rs.Close

Set rs = Nothing
db.Close
Set db = Nothing

I'm not familiar enough with ADO, much less ADO.Net, to test it as
quickly as no doubt you can. May I suggest you try it out and report
the results? Then we'll both know. :-)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Tim Wilson said:
I have tried it already in a .Net application and it works perfectly.
But I also have Access 2000 installed so I am still unsure as to
whether or not Access has installed something that is being used to
add this functionality. But the information you have given me so far
is enough to push forward, thanks.
Click to expand...

I see what you mean, but I, too, don't have a machine with (.Net AND NOT
Access). It seems to me that VBA must be installed, and I don't know
offhand if that is always installed with Jet, or under what
circumstances it might not be installed.

Note that you can accomplish the same as TimeValue() using Int() or
Fix() along with subtraction to get the fractional part of the date/time
value. However, those are both VBA functions, too, unless Jet supports
them internally as it does IIf().
 
Thanks for all your help, I'll keep looking into this issue to see what is
required to use TimeValue or possibly something similar.

Thanks
 
I'm not sure how .NET works with this, but wouldn't you need to set a
reference to the VBA library to get it to work? If so, delete the reference
and try it again. It seems to me that this function may also be a part of
one of the .NET libraries.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
But this function would not be getting called at the ".Net level". It is
part of a SQL statement that will be passed when querying the database. So
setting a reference to a VBA library or using some other .Net libraries I
don't think are going to help in this situation.
 
Tim,

They all use an Expression Service to be able to understand functions in SQL
statements. Other SQL languages do too (T-SQL for SQL-Server for example)
What makes JET so powerful is that is understands VBA functions, but you
need to have the VBA library referenced to make it work. You need to have a
vehicle (Access/VB/VB.Net) and a translator (ODBC/OLEDB). SQL statements do
not work in a vacuum, at least as far as I know.

Within Access, you cannot remove the VBA or Access references to see if it
will work without them because they are in use, the moment the app starts.
Dirk, checked from VB and found that he could make it work with a reference
to DAO.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
I was unaware of the "Expression Service" idea behind sql statement
execution. So JET will search for a specific dll to resolve a call to a
function like "TimeValue" or will it search all referenced (application
loaded) dll's for the calling application? I'm just a little unsure as to
how this whole process would work to resolve a function call in a sql
statement. Any hints would be greatly appreciated. Thanks.
 
First of all, it is only JET which resolves and outside language like VBA.
No other SQL-compliant database engine understands anything more than what
is programmed into the engine itself. The Expression Service is what allows
JET to understand exterior functions. That is essentially what makes JET
more powerful than any other database engine, at least for manipulating data
with outside functions. To a lesser degree, FoxPro can also evaluate
expressions, but not VBA expressions.

Before it can understand a function, JET has to understand what it does ...
the definition. It is the Expression Service which acts as a translator to
pass values to the SQL statement. It does not understand VBA constants, but
it does understand the values represented by those constants.

If you can get a copy of the Jet Programmers manual, it will go into much
more detail:

http://tinyurl.com/2dqmy
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top