Help please - date/time stamp problem

Results 1 to 2 of 2

Thread: Help please - date/time stamp problem

  1. #1
    Join Date
    Dec 1969

    Default Help please - date/time stamp problem

    Hi - hope you can help.<BR><BR>I&#039m using FrontPage 2000 and Access 2000 on PWS with .asp<BR><BR>I&#039ve got a problem displaying a date in the UK format.<BR><BR>Here&#039s what I&#039m doing . . . . . When a user first submits a Form this Form writes to the database TABLE, which automatically enters the current date/time with default value =Now(). No problem with this, it works fine. <BR><BR>The date field that the database uses is LastContacted.<BR><BR>Ideally when that user returns and UPDATES their data (online) I wanted Access to automatically update the LastContacted<BR>field with the current date/time. This doesn&#039t work - If you know how to do this without using an Access Form I&#039d appreciate the help as this would be the ideal way to solve my problem. <BR><BR>As I can&#039t find a solution this is the road I&#039m going down, which has thrown up an anomaly.<BR> <BR>What I&#039ve got is an .asp with a database search field. The user enters their reference and the database returns their LastContacted date.<BR>The user then manually inserts the LastContacted date into a field named OLDDATE.<BR> <BR>So that the database will be updated with the current date/time - the user has to also enter the current date/time in NEWDATE.<BR><BR>The anomaly is at this point - if the user enters OLDDATE & NEWDATE the database is not updated BUT if the user swaps the OLDDATE around (American format) then the database writes ok (unfortunately it does overwrites the =NOW() function - but as it doesn&#039t work I can live with that).<BR> <BR>Example: Database returns 25/04/00 - user enters 25/04/00 into OLDDATE - and also enters current date 21/07/00 into NEWDATE. This doesn&#039t work.<BR>BUT if user enters 04/25/00 into OLDDATE - and also enters current date 21/07/00 into NEWDATE. This does work. <BR><BR>The date/time in Access returns the UK format - as does the database result on the .asp - the problem is when I try to write the date back to the database - here is my SQL (custom query/edit box) <BR><BR>UPDATE table1<BR>SET LastContacted=#::LastContacted::#<BR>WHERE LastContacted=#::OldDate::#<BR>AND Check=&#039::Check::&#039 <BR><BR>(check = users reference)<BR><BR>My regional settings are correct. I have also tried every Access date/time option - I&#039ve left it now at &#039General&#039<BR> <BR>Any ideas? <BR> <BR>Any help would really be appreciated<BR> <BR>Stephen

  2. #2
    smbius Guest

    Default RE: Help please - date/time stamp problem

    I&#039m not sure if this is the answer you&#039re looking for looks like to me that your SQL statement needs to be dynamically generated in order for the server to do the time stamping instead of the user doing it for you.<BR><BR>example:<BR><BR>when the user is editing an entry he/she makes<BR><BR>your ASP page pulls up the single query of data...including the<BR><BR>SELECT * FROM table1 WHERE something=&#039xxx&#039 AND etc...<BR><BR>resulting in a form page with the data in it that the user can edit.<BR><BR>So, instead of having form fields for the users to enter the have SQL statement do it for you.<BR><BR>When the user hits the submit button to go to the ASP page that&#039s going to manipulate the data and update the DB, that particular page should have a dynamic SQL statement like this...<BR><BR><BR>UPDATE table1 SET LastContacted=#"&(date)&"#, UserValue=&#039"&Request.Form("some_data_entered_i nprevious_page")&"&#039" <BR><BR>Hope that helped.<BR><BR>smbius<BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts