Date Problems!

[holty]

Hi,

I have a problem!

I have a table in access which contains advert information. There is a field named date (when the advert was added).

On my asp page i would like to show all adverts that appeared in the last 14 days.

I realise that:

Date() - will show the current date - 08/01/03
Date()-14 - will show current date minus 14 - 26/12/02.

However in my asp page it shows advetrs taht have been added on the 10/12/02. Anyone had this problem before?

Here's my code

If rsAdvertDetails("Date") > Date()-14 then
Do While not rsAdvertDetails.EOF
%>
<center>
<table border="1" cellpadding="0" cellspacing="0" width="95%">
<tr>
<td width="100%" bgcolor="#5E6D95" class="heading" colspan="2">
<b>&raquo; Current Advert Information for <%=request.cookies("Username")%></b></td>
</tr>
<tr>
<td width="25%" class="middle"><b>Model: </b></font> </td>
<td width="75%" class="middle"><%=rsAdvertDetails("VWModelDescription")%>&nbsp;</td>
</tr>
<tr>
<td width="25%" class="middle"><b>Model type: </b></font> </td>
<td width="75%" class="middle"><%=rsAdvertDetails("VWModelTypeDescription")%>&nbsp;</td>
</tr>
<tr>
<td width="25%" class="middle"><b>Description: </b></font> </td>
<td width="75%" class="middle"><%=rsAdvertDetails("Description")%>&nbsp;</td>
</tr>
<tr>
<td width="25%" class="middle"><b>Year: </b></font> </td>
<td width="75%" class="middle"><%=rsAdvertDetails("Year")%>&nbsp;</td>
</tr>
<tr>
<td width="25%" class="middle"><b>Image: </b></font> </td>
<td width="75%" class="middle"><%=rsAdvertDetails("Image")%>&nbsp;</td>
</tr>
<tr>
<td width="25%" class="middle"><b>Price: </b></font> </td>
<td width="75%" class="middle"><%=rsAdvertDetails("Price")%>&nbsp;</td>
</tr>
<tr>
<td width="25%" class="middle"><b>Date: </b></font> </td>
<td width="75%" class="middle"><%=rsAdvertDetails("Date")%>&nbsp;</td>
</tr>
<tr>
<td width="100%" class="middle" colspan="2"><center><form method="POST" action="updateadvert.asp?advertID=<%=rsAdvertDetails("AdvertID")%>">
<INPUT TYPE=SUBMIT NAME="cmdLogout" VALUE="Revise this advert"></center>
</form>
</table>
<br>
</center>

<%

'Move to the next record in the recordset
rsAdvertDetails.MoveNext

Loop
%>
<% end if %>

[Phip]

how about something like

"SELECT tablename.date FROM tablename WHERE date=<'date()-14' ORDER BY date DESC";

[holty]

Thanks, will give that a go when i get back to my dev machine :)

[whammy]

FYI, when using SQL with an access database, datetime fields need to be surrounded by # instead of ' in your query.

:)

[holty]

Hi,

It doesn't seem to work -

Date () will give 11/01/03
Date()-14 will give 28/12/02

It will display dates such as 29/06/02. It must work of the day first. I'm abit confused.

Anyone have a good idea how to show records in a database that have bee added over the last 14 days?

Ta

[Morgoth]

