MS Query choking on date field

[sowley]

Hi, I'm a newbie with a full-blown Server 8 Advanced setup to manage.

I have a simple database on the server, and to produce reports I want to pull the data out of it and into Excel using the ODBC driver and MS Query (Office 2000 version). Everything works fine, except that MS Query kicks up an error on the FM database's date field.

The date field is set to be strict about the format, and to enforce a 4-digit year, so they all look like this: 6/8/2006

Has anyone else run into this? I have not found this issue on the forum, so forgive me please if the question has been answered.

I need to use Excel for this, as we do not want the FM client proliferating around.

[Wim Decorte]

Just tried this in a test file with 5000 date records. This query:

SELECT "0106".someDate

FROM "0106" "0106"

ORDER BY "0106".someDate

promptly returned all dates in Excel (2003).

Edited July 7, 2006 by Guest

[sowley]

Thanks!

I will try writing the query by hand, and see if MS Query responds correctly in that case.

This is the second database where the date field is a problem, however, and I just do not see where the issue is. The data is quite consistent.

I may have to try a 2003 install, and see if it goes away then.

Edited July 10, 2006 by Guest

[sowley]

An upgrade to Office 2003 does not fix the issue.

I'm using MS Query and the ODBC driver that comes with Filemaker, and any search that involves date or timestamp fields kicks up a Parse Error.

This is a from-scratch database--so it should not have any errors in it. Other databases in FM8 show similar problems.

Any other ideas? I'm getting a little worried that FM8 is not going to work out.

[sowley]

It would appear that the problem is the driver. I switched to OpenOffice 2 in order to get away from the MS stuff, and a query there returns the records, BUT it also returns about 34 pages of spurious records with dates of 1/03/1. I expect that this is what MS Query was choking on.

Any ideas what I am doing wrong?

Thanks!

[sowley]

Well, it's fixed. I renamed the DATE fields and the problem just went away. I see that the 8.0v4 version of Filemaker Server has a fix for this problem, so I will be plugging that update in ASAP.

How anybody got this to work without the fix is beyond me.