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....

[Jim Allensworth]

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