DateDiff()
<%
Response.Write DateDiff("d",#12/26/2002 12:00:00AM#,Now()) & "<br>"
Response.Write DateDiff("d",#12/27/2002 12:00:00AM#,Now()) & "<br>"
Response.Write DateDiff("d",#12/28/2002 12:00:00AM#,Now()) & "<br>"
Response.Write DateDiff("d",#12/29/2002 12:00:00AM#,Now()) & "<br>"
Response.Write DateDiff("d",#12/30/2002 12:00:00AM#,Now()) & "<br>"
Response.Write DateDiff("d",#12/31/2002 12:00:00AM#,Now()) & "<br>"
Response.Write DateDiff("d",#01/01/2003 12:00:00AM#,Now()) & "<br>"
Response.Write DateDiff("d",#01/02/2003 12:00:00AM#,Now()) & "<br>"
Response.Write DateDiff("d",#01/03/2003 12:00:00AM#,Now()) & "<br>"
Response.Write DateDiff("d",#01/04/2003 12:00:00AM#,Now()) & "<br>"
Response.Write DateDiff("d",#01/05/2003 12:00:00AM#,Now()) & "<br>"
Response.Write DateDiff("d",#01/06/2003 12:00:00AM#,Now()) & "<br>"

If DateDiff("d",#12/30/2002 12:00:00AM#,Now()) >= 14 Then
Response.Write "This <b>is</b> Greater Then or Equal To 14"
Else
Response.Write "This <b>isn't</b> Greater Then or Equal To 14"
End If
%>

Results:
18
17
16
15
14
13
12
11
10
9
8
7
This is Greater Then or Equal To 14

[holty]

Thank you very much

seem to have a syntax error thou when trying to compare the date out of the db

If DateDiff("d",#rsAdvertDetails("Date")#,Date()) <= 14 Then

can you spot it?

Thanks

[landon11]

you have Date() instead of Now() is the only difference I see

[holty]

took out the hashes and the syntax error has gone however, its producing no results

heres my code

<div id="foldheader">&nbsp;&nbsp; Click For Current Adverts listed</div>
<div id="foldinglist" style="display:none" style=&{head};>
<div id="foldheader">
<%
'Loop through the recordset
If not rsAdvertDetails.EOF then
If DateDiff("d",rsAdvertDetails("Date"),Date()) <= 14 Then
Do While not rsAdvertDetails.EOF
%>
<center>
<table border="1" cellpadding="0" cellspacing="0" width="95%">
<tr>
<td width="100%" bgcolor="#5E6D95" class="heading" colspan="2">
<b>&raquo; Current Advert Information for <%=request.cookies("Username")%></b></td>
</tr>
<tr>
<td width="25%" class="middle"><b>Model: </b></font> </td>
<td width="75%" class="middle"><%=rsAdvertDetails("ModelDescription")%>&nbsp;</td>
</tr>
<tr>
<td width="25%" class="middle"><b>Model type: </b></font> </td>
<td width="75%" class="middle"><%=rsAdvertDetails("ModelTypeDescription")%>&nbsp;</td>
</tr>
<tr>
<td width="25%" class="middle"><b>Description: </b></font> </td>
<td width="75%" class="middle"><%=rsAdvertDetails("Description")%>&nbsp;</td>
</tr>
<tr>
<td width="25%" class="middle"><b>Year: </b></font> </td>
<td width="75%" class="middle"><%=rsAdvertDetails("Year")%>&nbsp;</td>
</tr>
<tr>
<td width="25%" class="middle"><b>Image: </b></font> </td>
<td width="75%" class="middle"><%=rsAdvertDetails("Image")%>&nbsp;</td>
</tr>
<tr>
<td width="25%" class="middle"><b>Price: </b></font> </td>
<td width="75%" class="middle"><%=rsAdvertDetails("Price")%>&nbsp;</td>
</tr>
<tr>
<td width="25%" class="middle"><b>Date: </b></font> </td>
<td width="75%" class="middle"><%=rsAdvertDetails("Date")%>&nbsp;</td>
</tr>
<tr>
<td width="100%" class="middle" colspan="2"><center><form method="POST" action="updateadvert.asp?advertID=<%=rsAdvertDetails("AdvertID")%>">
<INPUT TYPE=SUBMIT NAME="cmdLogout" VALUE="Revise this advert"></center>
</form>
</table>
<br>
</center>

<%

'Move to the next record in the recordset
rsAdvertDetails.MoveNext

Loop
%>

</div>
</div>
<% end if %>
<%
else
Response.Write ("<br>")
Response.Write ("You do not have any adverts currently listed")
end if
%>


Anyone see any obvious errors?

[holty]

Landon11,

I have changed it to Date() rather than Now() as when an advert is added I have assigned Date to Date() rather than to Now(). I could change it to be assigned to Now() and try that. Will give it a go now actually :)

[holty]

Just changed it now to assign Now() to the Date field when u add an advert.

It works but doesn't show - Response.Write ("You do not have any adverts currently listed") for some reason

[holty]

all sorted, ignore me its been one of those days!!

Thanks for all the help!

[Morgoth]

So your good? It's all working well?

If not, I have noticed a wierd problem with my ASP and MS access.

The date from Now() is different then the date in my database because of:

MM/DD/YYYY
And
DD/MM/YYYY

To fix this problem, I went with the database, and any text I use for the date I do this:
<%=FormatDateTime(Now(), VbLongDate) & " " & FormatDateTime(Now(), VbLongTime)%>

It will come out the same.

The database has that default text thing for it's fields, I put Now() in it for the date.