Help with an update query

Lets say I have an Access database named Database001.
I have a table called Table001.
In the table I have the following fields:

1: Date\Time (an automated date\time stamp when an entry is added)
2: Status (defaults to the term 'Active')
3: Del14 (defaults to the term 'No')

I am tring to create an update query where the Status field is updated
to the term 'Inactive' if the field Del14 states 'Yes' and the
Date\Time stamp is more than 14 days old from the time the query is
ran.

I can do it in Access with the following SQL command:

UPDATE Table001 SET Table001.Status = "Inactive"
WHERE (((Date()+Time()-[Table001]![Date\Time])>13) AND
((Table001.Del14)="Yes"));

However, I am unable to use this code in asp. I receive a "Missing
Parameter" error. I am not very good at hard coding ASP so any help or
code snips would be great.

Thanks!

RE: Help with an update query

Good Question.
Do this create a new macro in the same database and try to update the
database using the docmd.updatequery and see if that works.
I believe the problem is with sending [] around the entities, since they
work directly with Access database but not with an external process.

"Brave" wrote:

> Lets say I have an Access database named Database001.
> I have a table called Table001.
> In the table I have the following fields:
>
> 1: Date\Time (an automated date\time stamp when an entry is added)
> 2: Status (defaults to the term 'Active')
> 3: Del14 (defaults to the term 'No')
>
> I am tring to create an update query where the Status field is updated
> to the term 'Inactive' if the field Del14 states 'Yes' and the
> Date\Time stamp is more than 14 days old from the time the query is
> ran.
>
> I can do it in Access with the following SQL command:
>
> UPDATE Table001 SET Table001.Status = "Inactive"
> WHERE (((Date()+Time()-[Table001]![Date\Time])>13) AND
> ((Table001.Del14)="Yes"));
>
> However, I am unable to use this code in asp. I receive a "Missing
> Parameter" error. I am not very good at hard coding ASP so any help or
> code snips would be great.
>
> Thanks!
>
>

Re: Help with an update query

Brave wrote:
> Lets say I have an Access database named Database001.
> I have a table called Table001.
> In the table I have the following fields:
>
> 1: Date\Time (an automated date\time stamp when an entry is added)
> 2: Status (defaults to the term 'Active')
> 3: Del14 (defaults to the term 'No')
>
> I am tring to create an update query where the Status field is updated
> to the term 'Inactive' if the field Del14 states 'Yes' and the
> Date\Time stamp is more than 14 days old from the time the query is
> ran.
>
> I can do it in Access with the following SQL command:
>
> UPDATE Table001 SET Table001.Status = "Inactive"
> WHERE (((Date()+Time()-[Table001]![Date\Time])>13) AND
> ((Table001.Del14)="Yes"));
>
> However, I am unable to use this code in asp. I receive a "Missing
> Parameter" error. I am not very good at hard coding ASP so any help or
> code snips would be great.
>
> Thanks!

Use the DateDiff function in your SQL statement:

UPDATE Table001 SET Status='Inactive' WHERE
DateDiff('d',[Date\Time],Date)>13 AND Del14='Yes'

--
Mike Brind

Re: Help with an update query

Thanks so much. The DateDiff function is something I will use many
times over.

Re: Help with an update query

Brave wrote:
> Lets say I have an Access database named Database001.
> I have a table called Table001.
> In the table I have the following fields:
>
> 1: Date\Time (an automated date\time stamp when an entry is added)

I hope that's not really the name of your field ... just because Access lets
you use non-standard characters in object names, does not mean that it's a
good idea for you to do so, especially if you wish to work with the database
from external programs.

> 2: Status (defaults to the term 'Active')
"Status" may be a reserved keyword ... no, according to
http://www.aspfaq.com/show.asp?id=2080, it doesn't appear to be reserved.
if it was, you would need to bracket it in statements run via ADO.

> 3: Del14 (defaults to the term 'No')
>
> I am tring to create an update query where the Status field is updated
> to the term 'Inactive' if the field Del14 states 'Yes' and the
> Date\Time stamp is more than 14 days old from the time the query is
> ran.
>
> I can do it in Access with the following SQL command:
>
> UPDATE Table001 SET Table001.Status = "Inactive"
> WHERE (((Date()+Time()-[Table001]![Date\Time])>13) AND
> ((Table001.Del14)="Yes"));
>
You cannot use the "bang" character (!) in sql statements run from external
programs. You will need to replace it with a period.

Anyways, this statement is only dealing with a single table, so there really
is no need to explicitly qualify your field names:

UPDATE Table001 SET Status = "Inactive"
WHERE (((Date()+Time()-[Date\Time])>13) AND
((Del14)="Yes"));

Moreover, this is a very inefficient way to accomplish your task. You should
avoid performing calculations on or applying functions to fields in your
WHERE clause. A better way would be (I'm removing the unnecessary
parentheses as well):

UPDATE Table001 SET Status = "Inactive"
WHERE [Date\Time] < DateAdd("d",-13,Now()) AND
Del14="Yes";

or
UPDATE Table001 SET Status = "Inactive"
WHERE [Date\Time] < Now() - 13 AND
Del14="Yes";


Seriously, change the name of [Date\Time] if that's really its name.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.