Date Search Not Working - Using DateValue()

[Guest]

There is a date field I need to search on with normal date parameter syntax.
I am stripping out the time value by creating this expression in a new field:
OrdDate: DateValue([OrderDate]). Problem is, this new field is not letting
me search like a date field. To bring up a date, I have to search like this:
Like "*3/27/2006*".

Is there a way to make this field search like a date? The underlying field,
BTW, is in Date/Time format. And, I've set the expression's field properties
to be "Short Date".

 

[Ken Snell \(MVP\)]

Delimit date values with # character, and be sure to use mm/dd/yyyy format:

SELECT * FROM Tablename
WHERE OrdDate = #03/28/2006#;

 

[Guest]

I'm having the same problem but I'm grabbing data based on date from other
sources such as db2, oracle and lotus notes db. Is there a way to grab dates
using sql? So far I'm not having any luck either.

Delimit date values with # character, and be sure to use mm/dd/yyyy format:

SELECT * FROM Tablename
WHERE OrdDate = #03/28/2006#;

--

Ken Snell
<MS ACCESS MVP>

There is a date field I need to search on with normal date parameter
syntax.
I am stripping out the time value by creating this expression in a new
field:
OrdDate: DateValue([OrderDate]). Problem is, this new field is not
letting
me search like a date field. To bring up a date, I have to search like
this:
Like "*3/27/2006*".

Is there a way to make this field search like a date? The underlying
field,
BTW, is in Date/Time format. And, I've set the expression's field
properties
to be "Short Date".

 

[Ken Snell \(MVP\)]

"Grabbing" dates shouldn't be difficult.... but show us what you've tried,
and give us some more details about your setup and use.

--

Ken Snell
<MS ACCESS MVP>

I'm having the same problem but I'm grabbing data based on date from other
sources such as db2, oracle and lotus notes db. Is there a way to grab
dates
using sql? So far I'm not having any luck either.

Delimit date values with # character, and be sure to use mm/dd/yyyy
format:

SELECT * FROM Tablename
WHERE OrdDate = #03/28/2006#;

--

Ken Snell
<MS ACCESS MVP>

There is a date field I need to search on with normal date parameter
syntax.
I am stripping out the time value by creating this expression in a new
field:
OrdDate: DateValue([OrderDate]). Problem is, this new field is not
letting
me search like a date field. To bring up a date, I have to search like
this:
Like "*3/27/2006*".

Is there a way to make this field search like a date? The underlying
field,
BTW, is in Date/Time format. And, I've set the expression's field
properties
to be "Short Date".

 

[Guest]

OK, thanks.

Sorry Papperback if I’m taking over. Didn’t mean too. I though this was a
related question. Again sorry.

Ken,
Here’s what I’m pulling from a linked (via ODBC) Oracle database. In this
example I’m doing the simplest thing. I have also tried the following.

Note: If it helps any I’ve also uploaded the Oracle table I’m linked with
and found that the date format was text.

1. format([date created],"mm/dd/yyyy")
2. CDate([date created])
3. like #01/01/2006#
4. Between DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date()),0)

Query:
SELECT TEC_TEC_T_EVT_REP.CLASS, TEC_TEC_T_EVT_REP.LAST_MODIFIED_TIME,
TEC_TEC_T_EVT_REP.SOURCE, TEC_TEC_T_EVT_REP.ORIGIN,
TEC_TEC_T_EVT_REP.HOSTNAME, TEC_TEC_T_EVT_REP.ADAPTER_HOST,
TEC_TEC_T_EVT_REP.ADMINISTRATOR, TEC_TEC_T_EVT_REP.DATE_EVENT,
TEC_TEC_T_EVT_REP.DURATION, TEC_TEC_T_EVT_REP.MSG
FROM TEC_TEC_T_EVT_REP
WHERE (((TEC_TEC_T_EVT_REP.DATE_EVENT)>Date()-"30"));

Results: Over 2,000 records with varying dates from 2005 to present.
Class = PATROL_SWIFT_FTABACK_FILE_CHECK
Last Modified = 2/2/2005 8:52:37 PM
Source = PATROL
Origin = 66.94.234.13
HostName = yahoo-dr
Adapter_host = socp055
Administrator = first_rule.rls
Date_Event = Feb 2 21:05:45 2005
Duration = 0
MSG = Please investigate…

"Grabbing" dates shouldn't be difficult.... but show us what you've tried,
and give us some more details about your setup and use.

--

Ken Snell
<MS ACCESS MVP>

I'm having the same problem but I'm grabbing data based on date from other
sources such as db2, oracle and lotus notes db. Is there a way to grab
dates
using sql? So far I'm not having any luck either.

Delimit date values with # character, and be sure to use mm/dd/yyyy
format:

SELECT * FROM Tablename
WHERE OrdDate = #03/28/2006#;

--

Ken Snell
<MS ACCESS MVP>

message There is a date field I need to search on with normal date parameter
syntax.
I am stripping out the time value by creating this expression in a new
field:
OrdDate: DateValue([OrderDate]). Problem is, this new field is not
letting
me search like a date field. To bring up a date, I have to search like
this:
Like "*3/27/2006*".

