ASP and MS Access date problem.

[smitho]

Hi all,

It's been a very long time since I've posted. Teaching myself ASP over the last year and a bit, wished my work place would adopt PHP would make my life easier. Anyway I've got a problem with the code below.

I have an MS Access database and a VBscript that adds records to it. The uploaded field has a default value of now() so as the record is being added it captures the date and time.

What I wanted to do was display the number of entries for each day and to do this I'm using this:

<%
Dim sDate, myLoop
sDate = DateAdd("d",-31,Date)
myLoop = DateDiff("d",sDate,Date)
'Begin Loop
Dim i
For i=1 to myLoop

Dim mycount 'get request al table details
set mycount=Server.CreateObject("ADODB.recordset")
mycount.Open "SELECT * FROM qclist WHERE uploaded>=#"&sDate&" 12:00:00 AM# AND uploaded<=#"&sDate&" 11:59:00 PM# ORDER BY id ASC", MM_Nums_STRING
'mycount.Open "SELECT * FROM qclist WHERE uploaded>=#10/03/2009 8:19:02 AM# AND uploaded<=#10/03/2009 7:19:01 PM# ORDER BY id ASC", MM_Nums_STRING

' Count how many records exist
DIM iRecordCount
iRecordCount = 0
WHILE NOT mycount.EOF
'Response.Write(mycount.Fields.Item("filename").Value)&"<br>"
iRecordCount = iRecordCount + 1
mycount.MoveNext
Wend
' Display result
Response.Write "<strong>"&sDate&" = (" & iRecordCount & ")Files</strong><br>"
'Got to the next day
sDate = DateAdd("d",1,sDate)
Next 
'Close connections
mycount.Close
Set mycount = Nothing
%>

Then result is;

09/02/2009 = (0)Files
10/02/2009 = (0)Files
11/02/2009 = (0)Files
12/02/2009 = (0)Files
13/02/2009 = (150)Files
14/02/2009 = (1)Files
15/02/2009 = (0)Files
16/02/2009 = (80)Files
17/02/2009 = (122)Files
18/02/2009 = (202)Files
19/02/2009 = (101)Files
20/02/2009 = (154)Files
21/02/2009 = (5)Files
22/02/2009 = (0)Files
23/02/2009 = (90)Files
24/02/2009 = (100)Files
25/02/2009 = (209)Files
26/02/2009 = (142)Files
27/02/2009 = (162)Files
28/02/2009 = (2)Files
01/03/2009 = (0)Files
02/03/2009 = (127)Files
03/03/2009 = (141)Files
04/03/2009 = (0)Files
05/03/2009 = (0)Files
06/03/2009 = (0)Files
07/03/2009 = (0)Files
08/03/2009 = (0)Files
09/03/2009 = (0)Files
10/03/2009 = (0)Files
11/03/2009 = (0)Files

The problem is that for the days between 04/03/2009 to 06/03/2009 and 09/03/2009 to 11/03/2009 there are records but they are not being picked up.

Has anyone come across this before. I'm thinking it might have somthing with the date format dd/mm/yyyy it may be reading it mm/dd/yyyy.

Any suggestions would be appreciated.

[smitho]

Just to test my theory I added:

session.lcid=1033
'1033 is General Unicode

The result was:

2/9/2009 = (126)Files
2/10/2009 = (109)Files
2/11/2009 = (206)Files
2/12/2009 = (157)Files
2/13/2009 = (150)Files
2/14/2009 = (1)Files
2/15/2009 = (0)Files
2/16/2009 = (80)Files
2/17/2009 = (122)Files
2/18/2009 = (202)Files
2/19/2009 = (101)Files
2/20/2009 = (154)Files
2/21/2009 = (5)Files
2/22/2009 = (0)Files
2/23/2009 = (90)Files
2/24/2009 = (100)Files
2/25/2009 = (209)Files
2/26/2009 = (142)Files
2/27/2009 = (162)Files
2/28/2009 = (2)Files
3/1/2009 = (0)Files
3/2/2009 = (116)Files
3/3/2009 = (141)Files
3/4/2009 = (197)Files
3/5/2009 = (141)Files
3/6/2009 = (181)Files
3/7/2009 = (0)Files
3/8/2009 = (0)Files
3/9/2009 = (22)Files
3/10/2009 = (121)Files
3/11/2009 = (179)Files

Wrong date format but file figures look right.

[smitho]

I found the problem. There was a nice script I found that give you info about your PC setup when I ran it and browsed the result in firefox I got:

Browser LCID: 1033 Server LCID: 3081

In IE I get:

Browser LCID: 3081 Server LCID: 3081

I'm trying to find a way to get Firefox to use LCID 3081 but not having much luck. Has anyone come across this before?