Problem with DateAdd

[stevem2004]

Hi,
I am using an Access DB & I am trying to find out how many products expire in the next 14 days & in the next month.

I have been trying to use DateDiff & DateAdd but it is not working, can someone please help. I know I'm doing something wrong...but what??

My code is as follows:-

<%
dim conn, rs, strconn, strSQL, strDate

strDate = Date()

strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("../database")
set conn = server.createobject("adodb.connection")
conn.open strconn
strSQL = "SELECT * FROM table WHERE dateend = " & DateAdd("d", 14, strDate) & " ORDER BY promocode ASC;"
set rs = server.createobject("adodb.recordset")
rs.open strSQL, conn, 3, 3

if rs.EOF and rs.BOF then
response.write ("<p class=""normalbold"">No products expiring.")
response.end
end if
%>

TIA
Steve

[lmf232s]

You need ' ' around your date like

dateend = '" & dateadd("d", 14, strDate) & "'

but you could try this also.

Create a new variable
NewDate = DateAdd("d", 14, Date)

SQL = "SELECT * FROM table WHERE (dateend = '" & NewDate & "') ORDER BY promocode ASC;"

also if you do a
response.write SQL or in your case strSQL
right after you SQL statement you can see whats going on with it and see the values. You may notice that your dateadd is doing nothing

bottom line is you need the ' ' around your dateadd '" & ? & "'

[buntine]

It seems the dateend field in your Access database is of type date. f this is so, you will need to use the # character apposed to single quotes.

SQL = "SELECT * FROM table WHERE (dateend = #" & DateAdd("d", 14, strDate) & "#) ORDER BY promocode ASC;"

Regards,
Andrew Buntine.

[buntine]

On second thought, you may want to look up the SQL DATEDIFF() function. Im not sure how well Access likes the equality operator for date comparison.

Regards.

[jytioh]

I have problem with the server time for daylight saving.

I am hosting my site in the US on a server with daylight saving. But my site is a global site which has users from areas with and without daylight saving. I know how to adjust for difference in time zones using dateadd function. But to cater for daylight saving changes is way more tricky than that.

Eg. Let say the server time in US is GMT -6 hours during non-daylight saving time and GMT -5 during daylight saving. For people with daylight saving, eg. Europe, northern US there won't be problem as their time will be adjusted in the same way. The DateAdd function will be sufficient. However, for places like Malaysia, during the daylight saving period, the server time will be faster by one hour and hence the 'Malaysian time' shown on the server will be one hour faster. How do I go about this to make sure that my site could handle daylight saving changes automatically?

Thank you.

[stevem2004]

Thank-you imf232s & buntine, although this now works I am having problems getting what I want with this query.

I am trying to find the products that will be expiring in 14 days, when I run the query the page is displaying everything that has a future date, including the ones that would be expiring, but what I really want is for it to display just the ones that are going to expire in the next 14 days, Any suggestions? or do I have to use the DateDiff function?