Date = Current Date

  • Thread starter Thread starter Aaron Sherber
  • Start date Start date
A

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.
 
SELECT * FROM [Mytable]
WHERE DateValue([Assign])=Date()
Click to expand...

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.
 
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.
 
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.
Click to expand...


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 said:
SELECT * FROM [Mytable]
WHERE DateValue([Assign])=Date()
Click to expand...

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?
Click to expand...

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
 
Aaron,
Int([Assign]) = Date()

--
Fred

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


Aaron Sherber said:
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.
Click to expand...


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.
Click to expand...
 
DateValue can take a string or a date. I got the following in the Immediate debug
window.
Click to expand...

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,
Int([Assign]) = Date()
Click to expand...

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.
 
Aaron Sherber said:
Aaron,
Int([Assign]) = Date()
Click to expand...

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.)
Click to expand...

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