Is there a way to make this field search like a date? The underlying
field,
BTW, is in Date/Time format. And, I've set the expression's field
properties
to be "Short Date".

 

[Ken Snell \(MVP\)]

The date field in the ORACLE table is text type? Is
"TEC_TEC_T_EVT_REP.DATE_EVENT" the name of the date field that is in text
format? You show a "date created" field name in your example, so I'm not
sure if the problem is that the WHERE criterion is not working as you wish,
or if the values being returned for a date field in the query's SELECT
clause are not right?

Assuming that the WHERE clause is the problem (and let me admit up front
that I have no experience with Oracle or ODBC in this type of setup), does
this query SQL statement work for you?

SELECT TEC_TEC_T_EVT_REP.CLASS, TEC_TEC_T_EVT_REP.LAST_MODIFIED_TIME,
TEC_TEC_T_EVT_REP.SOURCE, TEC_TEC_T_EVT_REP.ORIGIN,
TEC_TEC_T_EVT_REP.HOSTNAME, TEC_TEC_T_EVT_REP.ADAPTER_HOST,
TEC_TEC_T_EVT_REP.ADMINISTRATOR, TEC_TEC_T_EVT_REP.DATE_EVENT,
TEC_TEC_T_EVT_REP.DURATION, TEC_TEC_T_EVT_REP.MSG
FROM TEC_TEC_T_EVT_REP
WHERE CDate(TEC_TEC_T_EVT_REP.DATE_EVENT)>(Date()-30);


or this one:

SELECT TEC_TEC_T_EVT_REP.CLASS, TEC_TEC_T_EVT_REP.LAST_MODIFIED_TIME,
TEC_TEC_T_EVT_REP.SOURCE, TEC_TEC_T_EVT_REP.ORIGIN,
TEC_TEC_T_EVT_REP.HOSTNAME, TEC_TEC_T_EVT_REP.ADAPTER_HOST,
TEC_TEC_T_EVT_REP.ADMINISTRATOR, TEC_TEC_T_EVT_REP.DATE_EVENT,
TEC_TEC_T_EVT_REP.DURATION, TEC_TEC_T_EVT_REP.MSG
FROM TEC_TEC_T_EVT_REP
WHERE DateDiff("d", CDate(TEC_TEC_T_EVT_REP.DATE_EVENT), Date()) < 30;


--

Ken Snell
<MS ACCESS MVP>

OK, thanks.

Sorry Papperback if I'm taking over. Didn't mean too. I though this was a
related question. Again sorry.

Ken,
Here's what I'm pulling from a linked (via ODBC) Oracle database. In this
example I'm doing the simplest thing. I have also tried the following.

Note: If it helps any I've also uploaded the Oracle table I'm linked with
and found that the date format was text.

1. format([date created],"mm/dd/yyyy")
2. CDate([date created])
3. like #01/01/2006#
4. Between DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date()),0)

Query:
SELECT TEC_TEC_T_EVT_REP.CLASS, TEC_TEC_T_EVT_REP.LAST_MODIFIED_TIME,
TEC_TEC_T_EVT_REP.SOURCE, TEC_TEC_T_EVT_REP.ORIGIN,
TEC_TEC_T_EVT_REP.HOSTNAME, TEC_TEC_T_EVT_REP.ADAPTER_HOST,
TEC_TEC_T_EVT_REP.ADMINISTRATOR, TEC_TEC_T_EVT_REP.DATE_EVENT,
TEC_TEC_T_EVT_REP.DURATION, TEC_TEC_T_EVT_REP.MSG
FROM TEC_TEC_T_EVT_REP
WHERE (((TEC_TEC_T_EVT_REP.DATE_EVENT)>Date()-"30"));

Results: Over 2,000 records with varying dates from 2005 to present.
Class = PATROL_SWIFT_FTABACK_FILE_CHECK
Last Modified = 2/2/2005 8:52:37 PM
Source = PATROL
Origin = 66.94.234.13
HostName = yahoo-dr
Adapter_host = socp055
Administrator = first_rule.rls
Date_Event = Feb 2 21:05:45 2005
Duration = 0
MSG = Please investigate.

"Grabbing" dates shouldn't be difficult.... but show us what you've
tried,
and give us some more details about your setup and use.

--

Ken Snell
<MS ACCESS MVP>

I'm having the same problem but I'm grabbing data based on date from
other
sources such as db2, oracle and lotus notes db. Is there a way to grab
dates
using sql? So far I'm not having any luck either.

:

Delimit date values with # character, and be sure to use mm/dd/yyyy
format:

SELECT * FROM Tablename
WHERE OrdDate = #03/28/2006#;

--

Ken Snell
<MS ACCESS MVP>

in
message There is a date field I need to search on with normal date parameter
syntax.
I am stripping out the time value by creating this expression in a
new
field:
OrdDate: DateValue([OrderDate]). Problem is, this new field is not
letting
me search like a date field. To bring up a date, I have to search
like
this:
Like "*3/27/2006*".

Is there a way to make this field search like a date? The
underlying
field,
BTW, is in Date/Time format. And, I've set the expression's field
properties
to be "Short Date".