ASP - Access and dates

[Tom Jordan]

Hi All,

Some suggestions with this would be appreciated.

I have a page that gives customers the ability to retrieve records for
the last 3 months, based on a DATEADDED column in my DB. I use a drop
down menu with the past 3 months in it, which then self submits. The
default is the current month.

It works fine if I select the first month, for instance using May,
June, July. If I select May it returns all the records for May. But
anything else it just returns every single record for that customer!

Here is a snippet:

-------------
customerid = request.cookies("validated_id")

if request.form("month") = "" then
BaseDate = date()
strDatefrom = DateSerial(Year(BaseDate), Month(BaseDate) + iOffset, 1)

BaseDate = date()
strDateto = DateSerial(Year(BaseDate), Month(BaseDate) + 1, 0)

else
BaseDate = request.form("month")
strDatefrom = DateSerial(Year(BaseDate), Month(BaseDate) + iOffset, 1)
response.write DateSerial(Year(BaseDate), Month(BaseDate) + iOffset,
1) & "<br>"

BaseDate = request.form("month")
strDateto = DateSerial(Year(BaseDate), Month(BaseDate) + 1, 0)
end if

strSQLquery = "SELECT * FROM unlocks WHERE dateadded BETWEEN #" &
strDatefrom & "# AND #" & strDateto & "# AND customerid = '" &
customerid & "' order by unlockid desc"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3

-------------------

Thanks in advance guys! :)

Tom

[Bob Barrows [MVP]]

Use Response.Write to debug your SQL statement. This is the only way to find
out what your database's query engine is seeing:

Response.Write strSQLquery
Response.End

If the query is constructed correctly, you should be able to copy it to the
clipboard from the browser window, open your database in Access, create a
new query in Design View (closing the choose Tables dialog without selecting
a table), paste the statement into SQL View window, and run it without
modification. If it does not return the correct results, then you should use
the Query Builder to create a query that does return the correct results and
compare the two.

If seeing the result from this response.write does not help you solve your
problem, then show it to us. We cannot debug your code without seeing what's
going into your code and what's coming out of it.

Bob Barrows
Tom Jordan wrote:

> Hi All,
>
> Some suggestions with this would be appreciated.
>
> I have a page that gives customers the ability to retrieve records for
> the last 3 months, based on a DATEADDED column in my DB. I use a drop
> down menu with the past 3 months in it, which then self submits. The
> default is the current month.
>
> It works fine if I select the first month, for instance using May,
> June, July. If I select May it returns all the records for May. But
> anything else it just returns every single record for that customer!
>

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Tom Jordan]

thanks for the response Bob,

I've figured out the problem - but can't fix it :(

My Access DB is in dd/mm/yyyy format, my server is set to the same
format and I have also used "Session.LCID = 3081" to set the date
format.

But, the SQL query will only accept mm/dd/yyyy format!

If anyone has any suggestions then I would be v. grateful!

Tom

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:<#bYyTu1XEHA.1684@tk2msftngp13.phx.gbl>...

> Use Response.Write to debug your SQL statement. This is the only way to find
> out what your database's query engine is seeing:
>
> Response.Write strSQLquery
> Response.End
>
> If the query is constructed correctly, you should be able to copy it to the
> clipboard from the browser window, open your database in Access, create a
> new query in Design View (closing the choose Tables dialog without selecting
> a table), paste the statement into SQL View window, and run it without
> modification. If it does not return the correct results, then you should use
> the Query Builder to create a query that does return the correct results and
> compare the two.
>
> If seeing the result from this response.write does not help you solve your
> problem, then show it to us. We cannot debug your code without seeing what's
> going into your code and what's coming out of it.
>
> Bob Barrows
> Tom Jordan wrote:

> > Hi All,
> >
> > Some suggestions with this would be appreciated.
> >
> > I have a page that gives customers the ability to retrieve records for
> > the last 3 months, based on a DATEADDED column in my DB. I use a drop
> > down menu with the past 3 months in it, which then self submits. The
> > default is the current month.
> >
> > It works fine if I select the first month, for instance using May,
> > June, July. If I select May it returns all the records for May. But
> > anything else it just returns every single record for that customer!
> >

[Tom Jordan]

For the record - I've found the answer.

Apparently, the underlying SQL query engine is ALWAYS set to US
format, and cannot be altered.

I found a workaround at 4Guys:
[url]http://www.4guysfromrolla.com/webtech/041001-1.2.shtml[/url]

Tom

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:<#bYyTu1XEHA.1684@tk2msftngp13.phx.gbl>...

> Use Response.Write to debug your SQL statement. This is the only way to find
> out what your database's query engine is seeing:
>
> Response.Write strSQLquery
> Response.End
>
> If the query is constructed correctly, you should be able to copy it to the
> clipboard from the browser window, open your database in Access, create a
> new query in Design View (closing the choose Tables dialog without selecting
> a table), paste the statement into SQL View window, and run it without
> modification. If it does not return the correct results, then you should use
> the Query Builder to create a query that does return the correct results and
> compare the two.
>
> If seeing the result from this response.write does not help you solve your
> problem, then show it to us. We cannot debug your code without seeing what's
> going into your code and what's coming out of it.
>
> Bob Barrows
> Tom Jordan wrote:

> > Hi All,
> >
> > Some suggestions with this would be appreciated.
> >
> > I have a page that gives customers the ability to retrieve records for
> > the last 3 months, based on a DATEADDED column in my DB. I use a drop
> > down menu with the past 3 months in it, which then self submits. The
> > default is the current month.
> >
> > It works fine if I select the first month, for instance using May,
> > June, July. If I select May it returns all the records for May. But
> > anything else it just returns every single record for that customer!
> >

[Evertjan.]

Tom Jordan wrote on 02 jul 2004 in microsoft.public.inetserver.asp.db:

> My Access DB is in dd/mm/yyyy format,

Not true, [unless you store your dates in a textfield]

The dates are stored in an internal numeric value, independent of the in
and outut format.

The access PROGRAMME has it's own in- and output format, the database does
not. So if you use an aspect database in an ASP environment, ysing the jet
engine, you are not.

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Bob Barrows [MVP]]

Tom Jordan wrote:

> thanks for the response Bob,
>
> I've figured out the problem - but can't fix it :(
>
> My Access DB is in dd/mm/yyyy format, my server is set to the same
> format and I have also used "Session.LCID = 3081" to set the date
> format.
>
> But, the SQL query will only accept mm/dd/yyyy format!
>
> If anyone has any suggestions then I would be v. grateful!
>

[url]http://www.aspfaq.com/show.asp?id=2313[/url] vbscript
[url]http://www.aspfaq.com/show.asp?id=2040[/url] help with dates
[url]http://www.aspfaq.com/show.asp?id=2260[/url] dd/mm/yyy confusion

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"