There is an old 3rd party software we have that we use to track calls.

in one of the forms, there are some dropdown lists, checkboxes and date fields. I used Access to retrieve the table containing the info inthose calls and I could find the drop down lists and the checkboxes of that form. For some reason, I couldn't find any date fields at all on the tables I linked to that database. (after searching the most relevant tables (they were not named intuitively at all).

I was wondering if there is a way to search all date fields from various tables. If yes, I'll link all the tables I can, do that search and see which date field corresponds to the ones I need.

Thanks

If this is a one-time task, and if you don't have (literally) hundreds of tables, you could use the built-in documenter to generate a detail view of each table definition. You could find "Date/Time" fields there.

Depending on the version of Access, open the database files with linked tables. Go to Tools, Analyze, Documenter. You can search the tables there and see all the data types of the fields.

But don't be surprised to find there aren't any date fields. They might have put the data into text fields and used things like CDate to convert the text to dates. It's a bad way to design a database, but I've seen it too often.

Hi,

If you cannot find it manually, perhaps the 3rd party software may not be storing the dates as a Date/Time data type.

Just my 2 cents...


EDIT: Guess I'm starting out a little slow this morning... Need more (IMG:style_emoticons/default/coffee1.gif)

This post has been edited by theDBguy: Sep 16 2011, 09:59 AM

I thought about the date fields / text field issue too,

but in the same table where I can find the checkboxes and the other data, none had anything that even looked like dates...

There are a lot of tables, so if they really are text fields, it;s going to be really hard...

Edit: Yes, you are right, their dates are stored as text...!!

This post has been edited by Sako: Sep 16 2011, 10:17 AM

Nice catch!

You can use a query to convert (some) text-type pseudo-dates into real Date/Time values.

Well, it's not really a catch since I still haven't found in which table the data is stored in.

All I know is that all dates in that database are of text datatype!

Here's how to find out where your linked tables go:

SELECT MSysObjects.ForeignName, MSysObjects.Database, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=6));

However that doesn't tell you which table inside the file has your data. For that bring up Linked Table Manager and run it. The string should show the database name followed by the table inside of it. Something like this for the Programs table inside the COINS database.:

Programs (C:\Users\COINS.mdb\Programs)

You can bring up Linked Table Manager by going to Tools, Database Utilities OR right clicking on a linked table in the database window or navigation pane.