Select Current Date

[B-Dog]

This has to be so simple, I'm trying to build a select query using OLEDB
adapter using Jet 4.0 that only selects records with today's date. In
access =Date() works great but of course it doesn't work here. Now() works
but doesn't select the date I assume cause now looks at the date and time?
How can I get this to only look at the date? Thanks

 

[Imran Koradia]

Now().Date or System.DateTime.Now.Date

Is that what you were looking for?

Imran.

 

[Jeff Johnson [MVP: VB]]

This has to be so simple, I'm trying to build a select query using OLEDB
adapter using Jet 4.0 that only selects records with today's date. In
access =Date() works great but of course it doesn't work here. Now() works
but doesn't select the date I assume cause now looks at the date and time?
How can I get this to only look at the date? Thanks

According to my Jet SQL 4.0 help file, it supports a function called
CURDATE(). Give that a shot.

 

[Jeff Johnson [MVP: VB]]

Now().Date or System.DateTime.Now.Date

Is that what you were looking for?

No, he's looking to use the database-level date functions. However, if
there's no other way around it he could build an SQL string dynamically and
format the date into it

 

[Shiva]

Hi,
You cannot get a DateTime value without time part. You may convert the
DateTime value to a string having only the date part and do the comparison,
if required.
(Even DateTime.Date sets the time part to 12:00 AM)

This has to be so simple, I'm trying to build a select query using OLEDB
adapter using Jet 4.0 that only selects records with today's date. In
access =Date() works great but of course it doesn't work here. Now() works
but doesn't select the date I assume cause now looks at the date and time?
How can I get this to only look at the date? Thanks

 

[B-Dog]

Thanks Jeff, that is what I'm looking for but unfortunately it doesn't like
it. It says unsupported function. I searched CurDate and found this

"Because CurDate as a VBA function, which could be use in case if you
execute
your query from inside of Access, but is not recognizable by OLEDB provider
or ODBC driver. You cannot use it and any other VBA functions if you need to
execute query from VB application

--
Val Mazur
Microsoft MVP"

 

[B-Dog]

I'm trying to do it on the database side using a data as follows but it
doesn't like Date() or CurDate()

SELECT Client, Description, FileFrom, FileID, FileName, FileTo, Link,
sBcc, sCC, sTo, WO, WONumber, fDate
FROM Files
WHERE (fDate = CURDATE())
ORDER BY FileID DESC

 

[Imran Koradia]

Now().Date or System.DateTime.Now.Date

No, he's looking to use the database-level date functions. However, if
there's no other way around it he could build an SQL string dynamically and
format the date into it

Oops..sorry. In that case, you can use the Date() function in Access which
returns todays date. You probably should already have a column in the table
which stores the date when the record was added. You can then build the
query to compare it to Date() which will give you the records with today's
date.

For instance, you have a 'DateAdded' column with the default value as
'Date()'. Now, your query would look something like:

Select * from myTable where DateAdded = Date()

Imran.

 

[Cor Ligthert]

B-Dog,

I am not sure anymore of the answer however did you already tried something
like this?

Dim bb As New OleDb.OleDbParameter("@date", New Date(Now.Year, Now.Month,
Now.Day))

Cor

 

[Jeff Johnson [MVP: VB]]

Oops..sorry. In that case, you can use the Date() function in Access which
returns todays date.

The trick is that there are functions that are provided by ACCESS and there
are functions that are provided by JET. When you use Microsoft Access itself
you can utilize both sets of functions, but when you use OLE DB you only
have access to the functions provided by Jet, and apparently Date() isn't
one of them.

 

[Imran Koradia]

The trick is that there are functions that are provided by ACCESS and
there

are functions that are provided by JET. When you use Microsoft Access itself
you can utilize both sets of functions, but when you use OLE DB you only
have access to the functions provided by Jet, and apparently Date() isn't
one of them.

hmm..with Jet, I just assumed the OP was using Access. You're right. Thanks
for pointing that out.

Imran.

 

[B-Dog]

Right, I think I'm going to have to go about this with a different approach.
Thanks for the help.