View Single Post
Old 03-11-2009, 11:04 PM   #1
smitho
 
Join Date: Dec 2007
Posts: 149
Default ASP and MS Access date problem.

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:

HTML Code:
<%
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;

HTML Code:
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 is offline   Reply With Quote