Problem with an ASP update query

[Brave]

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!

 

[Tom Ellison]

Dear Brave:

Functions such as Date() and Time() are not available to you when your query
is not run by Access. This will be a severe limitation of using Jet with
ASP. Use MSDE instead. It has equivalent functions.

Tom Ellison

 

[Brave]

Thanks to everyone who helped. I was able to resolve the situation with
the following code:

UPDATE Table001 SET
Table001.[Active] = 'Inactive'
WHERE (((Date()+Time()-Table001.[Date\Time])>('::13::')) AND
((Table001.[Del14])= 'Yes'));

 

[Brendan Reynolds]

This is a common misconception - and one that I used to share - but it is a
misconception. Many built-in VBA functions, including Date() and Time, can
be used in JET queries when executed outside of Microsoft Access. For
example, here's a C# console app that executes a query against a JET
database using Date() and Time(). The output from this program on my system
(dd/mm/yyyy format) is ...

23/03/2006 00:00:00
30/12/1899 14:56:17

namespace TestJet
{
class Class1
{
[System.STAThread]
static void Main(string[] args)
{
System.Data.OleDb.OleDbConnection connection
= new System.Data.OleDb.OleDbConnection();
connection.ConnectionString
= "Provider=Microsoft.Jet.OLEDB.4.0;"
+ "Data Source=C:\\usenet\\asp_test_data.mdb;"
+ "Persist Security Info=False";
System.Data.OleDb.OleDbCommand command
= new System.Data.OleDb.OleDbCommand();
command.CommandText =
"SELECT Date() AS TheDate, Time() AS TheTime FROM Employees";
command.CommandType = System.Data.CommandType.Text;
command.Connection = connection;
System.Data.OleDb.OleDbDataReader reader = null;
try
{
connection.Open();
reader = command.ExecuteReader();
reader.Read();
System.Console.WriteLine(reader.GetValue(0).ToString());
System.Console.WriteLine(reader.GetValue(1).ToString());
}
finally
{
if (reader != null)
{
reader.Close();
}
if (connection != null && connection.State
!= System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
System.Console.ReadLine();
}
}
}

--
Brendan Reynolds
Access MVP

Dear Brave:

Functions such as Date() and Time() are not available to you when your
query is not run by Access. This will be a severe limitation of using Jet
with ASP. Use MSDE instead. It has equivalent functions.

Tom Ellison

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!