date error, dates change format

[Davo67]

Hi
1st of all i have looked at faqs and i'm still having problems.
I am new to asp and trying solve a problem with dates that are being inserted and then updated in an Access database.
The problem:
entering a UK date 27/01/2003 (27-jan-2003) goes into the database ok.

If the date is 11/01/2003(11-jan-2003) it enters the date as 01/11/2003(01-nov-2003).

No matter what date format I use dd/mm/yyyy or mm/dd/yyyyy
if the day value is less than 13 ie. 1 - 12 then it changes the date format round to be the month.

Here's the form input tag code

<input type=&quot;text&quot; name=&quot;required_by&quot; size=&quot;20&quot; value=&quot; <% =FormatDateTime(now,2)%> &quot;>

It uses the now function as a default date but can be changed by users.


Here's the SQL code


<%
date_reported= request.form(&quot;date_reported&quot
contact_type=request.form(&quot;contact_type&quot
reported_by=request.form(&quot;reported_by&quot
contact_tel=request.form(&quot;contact_tel&quot
branch_name=request.form(&quot;branch_name&quot
problem_type=request.form(&quot;selectbox1&quot
problem_cat=request.form(&quot;selectbox2&quot
required_by=request.form(&quot;required_by&quot
assigned_to=request.form(&quot;assigned_to&quot
notes=request.form(&quot;notes&quot
status=request.form(&quot;status&quot


set conn=Server.CreateObject(&quot;ADODB.Connection&quot
conn.Provider=&quot;Microsoft.Jet.OLEDB.4.0&quot;
conn.Open &quot;c:\helpdesk.mdb&quot;



'Actual SQL
sql=&quot;INSERT INTO logfile(date_reported,contact_type,reported_by,contact_tel,branch,problem_type,problem_cat,required_by,assigned_to,notes,status)&quot; _
& &quot; values('&quot;& date_reported &&quot;',&quot; & &quot;'&quot; & contact_type &&quot;',&quot;&&quot;'&quot; & reported_by & &quot;','&quot; & contact_tel & &quot;','&quot;& branch_name & &quot;','&quot; _
& problem_type & &quot;','&quot; & problem_cat & &quot;','&quot; & required_by & &quot;','&quot; & assigned_to & &quot;','&quot; & notes & &quot;','&quot; & status &&quot;')&quot;

...etc


I have tried using the formatdatetime function around the date when it being INSERT'd into the database.

Please help as it's driving me nuts and is such a basic problem but i can't see where i've gone wrong.

 

[mwolf00]

Since access expects dates in US format, you need to rewrite the input date (UK format) to US format...

function usDate(inDate)
usDate = datePart(&quot;m&quot;,inDate) & &quot;/&quot; & datePart(&quot;dd&quot;,inDate) & &quot;/&quot; & datePart(&quot;yyyy&quot;,inDate)
end function


sql=&quot;INSERT INTO logfile(date_reported,contact_type,reported_by,contact_tel,branch,problem_type,problem_cat,required_by,assigned_to,notes,status)&quot; _
& &quot; values('&quot;& usDate(date_reported) &&quot;',&quot; & &quot;'&quot; & contact_type &&quot;',&quot;&&quot;'&quot; & reported_by & &quot;','&quot; & contact_tel & &quot;','&quot;& branch_name & &quot;','&quot; _
& problem_type & &quot;','&quot; & problem_cat & &quot;','&quot; & required_by & &quot;','&quot; & assigned_to & &quot;','&quot; & notes & &quot;','&quot; & status &&quot;')&quot;


Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook