Date in Access with ASP/SQL (SOLVED)
I'm using an Access database for my webapplication.
In this application, users write a VisitDate in an input field and I check if it's a correct date and if it is, I try to write it towards the database. The date is in this format: (DD-MM-YYYY), that's how I try to save it to the database.
Then I also want to make, an overview of that table and order it by the date field, showing the most recent first and so on.
I don't know what type to choose for the date containing field in Access, like text or date/time. If I use text with a input mask, I can save the date succesfully but I can't display it in the right order (SQL statement sees it as plain text not as a date).
If I use date time (shortdate) this works and one of the problems is fixed (but it doesn't display the full year and doesn't place a zero in front of low day/months: dd-mm-yy, should be: dd-mm-yyyy). My biggest problem with this is: I don't know how to save the date towards the database correctly.
This how I want to order it correctly (I tried Dutch code which also uses DD-MM-YYYY, that shows it more correct):
Session.LCID = 1043
Dim Account_id, sqlVisitReport, rsVisitReport
Account_id = Request.QueryString("aid")
sqlVisitReport = "SELECT * FROM VisitReport VR WHERE (VR.CompanyName='" & Account_id & "') ORDER BY VR.VisitDate DESC"
Create_Recordset rsVisitReport, sqlVisitReport
This is how I try to save it.
vrdate has the value of the input field after it's checked if it's a correct date (DD-MM-YYYY) for example 12-06-2004.
sqlCreateVisitReport = "INSERT INTO VisitReport (CompanyName,WrittenBy,VisitDate,VisitedBy) VALUES ('" & accountname & "','" & writtenby & "'," & vrdate & ",'" & visitedby & "')"
set rs = Get_RecordSet(sqlCreateVisitReport)
Please help, if you do not understand my problem please tell me.