Hi,
I'm new on this forum, thanks in advance for looking...

I have an Access database table with rows I'm trying to
transform and add into an existing SQLServer table. The
tables both have a 'datetime' column and that is my problem.

In the DTS Import Wizard, when I choose "all rows" in the
query builder phase, I get a good transfer of rows. But when
I choose criteria to get a "WHERE..." statement, using the
Access table's 'CurrentEndDate' column to choose criteria value,
it looks like this:

WHERE [accesstable].'CurrentEndDate'>='05/06/2005'

...and I get the error message stating that I have a datatype
mismatch in criteria expression. If I use other operands such
as '>', '<', etc. I still get the same error.

I have two questions about this:
1. My premise on this is that since the rows transfer when I don't
put in the "WHERE..." statement, the problem is not with the data
but in the syntax that Import Wizard uses to build the "WHERE..."
clause. Is this correct, and if so, what can I do to repair the statement?
2. What I'd REALLY like to do is have a WHERE statement that
pulls the rows added since the last time the package was run,
if anyone has a suggestion on how to do that?

Thx much,

Reid C.

Reid C.

__________________
Reid C.

In the WHERE CLAUSE do not put single quotes around the field name 'CurrentEndDate'. Use CurrentEndDate or [CurrentEndDate].

Access may also require # instead of ' as a field delimiter for datetime data.




Rand

Thanks much for your help...choice #3 worked fine...

WHERE [accesstable].[CurrentEndDate] >= #05/06/2005#

this format was what was needed.

Can you help me with my second issue which is to, instead of having to put in a date, write a WHERE clause that pulls rows created since the last run of the DTS package?

Thx again!

Reid C.

That is an idiosyncrasy of Access data. Access uses the # as a datetime field delimiter. I recalled that from my Access database work, before I started using SQL Server.

As for the other issue. I don't have a quick answer since I don't use DTS very often.

Rand