Query based on import date

[Guest]

I have an Access database to which information is imported via Word userforms
I programmed. Once a week I create a report based on a query that pulls all
of the records with creation dates (of the userforms) for that week.
Unfortunately, some users do not submit these forms to Access until after the
weekly report is generated. So, what I need, if it's possible, is a query
that pulls all records *imported* into Access between certain dates, so the
report is not missing any records. Is that possible? If so, how do I build
the query, since the info is not pulled from an actual field in Access?

Thanks for any info.

 

[Guest]

since the info is not pulled from an actual field in Access
Where is the data pulled from?

 

[Guest]

Where is the data pulled from?

All I want to do, for example, is run a report that shows all of the records
added to the database last week regardless of the userform's creation date.

Claudia

 

[Guest]

Does the records have a date field that you load when they are added?

 

[Guest]

Does the records have a date field that you load when they are added?

Yes. It's from the CREATEDATE field on the Word userform that reflects the
day the userform was *created*. I already have a query built to extract
records using that date field. This would be fine, but sometimes a user
creates the userform but doesn't submit it to Access until a week later. So,
if my query says to get records for 5/8/06 - 5/12/06, and a user submitted
his form dated 5/4/06 during that week, that record will never be included in
the report because it's dated previous to the dates of the query.

Is there no way to simply build a query that extracts records based on when
they were imported into the database? If not, I guess I can probably rig
something into my Word VBA code to send into the database. But, I was hoping
there would be something on the Access end that would be simpler.

 

[John Spencer]

Add a datetime field to the table - CreatedOn.
Set its default value to Date()

When a record is created, the CreatedOn will automatomatically be assigned
the current date.

 

[Guest]

See John's answer.

Yes. It's from the CREATEDATE field on the Word userform that reflects the
day the userform was *created*. I already have a query built to extract
records using that date field. This would be fine, but sometimes a user
creates the userform but doesn't submit it to Access until a week later. So,
if my query says to get records for 5/8/06 - 5/12/06, and a user submitted
his form dated 5/4/06 during that week, that record will never be included in
the report because it's dated previous to the dates of the query.

Is there no way to simply build a query that extracts records based on when
they were imported into the database? If not, I guess I can probably rig
something into my Word VBA code to send into the database. But, I was hoping
there would be something on the Access end that would be simpler.

 

[Guest]

Add a datetime field to the table - CreatedOn.
Set its default value to Date()

When a record is created, the CreatedOn will automatomatically be assigned
the current date.

Thanks, John (and Karl). But, any idea why I'm getting a syntax error? I did
just as you instructed and got this error when I attempted to save before
exiting out of Design mode: "Compile error - in table-level validation
expression." I know this should work as I tried it on a dummy database with
no problems. I have no other problems with the database and can save it as
long as I don't add that default value.

Claudia

 

[John Spencer]

Do you have a table-level validation rule?
Open the table in design view and Select View Properties

Other than that I would guess the message is a bogus message - by that I
mean that there is a problem, but the error is not really a table-level
validation error. Perhaps you have some corruptiion in the table or some
other problem in the table.

 

[Guest]

I finally solved this problem and am reporting it for posterity. ;-) Turns
out I had a module with some unfinished code. I deleted the module which did
the trick. I was then able to add my Date() default value and my new records
reflect the date they were added, as I intended. Thanks for your assistance -
your suggestion that the table might be corrupt led me to examine all of my
objects.

Claudia

 

[rezanov]

Claudia.

It's apparently not related to the topic ... but unfortunately that
post was too old

I was just browsing google.groups trying to find the solution for exact
same proble that you guys discussed last year in a tread "Windows 2003
Activation Key"
Unfortunately I couldn't find how you actually ended up fixing this
issue...
So I just wanted to find out what was that you ended up doing to get it
to work

Thank you very much,
Val R

 

[Val]

Claudia.

It's apparently not related to the topic ... but unfortunately that
post where you discussed issue in my question was too old

I was just browsing google.groups trying to find the solution for exact
same proble that you guys discussed last year in a tread "Windows 2003
Activation Key"
Unfortunately I couldn't find how you actually ended up fixing this
issue...
So I just wanted to find out what was that you ended up doing to get it
to work.

Thank you very much,
Val R