Save a timestamp towards database (Access) - SOLVED -

Edit: I fixed it. The problem is the field name Timestamp, it's a Reserved word. So I changed it to [Timestamp] and now it works.

Hi,
I'm using a Access database.
In this database I've a table called VisitReport.
In the table VisitReport there is a field Timestamp from the type Date/Time -> "General Date"

Now what I want to do is save the current time towards the Timestamp field.

I tried to use this in the querry:
Timestamp=" & Now & " or #" & Now & "#

and even this:
Timestamp=#" & Year(Now) & "-" & Month(Now) & "-" & Day(Now) & " " & Hour(Now) & ":" & Minute(Now) & ":" & Second(Now) & "#

But they return syntax errors.

I haven't worked with general date or the Now function before so please explain to me how to do this thing.

Thanks a lot.

Used code:

VB Code:

sqlCreateVisitReport = "INSERT INTO VisitReport (VisitDate, 
CompanyName,WrittenBy,Address,VisitedBy,DFIStakeholders,ExecutiveSummary
,AgreedBusinessObjective,VisitReportFile,Presentation
,Timestamp) VALUES (#" & Year(vrdate) & "-" & Month(vrdate) 
& "-" & Day(vrdate) & "#,'" & accountname & "','" & writtenby 
& "','" & address & "','" & visitedby & "','" & dfistakeholders & "',
'" & executivesummary & "','" & agreedbusinessobjective & "','" & 
visitreport & "','" & presentation & "', " & Now & ")"

Quote:

---

"', " & Now & ")"

---

You need something like

Code:

---

"', #" & Now & "#)"

---

You put it in for the first date, but not around the second. Try that and post up if it works.


Vince

Edit: I fixed it. The problem is the field name Timestamp, it's a Reserved word. So I changed it to [Timestamp] and now it works.

Yeah thanks for your reply, indeed but I already tried that (this was a desperate attempt without the #).

This shows the Now function does work okay:

Code:
Response.Write Now
Result:
27-7-04 13:50:39

This is what happens with the # :

sqlCreateVisitReport = "INSERT INTO VisitReport (VisitDate,
CompanyName,WrittenBy,Address,VisitedBy,DFIStakeholders,ExecutiveSummary,
AgreedBusinessObjective,VisitReportFile,Presentation,Timestamp)
VALUES (#" & Year(vrdate) & "-" & Month(vrdate) & "-" & Day
(vrdate) & "#,'" & accountname & "','" & writtenby & "','" &
address & "','" & visitedby & "','" & dfistakeholders & "','" & executivesummary & "','" & agreedbusinessobjective & "','" &
visitreport & "','" & presentation & "', #" & Now & "#)"


Error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.


I get no errors and everything works when the Timestamp and Now part are left out.

INSERT INTO VisitReport (VisitDate, CompanyName,WrittenBy,Address,VisitedBy,DFIStakeholders,ExecutiveSummary,AgreedBusinessObjective,Vis itReportFile,Presentation,Timestamp) VALUES (#2004-1-12#,'Aquafil','','','','','','','','', #27-7-04 14:07:43#)

This is how it looks when I only entered the first two fields. You can see the date formats are quite different maybe it's because of that?

Thanks for trying to help me out.

Also I'm using Session.LCID = 1043.