Date Functionality Missing

B

brian.teller

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"><p align="center"><font
color="#000000">
<img border="0" src="../images/pictures/redpic.jpg" width="48"
height="34" align="left"></font></p>
</td>
<td width="70%" bgcolor="#CC0000" bordercolor="#FFFFFF">
<b><font face="Arial" size="5" color="#FFFFFF">Test</b></font>
</td>
<td width="29%" valign = "bottom" align = "Right"
bgcolor="#CC0000"><font face="Arial" size="3" color="#FFFFFF"></b>
<%
Response.Write Now()
%>
<br>
<b>Summary</a>
</td>
</table>
 
S

Stefan B Rusynko

1) Post your full sql string code for help on the error

2) For the date only just use

<% =Date() %>
which is same as
<% Response.Write Date() %>

--

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


|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"><p align="center"><font
| color="#000000">
| <img border="0" src="../images/pictures/redpic.jpg" width="48"
| height="34" align="left"></font></p>
| </td>
| <td width="70%" bgcolor="#CC0000" bordercolor="#FFFFFF">
| <b><font face="Arial" size="5" color="#FFFFFF">Test</b></font>
| </td>
| <td width="29%" valign = "bottom" align = "Right"
| bgcolor="#CC0000"><font face="Arial" size="3" color="#FFFFFF"></b>
| <%
| Response.Write Now()
| %>
| <br>
| <b>Summary</a>
| </td>
| </table>
|
 
B

brian.teller

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

Stefan B Rusynko

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top