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]

Re: Select Current Date

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

Is that what you were looking for?

Imran.

"B-Dog" <bdog4@hotmail. com> wrote in message
news:ewHpy1nlEH A.1008@tk2msftn gp13.phx.gbl...[color=blue]
> 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()[/color]
works[color=blue]
> 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
>
>[/color]

[Jeff Johnson [MVP: VB]]

Re: Select Current Date


"B-Dog" <bdog4@hotmail. com> wrote in message
news:ewHpy1nlEH A.1008@tk2msftn gp13.phx.gbl...
[color=blue]
> 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()[/color]
works[color=blue]
> 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[/color]

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

[Jeff Johnson [MVP: VB]]

Re: Select Current Date


"Imran Koradia" <nospam@microso ft.com> wrote in message
news:%23BcXx8nl EHA.948@TK2MSFT NGP12.phx.gbl.. .
[color=blue]
> Now().Date or System.DateTime .Now.Date
>
> Is that what you were looking for?[/color]

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]

Re: Select Current Date

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)

"B-Dog" <bdog4@hotmail. com> wrote in message
news:ewHpy1nlEH A.1008@tk2msftn gp13.phx.gbl...
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]

Re: Select Current Date

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"



"Jeff Johnson [MVP: VB]" <i.get@enough.s pam> wrote in message
news:OTj%23t$nl EHA.1644@tk2msf tngp13.phx.gbl. ..[color=blue]
>
> "B-Dog" <bdog4@hotmail. com> wrote in message
> news:ewHpy1nlEH A.1008@tk2msftn gp13.phx.gbl...
>[color=green]
> > 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()[/color]
> works[color=green]
> > but doesn't select the date I assume cause now looks at the date and[/color][/color]
time?[color=blue][color=green]
> > How can I get this to only look at the date? Thanks[/color]
>
> According to my Jet SQL 4.0 help file, it supports a function called
> CURDATE(). Give that a shot.
>
>[/color]

[Imran Koradia]

Re: Select Current Date

> > Now().Date or System.DateTime .Now.Date[color=blue][color=green]
> >
> > Is that what you were looking for?[/color]
>
> 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[/color]
and[color=blue]
> format the date into it[/color]

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.

[B-Dog]

Re: Select Current Date

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


"Shiva" <shiva_sm@onlin e.excite.com> wrote in message
news:ejtp4BolEH A.3968@TK2MSFTN GP11.phx.gbl...[color=blue]
> 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[/color]
comparison,[color=blue]
> if required.
> (Even DateTime.Date sets the time part to 12:00 AM)
>
> "B-Dog" <bdog4@hotmail. com> wrote in message
> news:ewHpy1nlEH A.1008@tk2msftn gp13.phx.gbl...
> 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()[/color]
works[color=blue]
> 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
>
>
>[/color]

[Cor Ligthert]

Re: Select Current Date

B-Dog,

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

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

Cor

[Jeff Johnson [MVP: VB]]

Re: Select Current Date


"Imran Koradia" <nospam@microso ft.com> wrote in message
news:uspiMWolEH A.712@TK2MSFTNG P09.phx.gbl...
[color=blue]
> Oops..sorry. In that case, you can use the Date() function in Access which
> returns todays date.[/color]

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]

Re: Select Current Date

> The trick is that there are functions that are provided by ACCESS and
there[color=blue]
> are functions that are provided by JET. When you use Microsoft Access[/color]
itself[color=blue]
> 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.[/color]

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

Imran.

[B-Dog]

Re: Select Current Date

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

"Jeff Johnson [MVP: VB]" <i.get@enough.s pam> wrote in message
news:usBuFiplEH A.3968@TK2MSFTN GP11.phx.gbl...[color=blue]
>
> "Imran Koradia" <nospam@microso ft.com> wrote in message
> news:uspiMWolEH A.712@TK2MSFTNG P09.phx.gbl...
>[color=green]
> > Oops..sorry. In that case, you can use the Date() function in Access[/color][/color]
which[color=blue][color=green]
> > returns todays date.[/color]
>
> The trick is that there are functions that are provided by ACCESS and[/color]
there[color=blue]
> are functions that are provided by JET. When you use Microsoft Access[/color]
itself[color=blue]
> 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.
>
>[/color]