Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / March 2006

Tip: Looking for answers? Try searching our database.

Problem with an ASP update query

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Brave - 22 Mar 2006 22:34 GMT
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 - 22 Mar 2006 23:11 GMT
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.
[quoted text clipped - 20 lines]
>
> Thanks!
Brave - 23 Mar 2006 14:12 GMT
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 - 23 Mar 2006 16:05 GMT
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();
 }
}
}

Signature

Brendan Reynolds
Access MVP

> Dear Brave:
>
[quoted text clipped - 28 lines]
>>
>> Thanks!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.