Problem with dates

[Andy]

Hi Everyone,

I am very new to this but so far have managed to create a small database
with a few tables linked together and a few queries.

I have also created some reports and forms.

Using the switch board I would like to open a form, which has been filtered
base on the following two conditions:
- Status = Open
- Trace date = Any date during this week. Basically I would like the date
range to change automatically depending on the current week. The current
week being Monday to Sunday.

I am using Access 2000.

Many thanks

Anguel.

 

[Klatuu]

dtmWeekStartDate = DateAdd("d", vbMonday - DatePart("w", Date), Date)
dtmWeekEndDate = DateAdd("d", 6, dtmWeekStartDate)

 

[Andy]

Hi There. I am not really sure how to implement this. I have tried building
an expression but it is not working.

How do I get the form to load with only the records matching the criteria
where:
(Trace Date is begging of the week) And (End of the week) and (Status=Open)

Many thanks,

Anguel

 

[Klatuu]

You can put your filtering criteria in the form's Filter property. Open the
form in design view, select properties, the Data tab, and the Filter
property. Then, you can put the criteria there:

TableName.Status = "Open" And TableName.StartDate = DateAdd("d", vbMonday -
DatePart("w", Date), Date) And TableName.EndDate =
DateAdd("d", 6, DateAdd("d", vbMonday - DatePart("w", Date), Date))

I think that will work.

 

[Andy]

Many thanks. I think we are getting there but now I am getting an error
message telling me that:

The expression On Load you have entered as the event property setting
produced the following error: The object doesn't contain the Automation
object 'dtmWeekStartDate.'.

* The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
* There may have been an error evaluating the function, event, or macro.

Any idea what this error could be?

Once again many thanks.

Anguel

 

[Klatuu]

You did not put it the code as I posted it. The code I posted had that
removed because I knew it would be a problem. Look at my previous post and
use that code for starters.

Many thanks. I think we are getting there but now I am getting an error
message telling me that:

The expression On Load you have entered as the event property setting
produced the following error: The object doesn't contain the Automation
object 'dtmWeekStartDate.'.

* The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
* There may have been an error evaluating the function, event, or macro.

Any idea what this error could be?

Once again many thanks.

Anguel

Hi Everyone,

I am very new to this but so far have managed to create a small database
with a few tables linked together and a few queries.

I have also created some reports and forms.

Using the switch board I would like to open a form, which has been
filtered base on the following two conditions:
- Status = Open
- Trace date = Any date during this week. Basically I would like the date
range to change automatically depending on the current week. The current
week being Monday to Sunday.

I am using Access 2000.

Many thanks

Anguel.

 

[Andy]

Sorry I had an expression in the builder which I have now cleared. The error
message is no longer coming but the form does not seem to be filtered. I can
still see the 3 records I have.

Any ideas?

You did not put it the code as I posted it. The code I posted had that
removed because I knew it would be a problem. Look at my previous post
and
use that code for starters.

Many thanks. I think we are getting there but now I am getting an error
message telling me that:

The expression On Load you have entered as the event property setting
produced the following error: The object doesn't contain the Automation
object 'dtmWeekStartDate.'.

* The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
* There may have been an error evaluating the function, event, or macro.

Any idea what this error could be?

Once again many thanks.

Anguel

Hi Everyone,

I am very new to this but so far have managed to create a small
database
with a few tables linked together and a few queries.

I have also created some reports and forms.

Using the switch board I would like to open a form, which has been
filtered base on the following two conditions:
- Status = Open
- Trace date = Any date during this week. Basically I would like the
date
range to change automatically depending on the current week. The
current
week being Monday to Sunday.

I am using Access 2000.

Many thanks

Anguel.

 

[Klatuu]

Can't say without it in front of me. The problem is with the filter property
that if the filter is not set correctly, you don't get an error, it just
doesn't filter.
Are the dates in your table carried as Date/Time fields?

Sorry I had an expression in the builder which I have now cleared. The error
message is no longer coming but the form does not seem to be filtered. I can
still see the 3 records I have.

Any ideas?

You did not put it the code as I posted it. The code I posted had that
removed because I knew it would be a problem. Look at my previous post
and
use that code for starters.

Many thanks. I think we are getting there but now I am getting an error
message telling me that:

The expression On Load you have entered as the event property setting
produced the following error: The object doesn't contain the Automation
object 'dtmWeekStartDate.'.

* The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
* There may have been an error evaluating the function, event, or macro.

Any idea what this error could be?

