467,887 Members | 1,594 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,887 developers. It's quick & easy.

Setting date condition

DL
Hi,
I have a table with quite a few fields wich saves al kind of activities on
has done on one day.
Most important to identify a unique record is the date and personID.
A person is only allowed to enter his activity data once per date.
Now I have to set a condition that will check that a person is not entering
his activity data twice for that day.

I already have tried a lot in macro's , conditions and VB.
Here are some examples:

IsNull DLookUp([activityTable]![datefield]; [activityTable];
[activityTable]![datefield]=[datefieldInForm])
set in a macro as a condition, so that if there is no record with the
current date it will give Null and thus make the expression True and
excecuting my following command.

or

Set dbs = CurrentDb
SQL_Str = "SELECT datefield FROM activityTable WHERE datefield
=[datefieldInForm]
Set t_Recordset = dbs.OpenRecordset(SQL_Str)
If t_Recordset!Code Then already_there= "yes"

If a record did exist with the given date then de IF statement should be
True and thus setting already_there to "yes".

Somehow all these thing keep on giving errors. I'm probably overlooking
something really easy but I'm just stuk on this one.

Please help....
Nov 13 '05 #1
  • viewed: 2995
Share:
1 Reply
On Wed, 30 Jun 2004 01:07:26 +0200, "DL" <da*******@hotmail.com>
wrote:
Hi,
I have a table with quite a few fields wich saves al kind of activities on
has done on one day.
Most important to identify a unique record is the date and personID.
A person is only allowed to enter his activity data once per date.
Now I have to set a condition that will check that a person is not entering
his activity data twice for that day.

I already have tried a lot in macro's , conditions and VB.
Here are some examples:

IsNull DLookUp([activityTable]![datefield]; [activityTable];
[activityTable]![datefield]=[datefieldInForm])
set in a macro as a condition, so that if there is no record with the
current date it will give Null and thus make the expression True and
excecuting my following command.

or

Set dbs = CurrentDb
SQL_Str = "SELECT datefield FROM activityTable WHERE datefield
=[datefieldInForm]
Set t_Recordset = dbs.OpenRecordset(SQL_Str)
If t_Recordset!Code Then already_there= "yes"

If a record did exist with the given date then de IF statement should be
True and thus setting already_there to "yes".

Somehow all these thing keep on giving errors. I'm probably overlooking
something really easy but I'm just stuk on this one.

Please help....

In query expressions (including domain aggregate functions) you need
to surround the dates with #'s. So try it like ...

SQL_Str = "SELECT datefield FROM activityTable WHERE datefield
= #" & [datefieldInForm] & "#"
DLookUp("datefield", "activityTable", "datefield=#" &
[datefieldInForm] & "#")

- Jim
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Luke Wright | last post: by
4 posts views Thread by Harsha Shah | last post: by
4 posts views Thread by Rodger Dusatko | last post: by
5 posts views Thread by Amogh | last post: by
5 posts views Thread by =?Utf-8?B?c2lwcHl1Y29ubg==?= | last post: by
1 post views Thread by sidathkp | last post: by
reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.