Date in Access with ASP/SQL (SOLVED)-VBForums
Results 1 to 5 of 5

Thread: Date in Access with ASP/SQL (SOLVED)

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jun 2004
    Location
    Earth 4 now.
    Posts
    27

    Date in Access with ASP/SQL (SOLVED)

    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.
    Last edited by ShotokanTuning; Jul 15th, 2004 at 04:48 AM.

  2. #2
    Frenzied Member dj4uk's Avatar
    Join Date
    Aug 2002
    Location
    Birmingham, UK Lobotomies: 3
    Posts
    1,131
    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:
    Code:
    sqlCreateVisitReport = "INSERT INTO VisitReport (CompanyName,WrittenBy,VisitDate,VisitedBy) VALUES ('" & accountname & "','" & writtenby & "',#" & Year(vrdate) & "-" & Month(vrdate) & "-" & Day(vrdate) & "#,'" & visitedby & "')"
    HTH

    DJ

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jun 2004
    Location
    Earth 4 now.
    Posts
    27
    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.

  4. #4
    Frenzied Member dj4uk's Avatar
    Join Date
    Aug 2002
    Location
    Birmingham, UK Lobotomies: 3
    Posts
    1,131
    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:
    Code:
    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

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Jun 2004
    Location
    Earth 4 now.
    Posts
    27
    Thanks man.

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

    Great DJ.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.