Once again many thanks.

Anguel



Hi Everyone,

I am very new to this but so far have managed to create a small
database
with a few tables linked together and a few queries.

I have also created some reports and forms.

Using the switch board I would like to open a form, which has been
filtered base on the following two conditions:
- Status = Open
- Trace date = Any date during this week. Basically I would like the
date
range to change automatically depending on the current week. The
current
week being Monday to Sunday.

I am using Access 2000.

Many thanks

Anguel.

 

[Andy]

Yes they are. I can email it to you if you don't mind looking at it?

Anguel

Can't say without it in front of me. The problem is with the filter
property
that if the filter is not set correctly, you don't get an error, it just
doesn't filter.
Are the dates in your table carried as Date/Time fields?

Sorry I had an expression in the builder which I have now cleared. The
error
message is no longer coming but the form does not seem to be filtered. I
can
still see the 3 records I have.

Any ideas?

You did not put it the code as I posted it. The code I posted had that
removed because I knew it would be a problem. Look at my previous post
and
use that code for starters.

:

Many thanks. I think we are getting there but now I am getting an
error
message telling me that:

The expression On Load you have entered as the event property setting
produced the following error: The object doesn't contain the
Automation
object 'dtmWeekStartDate.'.

* The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
* There may have been an error evaluating the function, event, or
macro.

Any idea what this error could be?

Once again many thanks.

Anguel



Hi Everyone,

I am very new to this but so far have managed to create a small
database
with a few tables linked together and a few queries.

I have also created some reports and forms.

Using the switch board I would like to open a form, which has been
filtered base on the following two conditions:
- Status = Open
- Trace date = Any date during this week. Basically I would like the
date
range to change automatically depending on the current week. The
current
week being Monday to Sunday.

I am using Access 2000.

Many thanks

Anguel.

 

[Klatuu]

I would, but I can't receive zip files or mdb files here.

Let me try to set up a similar test enviornment here. I will get back to you.

Yes they are. I can email it to you if you don't mind looking at it?

Anguel

Can't say without it in front of me. The problem is with the filter
property
that if the filter is not set correctly, you don't get an error, it just
doesn't filter.
Are the dates in your table carried as Date/Time fields?

Sorry I had an expression in the builder which I have now cleared. The
error
message is no longer coming but the form does not seem to be filtered. I
can
still see the 3 records I have.

Any ideas?



You did not put it the code as I posted it. The code I posted had that
removed because I knew it would be a problem. Look at my previous post
and
use that code for starters.

:

Many thanks. I think we are getting there but now I am getting an
error
message telling me that:

The expression On Load you have entered as the event property setting
produced the following error: The object doesn't contain the
Automation
object 'dtmWeekStartDate.'.

* The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
* There may have been an error evaluating the function, event, or
macro.

Any idea what this error could be?

Once again many thanks.

Anguel



Hi Everyone,

I am very new to this but so far have managed to create a small
database
with a few tables linked together and a few queries.

I have also created some reports and forms.

Using the switch board I would like to open a form, which has been
filtered base on the following two conditions:
- Status = Open
- Trace date = Any date during this week. Basically I would like the
date
range to change automatically depending on the current week. The
current
week being Monday to Sunday.

I am using Access 2000.

Many thanks

Anguel.

 

[Andy]

I can upload it to the company site and maybe you can download it from
there?

Anguel

I would, but I can't receive zip files or mdb files here.

Let me try to set up a similar test enviornment here. I will get back to
you.

Yes they are. I can email it to you if you don't mind looking at it?

Anguel

Can't say without it in front of me. The problem is with the filter
property
that if the filter is not set correctly, you don't get an error, it
just
doesn't filter.
Are the dates in your table carried as Date/Time fields?

:

Sorry I had an expression in the builder which I have now cleared. The
error
message is no longer coming but the form does not seem to be filtered.
I
can
still see the 3 records I have.

Any ideas?



You did not put it the code as I posted it. The code I posted had
that
removed because I knew it would be a problem. Look at my previous
post
and
use that code for starters.

:

Many thanks. I think we are getting there but now I am getting an
error
message telling me that:

The expression On Load you have entered as the event property
setting
produced the following error: The object doesn't contain the
Automation
object 'dtmWeekStartDate.'.

* The expression may not result in the name of a macro, the name of
a
user-defined function, or [Event Procedure].
* There may have been an error evaluating the function, event, or
macro.

Any idea what this error could be?

Once again many thanks.

Anguel



Hi Everyone,

