Problem with an update query in ASP

  • Thread starter Thread starter Brave
  • Start date Start date
B

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!
 
If you can tell us the exact data types of the columns you're working with,
and in plain English, what you want to do with them, we can help.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Show me your certification without works,
and I'll show my certification
*by* my works.
 
Date\Time: is a Date\Time field in the MS database that captures the
Date()+Time() function (example - 01/15/02 10:30am)

Status: is a text field that is updated by an administrator (either set
as Active or Inactive)

Del14: is also a text field that is updated by an administrator (either
set as Yes or No)

The purpose of the query is to give administrators of the database the
ability to mark an entry (by choosing Yes in the Del14 field) so that
when they run the command any entry

(a) That has the Del14 field set as Yes
and
(b) The Date\Time field's value is 14 days old or more

will have their Status field updated to Inactive.

Let me know if I can offer any additional information and thanks for
any help you can give.
 
Both Date and Time are reserved names in ASP and should never be used for a field names
- and neither should the operator /
Rename your DB field from "Date\Time" to say "Date_Time"

Next don't try to run operations (date math) in a query string
run it before the query string as a variable to use in comparison

Also if Del14 is a really a text field you can use
(Table001.Del14="Yes")
But if it is a Yes/No Field you should be using it w/o delimiters
(Table001.Del14=Yes)
Or for a True/False Field
(Table001.Del14=True)
- same applies to Status field

So you would have

CheckDate = DateAdd("d", -13, Date())
'gets a date 13 days ago
strSQL= "UPDATE Table001 SET Table001.Status = 'Inactive' WHERE " &_
"Table001.Date_Time<" & CheckDate & " AND " &_
"Table001.Del14='Yes'"

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
_____________________________________________


| Date\Time: is a Date\Time field in the MS database that captures the
| Date()+Time() function (example - 01/15/02 10:30am)
|
| Status: is a text field that is updated by an administrator (either set
| as Active or Inactive)
|
| Del14: is also a text field that is updated by an administrator (either
| set as Yes or No)
|
| The purpose of the query is to give administrators of the database the
| ability to mark an entry (by choosing Yes in the Del14 field) so that
| when they run the command any entry
|
| (a) That has the Del14 field set as Yes
| and
| (b) The Date\Time field's value is 14 days old or more
|
| will have their Status field updated to Inactive.
|
| Let me know if I can offer any additional information and thanks for
| any help you can give.
|
 
Hi Rave,

If you want the Date and Time, use the Now() function.

Also, it is important to note that a lot of Access proprietary SQL doesn't
work with remote queries. I'm not sure how well using the subtraction
operator with dates will work, so use DateTime functions. Use single quotes
for double quotes. Don't use the '*' character, but the '%' character for
wildcard queries. Avoid the '!' object notation. If you anticipate that a
field may have a name that is a reserved word, or has spaces in it, or other
reserved characters (such as '\') enclose it in [square brackets]. And,
since you're specifying the table in the query, there is no need to keep
re-referencing it.

Here's an example of your query that should work.

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

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Show me your certification without works,
and I'll show my certification
*by* my works.
 
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'));

Kevin: Thank you for the DateDiff function. I can see where that is
much easier to work with.
 
Back
Top