Access - ODBC to SQL - Incorrect Results

[Jensen.Katie]

I have a SQL Server 2005 DB that I set up an ODBC connection to so
that people can access the data through MS Access. There are 3
tables, each of which have a date column that is a varchar (20)
because the dates do not fall within the required range by SQL
server. The dates are stored in the following format - YYYY-MM-DD.
Two of the three tables can (and do return) the correct results in
ACCESS when you search on that date field. The third table, for some
reason, isn't responding to anything I try and search on. I've
brought up the entire table and attempted to filter on a value in the
date column and it still returns multiple different values in that
column. I've attempted to convert the field to String, Date, Cdate
and many others in Access and haven't had any luck with the results
returning anything correct.

If I go back to the SQL server tables, I can search on all 3 of the
tables using ='YYYY-MM-DD' in the date field and get correct
results.

Has anyone seen anything like this? Any tips on how to fix?

[Rich P]

It sounds like you are trying to mix apples with oranges. A datefield
is not a varchar field. Even if you change the datatype to varchar and
expect to get results that you would get using dates, obviously it isn't
happening. If you change the data type back to datetime it sounds like
your system wont work.

If your system isn't working when the respective fields are in the
correct data type mode, then you need to fix the design of your system.

Trying to tweak stuff on a sql server never works. It was specifically
desinged that way to prevent the usage of the apples/oranges paradigm
because even though apples and oranges may work for the first 10 million
records, you will eventually get that 10 millionth and one record where
all of a sudden it no longer works and your entire system gets messed up
because you can't locate the offending record because your are looking
for an apple that is really an orange.

Instead, you should post what the problem is when you are using the
correct data types.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Jensen.Katie]

In SQL server I am getting the correct results for all the varchar
columns that hold dates in them. The problem is that the data in SQL
was imported from a different system that didn't do any date
verification, so the information held in those fields are not all
valid dates. That alone caused me hours of errors trying to import
the data into SQL server, forcing me to import that field as
varchar.

My question was why 3 fields defined as the same thing in SQL server
holding the same format of data are acting differently in ACCESS.

[lyle]

On Nov 19, 7:49 pm, [e-mail] wrote:

My question was why 3 fields defined as the same thing in SQL server
holding the same format of data are acting differently in ACCESS.

This is one of those questions that is very difficult to answer
because it is so general, like "Why the sea is boiling hot" and
"Whether pigs have wings?"

Questions:
1. yyyy-mm-dd is 10 characters long. Why Varchar(20)?
2. Why yyyy-mm-dd rather than yyyymmdd? "-" has significance to many
search and filter engines beyond being a hyphen. It adds zip of
significance to yyyymmdd.
3. You say the data are in SQL server. You also say the data were
imported form SQL-Server. And you say, why won't what I want to
happen, happen in Access. Where are the data? And how are you
connecting? And what does in Access mean: on a form? in a recordset?
In the returns of a query? WHAT?

It sounds to me as if you are in too deep here. If you tell us some
specifics we might be able to help. I don't use ODBC but I've been
convinced of its effectiveness. It should be able to take your Access
commands and translate them into MS SQL-Server SQL that will give you
the results you need.

BTW. X-Base programs translated dates to yyyymmdd for sorting purposes
for years and years and years with no problems. Probably they still
do. So there should be no unsolvable problem in your using this
format. But I would create another date column and cast the yyyymmdd
strings to dates and use that. Why use a 13.5 compression ratio V-12
and give it regular gas ... or sub-regular gas?