I am very new to this but so far have managed to create a small
database
with a few tables linked together and a few queries.

I have also created some reports and forms.

Using the switch board I would like to open a form, which has
been
filtered base on the following two conditions:
- Status = Open
- Trace date = Any date during this week. Basically I would like
the
date
range to change automatically depending on the current week. The
current
week being Monday to Sunday.

I am using Access 2000.

Many thanks

Anguel.

 

[Andy]

Or maybe just take the file extension off?

I don't know if this is going to work though.

Anguel

I can upload it to the company site and maybe you can download it from
there?

Anguel

I would, but I can't receive zip files or mdb files here.

Let me try to set up a similar test enviornment here. I will get back to
you.

Yes they are. I can email it to you if you don't mind looking at it?

Anguel


Can't say without it in front of me. The problem is with the filter
property
that if the filter is not set correctly, you don't get an error, it
just
doesn't filter.
Are the dates in your table carried as Date/Time fields?

:

Sorry I had an expression in the builder which I have now cleared.
The
error
message is no longer coming but the form does not seem to be
filtered. I
can
still see the 3 records I have.

Any ideas?



You did not put it the code as I posted it. The code I posted had
that
removed because I knew it would be a problem. Look at my previous
post
and
use that code for starters.

:

Many thanks. I think we are getting there but now I am getting an
error
message telling me that:

The expression On Load you have entered as the event property
setting
produced the following error: The object doesn't contain the
Automation
object 'dtmWeekStartDate.'.

* The expression may not result in the name of a macro, the name
of a
user-defined function, or [Event Procedure].
* There may have been an error evaluating the function, event, or
macro.

Any idea what this error could be?

Once again many thanks.

Anguel



Hi Everyone,

I am very new to this but so far have managed to create a small
database
with a few tables linked together and a few queries.

I have also created some reports and forms.

Using the switch board I would like to open a form, which has
been
filtered base on the following two conditions:
- Status = Open
- Trace date = Any date during this week. Basically I would like
the
date
range to change automatically depending on the current week. The
current
week being Monday to Sunday.

I am using Access 2000.

Many thanks

Anguel.

 

[Eric]

You don't need to be able to receive zip files or mdb files if you can
receive email with attachments. There are other compressed formats such as
(.jar?) or you can just change the extension (simplest way if your recipient
knows how to change it back).

