Date Functionality Missing

brian.teller · Jan 10, 2007

I recently got an error from when I ran an sql query that resulted in
an error

Microsoft JET Database Engine (0x80040E07)
Syntax error in date in query expression 'Date1 = ##'.

As I investigated why this occured, I noticed I lost the ability to
look up the date using asp from frontpage.
The code below shows a red bar, the text "Test" and "Summary", but the
date value does appear.

Any suggestions what happen here and how to correct?

Thanks

Brian

<html>
<head><title>Date Check</title></head>
<body link="#FFFFFF" vlink="#FFFFFF" alink="#FFFFFF">
<table border="0" align = "center" width="100%" cellpadding="0"
cellspacing="0" bordercolor="#000000" bgcolor="#CC0000">
<td width="1%" bgcolor="#CC0000">
<img border="0" src="../images/pictures/redpic.jpg" width="48"
height="34" align="left">
</td>
<td width="70%" bgcolor="#CC0000" bordercolor="#FFFFFF">
Test
</td>
<td width="29%" valign = "bottom" align = "Right"
bgcolor="#CC0000">
<%
Response.Write Now()
%>
 
Summary</a>
</td>
</table>

Stefan B Rusynko · Jan 11, 2007

brian.teller · Jan 11, 2007

The syntax error (line with the SQL12) was caused by the array having a
null value in the seventh element. Our fiscal week goes for 7 days
starting Jan 1 and ending Jan 7th. I was able to correct that issue.
However, I will have issues once there is more than 1 year 's worth of
data and the fiscal weeks begin to repeat. Is there anyway to avoid
this by having the query just look up the most recent FW data or using
a "smart" calendar.

Dim DateArray(7)
i=1
SQLDates = "SELECT Date1, FW FROM tblFW WHERE FW =
DatePart("ww",Date())-1"
Set RSDates = Conn1.Execute(SQLDates)
Do While not RSDates.eof
DateArray(i) = RSDates("Date1")
Response.Write DateArray(i)
i = i + 1
RSDates.Movenext
Loop

For i = 1 to 7
'Identifying the date
CurrentDate = DateArray(i)
'Finding all production information for that date.
SQL12 = "Select * FROM Production WHERE Date1 = #" & CurrentDate & "#"
Set RS12 = Conn1.Execute(SQL12)
'Rest of the code not included.

Stefan B Rusynko · Jan 12, 2007

Why not just constrain the results to the last 12 months

SQLDates = "SELECT Date1, FW FROM tblFW
WHERE FW =DatePart("ww",Date())-1" AND PastYr=DateAdd("m",Date(),-12)

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________

| The syntax error (line with the SQL12) was caused by the array having a
| null value in the seventh element. Our fiscal week goes for 7 days
| starting Jan 1 and ending Jan 7th. I was able to correct that issue.
| However, I will have issues once there is more than 1 year 's worth of
| data and the fiscal weeks begin to repeat. Is there anyway to avoid
| this by having the query just look up the most recent FW data or using
| a "smart" calendar.
|
| Dim DateArray(7)
| i=1
| SQLDates = "SELECT Date1, FW FROM tblFW WHERE FW =
| DatePart("ww",Date())-1"
| Set RSDates = Conn1.Execute(SQLDates)
| Do While not RSDates.eof
| DateArray(i) = RSDates("Date1")
| Response.Write DateArray(i)
| i = i + 1
| RSDates.Movenext
| Loop
|
| For i = 1 to 7
| 'Identifying the date
| CurrentDate = DateArray(i)
| 'Finding all production information for that date.
| SQL12 = "Select * FROM Production WHERE Date1 = #" & CurrentDate & "#"
| Set RS12 = Conn1.Execute(SQL12)
| 'Rest of the code not included.
|

Error message "An error has occured in the script on this page"	1	Jul 18, 2008
Report Grouping	2	Feb 4, 2009
Frontpage removes javascript after save	6	Jan 2, 2008
Menu to drop down only on arrow?	3	Feb 23, 2009
Receiving code instead of graphics	0	Dec 26, 2009
Absolute Position of a table realtive to a cell?	3	Feb 15, 2008
Can one include data from javascript in form	1	Dec 30, 2009
CSS DOES NOT DISPLAY IN IE7 FROM AN INDEX PAGE	4	Jan 30, 2008

Date Functionality Missing

brian.teller

Stefan B Rusynko

brian.teller

Stefan B Rusynko

Ask a Question

Similar Threads