Can Access report the date data was imported, as a static date?

[Axsom]

I receive excel files from multiple sources each month. Mainly it covers the same items, and I have to look for changes, i.e. have items been added or removed?


I want to set Access to import from a form button, appending to the existing table. To separate by month, as is the request, can an Assess query find the date data was added, say with the date() function, then set that date as a static number that never changes?

I was doing this manually with excel, but I was separating everything out using a folder structure and appending the folder name (Jan, Feb, ext.), using a Data Validation selection.

I am pretty new to Access, so I am sure there is a simple solution.

[Minty]

In your access import table have a new field called ImportDate and set it's default value to either Date() or Now() if you want the time and date.
Any new records will have the current date added automatically.

If I've grabbed the wrong end of the stick let us know where I have misunderstood your requirement.

[Axsom]

In your access import table have a new field called ImportDate and set it's default value to either Date() or Now() if you want the time and date.
Any new records will have the current date added automatically.

If I've grabbed the wrong end of the stick let us know where I have misunderstood your requirement.

ok, but what happens when I import next month's data, won't last month's date just update to this month's? That would defeat the purpose. When I import this month, I need to set the date of import, then next month when appending the new data to the table, I need last month's date to stay last month's, with the new data getting this month's data.
I thought about doing separate tables, but wouldn't the same thing happen when running a comparison query, the Date() or Now() would update to the date the query ran and not the date of import. - right???
Again, I am new to working with Access, but the functions work the same as in Excel - right - which I have much more experience in...
you know how, in Excel, you can copy a cell that shows the value of a formula in it, then when you copy and 'paste value'? You get the value in the cell, not the formula returning a value... I need to do something like that.

[Minty]

No a default value only effects new records. It has no effect on existing records.
If you don't update the import date field by some other method it remains static for as long as it isn't touched.

You can prove this by adding a new field to an existing dummy table and setting the default value before saving the design changes.
Once saved then look at new field in the table data. The existing records shouldn't have any values in that field.

[Axsom]

Well, I'm going to chalk this up to my inexperience...lol
I scoured through the syntax of all the functions in the date/time category, before finding this forum (the MS Access forum, posted 24hrs before this one, still has no results-fyi). So this has just become an excellent resource...lol

Thanks for your speedy reply, and help - I will mark it as solved