Using table property in query

sumdumgai

Registered User.
Local time
Today, 18:42
Joined
Jul 19, 2007
Messages
453
Is it possible to use a table's property (e.g., Created Date) within a query. I would like to use that date to format some form fields. For example, if the Created Date is 8/15/12, then I would set a form's field to 'Aug 12'. Thanks.
 
Does the field you are adding to the form contain a date value? Just click on that control and change the Format property to mmm/yyyy
 
No, there are no date values in any of the fields. The data is downloaded monthly and has no reference to its timeliness. The only thing that I know about the table regarding time is when it was created (i.e. downloaded).
 
So are you downloading say a csv file from a site which you are then importing into a db?
Or is this a linked table from another data source?
 
You could use some vba


Sub GetDateLastModified()

Dim oFS As Object
Dim strFilename As String

'Put your filename here
strFilename = "c:\temp\find files.xls"

'This creates an instance of the MS Scripting Runtime FileSystemObject class
Set oFS = CreateObject("Scripting.FileSystemObject")

MsgBox strFilename & " was last modified on " & oFS.GetFile(strFilename).Datelastmodified

Set oFS = Nothing

End Sub

Add that to a field in the table when importing etc
 
No, I'm familiar with .Datelastmodified approach. I want to create a query using information in the table (an Excel table that is exported from a site that is then imported into the database, creating a new table) that is available. The 'Created Date' attribute for this newly created table is what I need. If VBA is required, I guess that would work, but it has to be used against the table, not the imported Excel file.
 
When you import the file into a table in the database, you could add a field to the Table
eg ImportedOn Date

Run a query to populate that field with Now or Date your choice, as part of the import process, or a separate process immediately after the import.
 
Yeah just create an extra field (date created) in that table and set its default property as Now() so any records created will have it's created date attached, then add that field as a form header/footer and format as mmm yyyy and it'll take it from the query which you could filter with specific date for that form.
 
As per your OP, here's the query for getting the created date:
Code:
SELECT [Name], DateCreate
FROM MSysObjects
WHERE ((([Type])=1) AND ((Left([Name],4))<>"MSys"));
 
vbaInet's approach will work if you delete the Access table into which you import Excel data every time you run the import. That is, you create a new table for each import.

However, if you create your "ImportToTable" in April and import (with an ImportedOnDate ) and continue to Add newly imported data, your records are retrievable by ImportedOnDate.
Also, if you Delete records from the Table after the import and use, the table still exists, and still has a Create Date of April.

Much of this is academic and depends on your process. Do you want the ImportedOnDate, or Table Created date ?? Both will work, as long as your process has some discipline.
 
Thanks to everyone for helping. vbaInet's suggestion works and is what I needed.
 
Have a follow on question to this topic if I may. When I specify the full table name (of the table that's created each month with imported data) in the query that vbaInet suggested, I get one record back that gives me the 'Created Date'. The field name is 'DateCreate' and the first record contains a date value such as '8/15/12 9:41:42 AM'. How can I use this record to modify the results of another query such that a field name (e.g. '1st Month') in those results is renamed 'Aug-12' (corresponding to the 'Created Date')?
Thanks.
 
You need a couple of things, DLookup(), Format() and an UPDATE query.

* Create an UPDATE query based on your table that you wish to modify
* Use the DLookup() function to return the date. First of all test it out and ensure it returns the right data and once you're satisfied with the results, put it in the Update To part
* Use the Format() function to format the date returned.
 
Create and Modified dates are NOT reliable. Alex almost gave you the correct solution. The column should default to Date() rather than Now() unless you really want a time component.
 
Create and Modified dates are NOT reliable. Alex almost gave you the correct solution. The column should default to Date() rather than Now() unless you really want a time component.
I've heard about the problems with the Modified Date in the sys object but I don't know of any issues with the Create Date. What's wrong with it?
 
If you import objects to a new database (in an effort to get rid of corruption for example), its create date will be changed to the current date and so the original import date would be lost.

The OP is using Access as if it were Excel and that leads to problems.
 
Alright, from that perspective. I see what you mean Pat.
 

Users who are viewing this thread

Back
Top Bottom