Jul 14th, 2004, 06:53 AM
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.
Last edited by ShotokanTuning; Jul 15th, 2004 at 04:48 AM.
Jul 14th, 2004, 09:58 AM
Dates cause all sorts of problems in Access if you are using British date format - a tad unpredictable at times!
Its still better to use a date field rather than a text field as otherwise it'll complicate things alot more when you start creating queries (just think of a simple order by).
I find the best way to use it is use the short date format in Access and then whenever you insert a date use 'YYYY-MM-DD' format as this never gets confused between American and British format. So your insert statement would be:
sqlCreateVisitReport = "INSERT INTO VisitReport (CompanyName,WrittenBy,VisitDate,VisitedBy) VALUES ('" & accountname & "','" & writtenby & "',#" & Year(vrdate) & "-" & Month(vrdate) & "-" & Day(vrdate) & "#,'" & visitedby & "')"
Jul 14th, 2004, 10:22 AM
Thanks that works DJ,
How can I show the date in DD-MM-YYYY with a leading zero if the day/month is below 10, when I read it from the database (shortdate).
If someone could help me with that, this problem is fixed.
Thanks a lot.
Jul 14th, 2004, 10:42 AM
When you mean show I think you mean output to the browser?
I tend to use a custom function to output dates in format I want as VBScripts built in FormatDateTime does always create what I want.
Function FormatLongDate (inputDate)
dateString = ""
If Day(inputDate) < 10 Then
dateString = dateString & "0"
dateString = dateString & Day(inputDate) & "/"
If Month(inputDate) < 10 Then
dateString = dateString & "0"
dateString = dateString & Month(inputDate) & "/"
dateString = dateString & Year(inputDate)
FormatLongDate = dateString
Jul 15th, 2004, 03:17 AM
It's just what I didn't know how to do.
I only changed the "/" part with "-".
Click Here to Expand Forum to Full Width
Survey posted by VBForums.