Pass-Through Query Headaches

[Galen]

I have a date/time called REVDATTIM stored on a remote
table in the following format 7/15/2004 8:05:09 AM.

I have created a pass-through query in access that
retrives several fields plus this date/time field.

I need to restrict the records the pass-though query
returns based upon this date/time field.

I have tried every syntax combination I can think of with
no result. Here is what I have tried:

SELECT FIELD1, FIELD2, REVDATTIM
WHERE <my other criteria here works fine until I add one
of the date criteria below> AND

REVDATTIME > #8/1/04#

REVDATTIME > '#8/1/04#'

REVDATTIME > '8/1/04'

REVDATTIME > '8/1/2004 12:00:00 AM'

REVDATTIME > '#8/1/2004 12:00:00 AM#'

REVDATTIME > #8/1/2004 12:00:00 AM#

REVDATTIME > [#8/1/2004 12:00:00 AM#]
;

I always get an error either "The string representation of
datetime is incorrect or "the numeric literal "04#" is not
valid"

I am stumped... any ideas?

 

[Duane Hookom]

You didn't suggest what database type. Is this a SQL Server backend? If so,
then I would use:
REVDATTIME > '8/1/2004'

 

[Guest]

The tables appear to be IBM DB2 tables. The formula you
suggested did not work. It returned and error stating
that "REVDATTIM is not valid in the context where it is
used.

-----Original Message-----
You didn't suggest what database type. Is this a SQL Server backend? If so,
then I would use:
REVDATTIME > '8/1/2004'


--
Duane Hookom
MS Access MVP
--

I have a date/time called REVDATTIM stored on a remote
table in the following format 7/15/2004 8:05:09 AM.

I have created a pass-through query in access that
retrives several fields plus this date/time field.

I need to restrict the records the pass-though query
returns based upon this date/time field.

I have tried every syntax combination I can think of with
no result. Here is what I have tried:

SELECT FIELD1, FIELD2, REVDATTIM
WHERE <my other criteria here works fine until I add one
of the date criteria below> AND

REVDATTIME > #8/1/04#

REVDATTIME > '#8/1/04#'

REVDATTIME > '8/1/04'

REVDATTIME > '8/1/2004 12:00:00 AM'

REVDATTIME > '#8/1/2004 12:00:00 AM#'

REVDATTIME > #8/1/2004 12:00:00 AM#

REVDATTIME > [#8/1/2004 12:00:00 AM#]
;

I always get an error either "The string representation of
datetime is incorrect or "the numeric literal "04#" is not
valid"

I am stumped... any ideas?

.

 

[Van T. Dinh]

You need to identify the Back-End exactly and use the correct SQL syntax for
the server with with Date/Time, they all seem to have different
implementation.

--
HTH
Van T. Dinh
MVP (Access)

The tables appear to be IBM DB2 tables. The formula you
suggested did not work. It returned and error stating
that "REVDATTIM is not valid in the context where it is
used.

-----Original Message-----
You didn't suggest what database type. Is this a SQL Server backend? If so,
then I would use:
REVDATTIME > '8/1/2004'


--
Duane Hookom
MS Access MVP
--

I have a date/time called REVDATTIM stored on a remote
table in the following format 7/15/2004 8:05:09 AM.

I have created a pass-through query in access that
retrives several fields plus this date/time field.

I need to restrict the records the pass-though query
returns based upon this date/time field.

I have tried every syntax combination I can think of with
no result. Here is what I have tried:

SELECT FIELD1, FIELD2, REVDATTIM
WHERE <my other criteria here works fine until I add one
of the date criteria below> AND

REVDATTIME > #8/1/04#

REVDATTIME > '#8/1/04#'

REVDATTIME > '8/1/04'

REVDATTIME > '8/1/2004 12:00:00 AM'

REVDATTIME > '#8/1/2004 12:00:00 AM#'

REVDATTIME > #8/1/2004 12:00:00 AM#

REVDATTIME > [#8/1/2004 12:00:00 AM#]
;

I always get an error either "The string representation of
datetime is incorrect or "the numeric literal "04#" is not
valid"

I am stumped... any ideas?

.

 

[Duane Hookom]

I did a search on google and found a date expression like
'1990-02-22-24.00.00'

--
Duane Hookom
MS Access MVP

The tables appear to be IBM DB2 tables. The formula you
suggested did not work. It returned and error stating
that "REVDATTIM is not valid in the context where it is
used.

-----Original Message-----
You didn't suggest what database type. Is this a SQL Server backend? If so,
then I would use:
REVDATTIME > '8/1/2004'


--
Duane Hookom
MS Access MVP
--

I have a date/time called REVDATTIM stored on a remote
table in the following format 7/15/2004 8:05:09 AM.

I have created a pass-through query in access that
retrives several fields plus this date/time field.

I need to restrict the records the pass-though query
returns based upon this date/time field.

I have tried every syntax combination I can think of with
no result. Here is what I have tried:

SELECT FIELD1, FIELD2, REVDATTIM
WHERE <my other criteria here works fine until I add one
of the date criteria below> AND

REVDATTIME > #8/1/04#

REVDATTIME > '#8/1/04#'

REVDATTIME > '8/1/04'

REVDATTIME > '8/1/2004 12:00:00 AM'

REVDATTIME > '#8/1/2004 12:00:00 AM#'

REVDATTIME > #8/1/2004 12:00:00 AM#

REVDATTIME > [#8/1/2004 12:00:00 AM#]
;

I always get an error either "The string representation of
datetime is incorrect or "the numeric literal "04#" is not
valid"

I am stumped... any ideas?

.