date and access database problem

[skalag]

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.

[arthurakay]

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?

[skalag]

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.....

 

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"

[skalag]

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?

[skalag]

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..

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....

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)

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!!