Where DateTime Field >= '2008-03-22'

  • Thread starter Thread starter Carroll
  • Start date Start date
C

Carroll

In Design View for ACCESS 2003, how do I select records where a
DateTime formatted field is >= '2008-03-22'?

The field name is INSRT_TS in table QDDWCHAR, database MEDIB. Any
ideas?
 
Carroll

? ... in Design View ...? Design of what?

Are you trying to use a query to find records with a date/time datatype
field value greater than or equal to March 22, 2008?

If so, one way would be to open the query in design view, select the table,
select the field(s) of interest, and enter:
=3/22/2008
Click to expand...
in the selection criterion "cell". Note that Access will convert this to:
=#3/22/2008#
Click to expand...
(which you can alse enter if you feel like doing the extra typing).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Carroll

? ... in Design View ...?  Design of what?

Are you trying to use a query to find records with a date/time datatype
field value greater than or equal to March 22, 2008?

If so, one way would be to open the query in design view, select the table,
select the field(s) of interest, and enter:
    >=3/22/2008
in the selection criterion "cell".  Note that Access will convert this to:
    >=#3/22/2008#
(which you can alse enter if you feel like doing the extra typing).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...

Jeff,

Yes, I'm querying a table, and I want all records that are equal to or
greater than the date I mentioned. I did type the date in as you
suggested, and it did convert it by putting the # symbols around the
date. But upon running the query, I get an error message: "ODBC--call
failed. [IBM][CLI Driver][DB2] SQL0180N The syntax of the string
representation of a datetime value is incorrect. SQLSTATE=22007"

Thanks!

Carroll Rinehart
 
The implication of the error message is that the connection between MS
Access and your data source has an issue.

What are you using for a back-end data source?

Can you open the underlying table WITHOUT the date/time restriction?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Carroll

? ... in Design View ...? Design of what?

Are you trying to use a query to find records with a date/time datatype
field value greater than or equal to March 22, 2008?

If so, one way would be to open the query in design view, select the
table,
select the field(s) of interest, and enter:
in the selection criterion "cell". Note that Access will convert this to:
(which you can alse enter if you feel like doing the extra typing).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...

Jeff,

Yes, I'm querying a table, and I want all records that are equal to or
greater than the date I mentioned. I did type the date in as you
suggested, and it did convert it by putting the # symbols around the
date. But upon running the query, I get an error message: "ODBC--call
failed. [IBM][CLI Driver][DB2] SQL0180N The syntax of the string
representation of a datetime value is incorrect. SQLSTATE=22007"

Thanks!

Carroll Rinehart
 
The implication of the error message is that the connection between MS
Access and your data source has an issue.

What are you using for a back-end data source?

Can you open the underlying table WITHOUT the date/time restriction?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Carroll
Click to expand...
? ... in Design View ...? Design of what?
Click to expand...
Are you trying to use a query to find records with a date/time datatype
field value greater than or equal to March 22, 2008?
Click to expand...
If so, one way would be to open the query in design view, select the
table,
select the field(s) of interest, and enter:
in the selection criterion "cell". Note that Access will convert this to:
(which you can alse enter if you feel like doing the extra typing).
Click to expand...
Good luck!

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...

Jeff,

Yes, I'm querying a table, and I want all records that are equal to or
greater than the date I mentioned. I did type the date in as you
suggested, and it did convert it by putting the # symbols around the
date. But upon running the query, I get an error message: "ODBC--call
failed. [IBM][CLI Driver][DB2] SQL0180N The syntax of the string
representation of a datetime value is incorrect. SQLSTATE=22007"

Thanks!

Carroll Rinehart
Click to expand...

Jeff,

Yes, if I take the restriction off, the table opens just fine. This
is a table in a DB2 database. The field is a date/time stamp for the
date that the record was inserted into the table.

Carroll
 
Carroll

Can you confirm that the DB2 field is actually a date/time field?

I've seen "dates" recorded in text/char fields so that they look like dates,
but are actually just strings.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Carroll said:
The implication of the error message is that the connection between MS
Access and your data source has an issue.

What are you using for a back-end data source?

Can you open the underlying table WITHOUT the date/time restriction?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Carroll
Click to expand...
? ... in Design View ...? Design of what?
Click to expand...
Are you trying to use a query to find records with a date/time datatype
field value greater than or equal to March 22, 2008?
Click to expand...
If so, one way would be to open the query in design view, select the
table,
select the field(s) of interest, and enter:
=3/22/2008
in the selection criterion "cell". Note that Access will convert this
to:
=#3/22/2008#
(which you can alse enter if you feel like doing the extra typing).
Click to expand...
Good luck!

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...

Jeff,

Yes, I'm querying a table, and I want all records that are equal to or
greater than the date I mentioned. I did type the date in as you
suggested, and it did convert it by putting the # symbols around the
date. But upon running the query, I get an error message: "ODBC--call
failed. [IBM][CLI Driver][DB2] SQL0180N The syntax of the string
representation of a datetime value is incorrect. SQLSTATE=22007"

Thanks!

Carroll Rinehart
Click to expand...

Jeff,

Yes, if I take the restriction off, the table opens just fine. This
is a table in a DB2 database. The field is a date/time stamp for the
date that the record was inserted into the table.

Carroll
Click to expand...
 
Yes, if I take the restriction off, the table opens just fine. This
is a table in a DB2 database. The field is a date/time stamp for the
date that the record was inserted into the table.
Click to expand...

Try

Where DateTime Field >= #2008-03-22#

# is a date/time delimiter in Access, I'm guessing that using it will coerce
the query parser to convert it to a date/time value that DB2 will recognize.
 
Try

Where DateTime Field >= #2008-03-22#

# is a date/time delimiter in Access, I'm guessing that using it will coerce
the query parser to convert it to a date/time value that DB2 will recognize.
Click to expand...

A person at work suggested I try >#3/20/2008 12:01:00 AM# and this
worked.

Thanks for all of your suggestions.

Carroll Rinehart
 
Carroll

If the value that was stored was a date AND time value, trying to compare to
a date-only value will give you ... unusual ... results.

If you only wish to compare the date portion, you can use a query and the
DateValue([YourDateField]) function to compare apples and apples.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Try

Where DateTime Field >= #2008-03-22#

# is a date/time delimiter in Access, I'm guessing that using it will
coerce
the query parser to convert it to a date/time value that DB2 will
recognize.
Click to expand...

A person at work suggested I try >#3/20/2008 12:01:00 AM# and this
worked.

Thanks for all of your suggestions.

Carroll Rinehart
 
Back
Top