Date = Current Date

[Aaron Sherber]

Hi,

I'm sure I'm missing something obvious here. I have a table with a
date/time field called Assign. I want to select all records from this
table where the date part of Assign is equal to today's date.

I'd like to do something like:

SELECT * FROM [Mytable]
WHERE DateValue([Assign])=DateValue(Now)

....except that DateValue isn't quite the function I'm looking for.

Any help would be appreciated.

Thanks,
Aaron.

 

[Guido]

SELECT * FROM [Mytable]
WHERE DateValue([Assign])=Date()

HTH

 

[Aaron Sherber]

SELECT * FROM [Mytable]
WHERE DateValue([Assign])=Date()

In Access 2000, that gets me "Data type mismatch in criteria
expression."

Doesn't Datevalue take a String argument? Is there no function that
returns the date portion of a date/time value?

Thanks,
Aaron.

 

[Fredg]

If [Assign] is a Date/Time datatype...

SELECT * FROM [Mytable]
WHERE [Assign]= Date();

Now() won't work as Now() includes a Time Value
whereas Date() is always Midnight.
There is no need to use the DateValue() function for this.

 

[Aaron Sherber]

If [Assign] is a Date/Time datatype...

SELECT * FROM [Mytable]
WHERE [Assign]= Date();

Now() won't work as Now() includes a Time Value
whereas Date() is always Midnight.

But [Assign] is a Date/Time datatype and also includes a time value. And
since this time value is never midnight, it will never match Date().

(Since [Assign] will never be a future value in this case, I could do

WHERE [Assign]>= Date()

but that doesn't really solve the larger problem.)

How about casting Date/Time values as Integers, which would give you
just the date part? Access doesn't seem to support CAST in SQL, and it
doesn't seem to like the Floor function, either.

Aaron.

 

[Rick Brandt]

SELECT * FROM [Mytable]
WHERE DateValue([Assign])=Date()

In Access 2000, that gets me "Data type mismatch in criteria
expression."

Doesn't Datevalue take a String argument? Is there no function that
returns the date portion of a date/time value?

DateValue can take a string or a date. I got the following in the Immediate debug
window.

?DateValue("3/23/01 10:15 AM")
3/23/2001

?DateValue(#3/23/01 10:15 AM#)
3/23/2001

 

[Fredg]

Aaron,
Int([Assign]) = Date()

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.

If [Assign] is a Date/Time datatype...

SELECT * FROM [Mytable]
WHERE [Assign]= Date();

Now() won't work as Now() includes a Time Value
whereas Date() is always Midnight.

But [Assign] is a Date/Time datatype and also includes a time value. And
since this time value is never midnight, it will never match Date().

(Since [Assign] will never be a future value in this case, I could do

WHERE [Assign]>= Date()

but that doesn't really solve the larger problem.)

How about casting Date/Time values as Integers, which would give you
just the date part? Access doesn't seem to support CAST in SQL, and it
doesn't seem to like the Floor function, either.

Aaron.

 

[Aaron Sherber]

DateValue can take a string or a date. I got the following in the Immediate debug
window.

I had a strange experience with DateValue. I actually had it working for
a while, but then some times I would close Access and come back to it
later, and then I would get the error I reported in an earlier post
(Data type mismatch in criteria
expression).

Aaron.

 

[Aaron Sherber]

Aaron,
Int([Assign]) = Date()

Yes! Thank you for that.

(As an aside, although I don't have much experience with Access, I've
been coding for 10 years, and I can't believe how absolutely terrible
the Access help files are.)

Aaron.

 

[Rick Brandt]

Aaron,
Int([Assign]) = Date()

Yes! Thank you for that.

(As an aside, although I don't have much experience with Access, I've
been coding for 10 years, and I can't believe how absolutely terrible
the Access help files are.)

They munged them up when they switched to HTML help in Access 2000. The help file in
Access 97 is great.