Date in Access with ASP/SQL (SOLVED)

[ShotokanTuning]

Hi,

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.

Thank you.

[dj4uk]

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 & "')"

HTH

DJ

[ShotokanTuning]

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.

[dj4uk]

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.

Try using:

Function FormatLongDate (inputDate)
	Dim dateString

	dateString = ""
	
	If Day(inputDate) < 10 Then
		dateString = dateString & "0"
	End If

	dateString = dateString & Day(inputDate) & "/"
		
	If Month(inputDate) < 10 Then
		dateString = dateString & "0"
	End If

	dateString = dateString & Month(inputDate) & "/"
	
	dateString = dateString & Year(inputDate)
	
	FormatLongDate = dateString
End Function

HTH

DJ

[ShotokanTuning]

Thanks man.

It's just what I didn't know how to do.
I only changed the "/" part with "-".

Great DJ.