(of course setting up your own test DB is still better for security if you
know just how to reproduce it and it won't take too long)

 

[Klatuu]

Send me the URL to your site and I will see if I can get to it. The block a
lot of sites here.

Or maybe just take the file extension off?

I don't know if this is going to work though.

Anguel

I can upload it to the company site and maybe you can download it from
there?

Anguel

I would, but I can't receive zip files or mdb files here.

Let me try to set up a similar test enviornment here. I will get back to
you.

:

Yes they are. I can email it to you if you don't mind looking at it?

Anguel


Can't say without it in front of me. The problem is with the filter
property
that if the filter is not set correctly, you don't get an error, it
just
doesn't filter.
Are the dates in your table carried as Date/Time fields?

:

Sorry I had an expression in the builder which I have now cleared.
The
error
message is no longer coming but the form does not seem to be
filtered. I
can
still see the 3 records I have.

Any ideas?



You did not put it the code as I posted it. The code I posted had
that
removed because I knew it would be a problem. Look at my previous
post
and
use that code for starters.

:

Many thanks. I think we are getting there but now I am getting an
error
message telling me that:

The expression On Load you have entered as the event property
setting
produced the following error: The object doesn't contain the
Automation
object 'dtmWeekStartDate.'.

* The expression may not result in the name of a macro, the name
of a
user-defined function, or [Event Procedure].
* There may have been an error evaluating the function, event, or
macro.

Any idea what this error could be?

Once again many thanks.

Anguel



Hi Everyone,

I am very new to this but so far have managed to create a small
database
with a few tables linked together and a few queries.

I have also created some reports and forms.

Using the switch board I would like to open a form, which has
been
filtered base on the following two conditions:
- Status = Open
- Trace date = Any date during this week. Basically I would like
the
date
range to change automatically depending on the current week. The
current
week being Monday to Sunday.

I am using Access 2000.

Many thanks

Anguel.

 

[Andy]

I am happy to send you the database as it is empty at the moment.

I have only started to work on it but this is proving too hard to solve.

I am very grateful for your help.

Anguel

 

[Klatuu]

I could do all of that Eric, but I am in a secure location (government
contracts) and avoid publishing my E-mail address.

 

[Andy]

Hi Klatuu,

The last comment was from me and not sure how it came under Eric.

Anyway I would be grateful if you could help me solve this problem.

I have tried everything I can think of and everything you told me.

Anguel

Send me the URL to your site and I will see if I can get to it. The block
a
lot of sites here.

Or maybe just take the file extension off?

I don't know if this is going to work though.

Anguel

I can upload it to the company site and maybe you can download it from
there?

Anguel

I would, but I can't receive zip files or mdb files here.

Let me try to set up a similar test enviornment here. I will get back
to
you.

:

Yes they are. I can email it to you if you don't mind looking at it?

Anguel


Can't say without it in front of me. The problem is with the
filter
property
that if the filter is not set correctly, you don't get an error, it
just
doesn't filter.
Are the dates in your table carried as Date/Time fields?

:

Sorry I had an expression in the builder which I have now cleared.
The
error
message is no longer coming but the form does not seem to be
filtered. I
can
still see the 3 records I have.

Any ideas?



You did not put it the code as I posted it. The code I posted
had
that
removed because I knew it would be a problem. Look at my
previous
post
and
use that code for starters.

:

Many thanks. I think we are getting there but now I am getting
an
error
message telling me that:

The expression On Load you have entered as the event property
setting
produced the following error: The object doesn't contain the
Automation
object 'dtmWeekStartDate.'.

* The expression may not result in the name of a macro, the
name
of a
user-defined function, or [Event Procedure].
* There may have been an error evaluating the function, event,
or
macro.

Any idea what this error could be?

Once again many thanks.

Anguel



Hi Everyone,

I am very new to this but so far have managed to create a
small
database
with a few tables linked together and a few queries.

I have also created some reports and forms.

Using the switch board I would like to open a form, which has
been
filtered base on the following two conditions:
- Status = Open
- Trace date = Any date during this week. Basically I would
like
the
date
range to change automatically depending on the current week.
The
current
week being Monday to Sunday.

I am using Access 2000.

Many thanks

Anguel.

 

[Eric]

I wouldn't publish an email address either, but there are ways around that
as well. If you don't have email sites blocked, you could set up a
temporary email account with a site like yahoo...

government? employee? using our tax dollars?

 

[Andy]

Try this link.
www.adisolutions.co.uk/temp/maindata.zip

Thanks

Send me the URL to your site and I will see if I can get to it. The block
a
lot of sites here.

Or maybe just take the file extension off?

I don't know if this is going to work though.

Anguel

I can upload it to the company site and maybe you can download it from
there?

Anguel

I would, but I can't receive zip files or mdb files here.

Let me try to set up a similar test enviornment here. I will get back
to
you.

:

Yes they are. I can email it to you if you don't mind looking at it?

Anguel


Can't say without it in front of me. The problem is with the
filter
property
that if the filter is not set correctly, you don't get an error, it
just
doesn't filter.
Are the dates in your table carried as Date/Time fields?

:

Sorry I had an expression in the builder which I have now cleared.
The
error
message is no longer coming but the form does not seem to be
filtered. I
can
still see the 3 records I have.

Any ideas?



You did not put it the code as I posted it. The code I posted
had
that
removed because I knew it would be a problem. Look at my
previous
post
and
use that code for starters.

:

Many thanks. I think we are getting there but now I am getting
an
error
message telling me that:

The expression On Load you have entered as the event property
setting
produced the following error: The object doesn't contain the
Automation
object 'dtmWeekStartDate.'.

* The expression may not result in the name of a macro, the
name
of a
user-defined function, or [Event Procedure].
* There may have been an error evaluating the function, event,
or
macro.

Any idea what this error could be?

Once again many thanks.

Anguel



Hi Everyone,

I am very new to this but so far have managed to create a
small
database
with a few tables linked together and a few queries.

I have also created some reports and forms.

Using the switch board I would like to open a form, which has
been
filtered base on the following two conditions:
- Status = Open
- Trace date = Any date during this week. Basically I would
like
the
date
range to change automatically depending on the current week.
The
current
week being Monday to Sunday.

I am using Access 2000.

Many thanks

Anguel.

 

[mnature]

government? employee? using our tax dollars?

Many computing environments have slack time (yes, even government ones), and
wouldn't you rather see such people helping out in these forums rather than
surfing ebay? You could also look at it as keeping their skills up-to-date
(I can think of a few government projects that failed because of mistakes
with simple math skills . . .).