HELP - Insert/update date to Access database when records are changed

[xfile]

Hi:


Appreciate if anyone can help me on this since I've spent two days and still
could not solve.

Purpose - Update a feild called "DATE" in Access DB when records are
changed.

To skip all other previous problems, the following is the closest I can get
to the code without any errors:

Explainations:

(1) It is based on http://www.4guysfromrolla.com/webtech/041001-1.2.shtml,
and I've tried many others also failed.

(2) I put into two UPDATE statements, because I don't know how to integrate
into one without any errors. It is always either this or that errors.

(3) RegNo is a feild name in database, primary key and auto number. In the
same time, RegNo also is a hidden field carried by the previous form for the
RegNo value of the user.

(4) DATE is the field name of the database, which is set to DATE/TIME and no
additional setting, otherwise.

(5) If I set condition using FirstName and LastName, again, there will be
other errors, so I changed to using RegNo unlike the second UPDATE
statement.


My problem is nothing happened when I run the script. Strange thing is,
all things listed on the second statement will be updated (I am just using
two fields and skipping others to save space, but there are other fields).

Simply stated, I just cannot insert or update the DATE filed. I have tried
other scripts, and using hidden value from the previous form (e.g. NOW () ),
which could display on the page, but just cannot insert to the database.

Really appreciate any help. Please also let me know if additional settings
are need for the field in Access, if necessary.


--------------
' skip open connection and so on...


Function MediumDate (str)
Dim aDay
Dim aMonth
Dim aYear

aDay = Day(str)
aMonth = Monthname(Month(str),True)
aYear = Year(str)

MediumDate = aDay & "-" & aMonth & "-" & aYear
End Function


mySQL = "SELECT * FROM tb1Users WHERE FirstName='" & FirstName & "' AND
LastName='" & LastName & "'"
mySQL = mySQL + " WHERE FirstName = '" & FirstName & "' AND LastName = '" &
LastName & "';"

mySQL = "UPDATE tb1Users WHERE RegNo=" & RegNo
mySQL = mySQL +"SET Date = #" & MediumDate( Date() ) & "# " & _
mySQL = mySQL + "WHERE fldStatus = open ; "

mySQL = "UPDATE tb1Users"
mySQL = mySQL + " SET PWD= '" & Request("Password") & "',ConfirmPWD= '" &
Request("ConfirmPassword") & "' "
mySQL = mySQL + " WHERE FirstName = '" & FirstName & "' AND LastName = '" &
LastName & "';"

' skip execute, close and so on...

-------------------------------------------------------------

 

[Kathleen Anderson [MVP - FrontPage]]

Date is a reserved word in SQL - try changing the name of the field to
something else.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

 

[xfile]

Hi:

Thanks for the reply, but still

Date is a reserved word in SQL - try changing the name of the field to
something else.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

 

[xfile]

Just to share, and maybe does not have to be so complicated.

Dim Today
Today = date

mySQL = "UPDATE TableName"
mySQL = mySQL + " SET Date_LastUpdated= '" & (Today) & "' "
Additional conditions if necessary to ensure records to be updated is the
right one.

Hi:

Thanks for the reply, but still