One possibility comes to mind:
Are you seeing the latest date in MS Access but not seeing it via ODBC in Filemaker?
Perhaps the record in MS Access has not yet been committed. If so, then committing the record in access should make it accessible via ODBC in Filemaker.
Not sure PhilModJunk, the access database is in a third party software suite for the test set, so there is no real control. I am just accessing the table directly from the directory without going through access. The access tables are in order and the newest date is on the same table. the SQL statement in the import is ordered by date so it should pick up the last one.
thanks anyway PhilModJunk, I will try and get in contact with the test set manufacturer to see if he has an answer.
I appreciate your time, thanks.
What is your SQL statement?
Thanks Mr. Vodka.
I have not yet had a reply from the Test set manufacturer, (AVO PAT4 Megger)
My SQL statement reads:
SELECT 'Results','ApplianceId','Results','TestDate' from 'Results'
WHERE 'Results','TestDate',IS NOT NULL.
ORDER BY 'Results','TestDate';update matching.
I have not sorted it in descending order as that gives me the oldest date, so I believe the table is already sorted in descending order.
When we download the test set, the dates populate this 'Results' table and I can see it in the table with the same 'ApplianceID' but it still gets the date previous.
Well I tested a similar setup with Access and I dont see an issue. It works for me as it should.
My SQL statement was:
SELECT `ID`, `Results`,`TestDate`, `fkApplianceID`
WHERE `Results` IS NOT NULL AND `TestDate` IS NOT NULL
ORDER BY `Results`,`TestDate`
The import options in FileMaker were set to:
Update matching record in found set with fkApplianceID as the match base field.
Yes, I agree, I have just tried it on a different access application and it worked fine. I think my issue is that the table is on an older version of access. I am reinstalling the test set software and starting from scratch, thank you for your help.
I finally got around the issue. I had no joy from the test set manufacturer so I did it the long way.
I created a new table in filemaker and imported all the records unsorted from the access table. I then let filemaker do all the sorting and hey presto it gives me the correct date every time.
Thank you everyone for your efforts:smileyvery-happy: