Querying a date

[Holywhippet]

This is probably fairly simple but I'm used to Oracle databases and I'm not sure how to do this for Access type databases. I'm trying to build a query to find a specific row. One of the fields is of type date. How do I set this up in the SQL query.

For Oracle I would have used something like this:

select * from table where date_column = to_date('1-11-2004','DD-MM-YYYY')

I tried this:

select * from table where date_column = '1-11-2004'

and it wasn't happy with the date being a string so I tried this:

select * from table where date_column = 1-11-2004

it didn't complain but it didn't work either.

[erickwidya]

what do mean it wasn't happy?

try this one

select * from table where date_column = #1-11-2004#

PS : don't forget to format the date to appropriate format

[Holywhippet]

Hmm, that didn't help either although it doesn't give me an error message.

What I mean by not being happy is that it gave me this error message: "Data type mismatch in criteria expression."

[erickwidya]

hm..u have a table call Table? if it is..try use [] bracket..
maybe is a reserverd word

u date_column type is Date/Time right?

[Holywhippet]

hm..u have a table call Table? if it is..try use [] bracket..
maybe is a reserverd word

u date_column type is Date/Time right?

The column type is Date. The way I'm accessing it is using the API for ESRI ArcMap. In it you set up a QueryFilter and then set the Where clause for it. You can then apply the filter into one of the functions associated with the table object - you can count the number of rows that matches the query or delete rows etc.

[erickwidya]

..The way I'm accessing it is using the API for ESRI ArcMap.

never used it before..but i can get it to work with the previous one..

In it you set up a QueryFilter and then set the Where clause for it.

not following u here..

[Holywhippet]

Here's a code snippet showing how ArcMap handles queries:

Dim existingRowFilter As IQueryFilter
existingRowFilter = New QueryFilter

existingRowFilter.WhereClause = myHub.currentRelatedTableFK(counter) & " = " & idVal

existingRowFilter.WhereClause += " AND " & myHub.relatedRows(counter).Fields.Field(counter2).Name & " = #" & reCodedValue & "#"

Dim temp As Integer = targetTable.RowCount(existingRowFilter) targetTable.DeleteSearchedRows(existingRowFilter)

The IQueryFilter class stores queries that can be run against a table. You set the where clause and then feed it into one of the functions for the class representing the table, in this case targetTable.

[erickwidya]

can u just use the query without ArcMap Class to see what happen?

maybe the Class not use '#' for Date data type or maybe it need to change the format maybe yy, mm, dd like in Cystal Reports..but i'm not sure since never try it before..