Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Jan 2003
    Location
    N. Ireland
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    date and access database problem

    Hi, i have an access database that holds all the data and i select 'upcoming events' by date, anything older than Date(today) is displayed on the archive page, the upcoming events are displayed as as from today onwards. It works most of the time but every now and again its been selecting all the events from the database and displaying them all as upcoming events, even though the date has obviously passed. The problem occurred now and again and usually went away when i closed down internet explorer and then reopened the page, weird i thought, but i had no ideas why it was doing it. I redid all the date coding again and formatted all input as short date (uk) and only formatted as long date for displaying purposes. It worked fine for 3 weeks but today it is displaying all the events again and this time it wont revert to normal no matter what i do.

    I have no idea where to start with this problem. Surely the code is either right or wrong and it does one thing or another and not both whenever it wants too? does this mean its a database problem?


    I have just made the events change back to display what they should be by removing this code locale from one page.
    <%
    'Set the server locale
    Session.LCID = 2057
    %>


    any ideas what is going on cos i want this to work automatically by date but am close to recoding it to manually use a checkbox to archive an event as i am pulling my hair out with this.
    ska

  • #2
    Regular Coder
    Join Date
    Nov 2007
    Location
    Chicago
    Posts
    134
    Thanks
    2
    Thanked 9 Times in 9 Posts
    could be a server issue... I had a weird problem with my database where, each time the server variables were reset, I would lose formatting data stored in my database.

    Can you paste the code and SQL statement you use to call/display the output?

  • #3
    New Coder
    Join Date
    Jan 2003
    Location
    N. Ireland
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, i was thinking the server could be causing a problem though i dont know how. the code below is used on several different pages. It works the way i want sometimes and others it doesnt. The same code on different pages gives different results too, at the moment it is showing correct events on one page and displaying all the data on another.....

    Code:
     
    
    CurrDate = Date
    CurrDate = FormatDateShort(CurrDate)
    			
     	Set objDB = New clsDatabase
    	strSQL = "Select * FROM events where [date]  between #" & CurrDate & "# and #25-12-2050# ORDER BY [date] ASC"
    ska

  • #4
    New Coder
    Join Date
    Jan 2003
    Location
    N. Ireland
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well, i found that this is a common problem with dates and access outside of usa and the solution is to use iso date format #yyyy-mm-dd# I have converted my date to that format but cant insert it into access so obviously im going wrong somewhere. I get a type mismatch error when i try this so how can i make access accept this format?
    ska

  • #5
    New Coder
    Join Date
    Jan 2003
    Location
    N. Ireland
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I've finally got the solution after a lot of research.

    the problem with Access, SQL and dates is that it will query the database in USA format (mm/dd/yyyy) first, no matter if you use locale id, or no matter what your pc regional settings are. This is a recipe for disaster as i found out.

    The solution is this function from Giuliano Sauro and a full explanation can be found on 4guysfromrolla.

    First make sure you have your locale id on your page to set the date according to your country's format. For me this was uk

    Session.LCID = 2057 (dd/mm/yyyy)

    Then ensure you have this function from Giuliano..

    Code:
    FUNCTION IsoDate(dteDate)
    'Version 1.0 by Giuliano Sauro
       If IsDate(dteDate) = True Then
          DIM dteDay, dteMonth, dteYear
          dteDay = Day(dteDate)
          dteMonth = Month(dteDate)
          dteYear   = Year(dteDate)
          IsoDate = dteYear & _
             "-" & Right(Cstr(dteMonth + 100),2) & _
             "-" & Right(Cstr(dteDay + 100),2)
       Else
          IsoDate = Null
       End If
    END FUNCTION

    to insert a date into access database field then format it using the function first....

    Code:
    If  IsDate(DatDateAdd) Then 
       DatDateAdd = IsoDate(DatDateAdd)
    End If
    the date will now be entered into access in ISO format which will avoid all problems.

    To query the database and ensure you get the required recordset back if querying by date then make sure you format your query date in ISO format too, ie query for all records from todays date up until end date (for me xmas 2050)

    Code:
    CurrDate = Date      ' get todays date
    CurrDate = IsoDate(CurrDate) ' format iso date
    
    strSQL = "Select * FROM events where [date]  between #" & CurrDate & "# and #25-12-2050# ORDER BY [date] ASC"
    It's solved all my date problems, thanks Giuliano!!
    ska


  •  

    Posting Permissions

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