DRW month query

[bender]

using an Access d/b, trying to make a page where user can input the
number of the month to find out how many files were created in the
month.

custom query i'm trying to use:

SELECT count(*)AS filecount FROM Results WHERE
DatePart('m',[timestamp]) = ::monthnumber::

doesn't work.

btw, i changed the timestamp to a short date field. was this
necessary? As is, if I put in a 2 instead of the ::monthnumber::, it
works ok.

Thanks,

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."

 

[Jim Buyens]

Make sure that the form that invokes the DRW page contains
a text box (or other form element) named monthnumber. i.e.:

<form action="mydrwpage.asp">
<input type="text" name="monthnumber">
....
</form>

If you still can't get this to work, post again to this
thread and include the *exact* error message.

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------

 

[bender]

Make sure that the form that invokes the DRW page contains
a text box (or other form element) named monthnumber. i.e.:

<form action="mydrwpage.asp">
<input type="text" name="monthnumber">
...
</form>

If you still can't get this to work, post again to this
thread and include the *exact* error message.

++++++++++++++++++++++++++++++++++++++++++++++++
Have gotten it to work using just ASP

But using the DRW, i let it create a search form, and it created a
textbox as follows:

<input TYPE="TEXT" NAME="month"
VALUE="<%=Server.HtmlEncode(Request("month"))%>"></td>

the error that is displayed when i run the page is:

Database Results Wizard Error
The operation failed. If this continues, please contact your server
administrator.

it is a puzzlement. as i mentioned before, putting in the number 2
instead of the '::month::' in the custom query produces the correct
answer (the number of files for february)

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."

 

[Jim Buyens]

If the name of hte text box is going to be month, the query should be:

SELECT count(*) AS filecount FROM Results WHERE
DatePart('m',[timestamp]) = ::month::

Because DatePart('m',[timestamp]) returns a number, you must compare
it to a number (like 2) and not to a string (like '2').

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------

 

[bender]

If the name of hte text box is going to be month, the query should be:

SELECT count(*) AS filecount FROM Results WHERE
DatePart('m',[timestamp]) = ::month::

Because DatePart('m',[timestamp]) returns a number, you must compare
it to a number (like 2) and not to a string (like '2').

++++++++++++++++++++++++++++++++++++++++++++++++++++
thought about that...doesn't work. then i tried surrounding it with a
host of other characters, including #'s and %'s ad nauseum. Like I
say, I've gotten this to work with asp, where oddly enuff, the SQL
string works when the month is shown as ' " & month & " '

oh well....

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."

 

[Jim Buyens]

Well, I tried the same thing with the sample Northwind databaseis and
it worked like a champ. Here's the text box

<input type="text" name="T1" size="20" value="<%=request("T1")%>">

and here's the query:

SELECT EmployeeID,
LastName,
FirstName,
BirthDate
FROM Employees
where DatePart('m',[BirthDate]) = ::T1::

This is definitely getting a bit spooky. The name TimeStamp is a
reserved word to Access, but surrounding it with square brackets (as
you've already done) normally avoids any issues. Results.[TimeStamp]
might work, or you might have to rename the field.

Spooky.

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------

If the name of hte text box is going to be month, the query should be:

SELECT count(*) AS filecount FROM Results WHERE
DatePart('m',[timestamp]) = ::month::

Because DatePart('m',[timestamp]) returns a number, you must compare
it to a number (like 2) and not to a string (like '2').

++++++++++++++++++++++++++++++++++++++++++++++++++++
thought about that...doesn't work. then i tried surrounding it with a
host of other characters, including #'s and %'s ad nauseum. Like I
say, I've gotten this to work with asp, where oddly enuff, the SQL
string works when the month is shown as ' " & month & " '

oh well....

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."

 

[bender]

Well, I tried the same thing with the sample Northwind databaseis and
it worked like a champ. Here's the text box

<input type="text" name="T1" size="20" value="<%=request("T1")%>">

and here's the query:

SELECT EmployeeID,
LastName,
FirstName,
BirthDate
FROM Employees
where DatePart('m',[BirthDate]) = ::T1::

This is definitely getting a bit spooky. The name TimeStamp is a
reserved word to Access, but surrounding it with square brackets (as
you've already done) normally avoids any issues. Results.[TimeStamp]
might work, or you might have to rename the field.

Spooky.

++++++++++++++++++++++++++++++++++++++++++++++
I'm wondering if the spookyness doesn't come from the fact that my
query involves the Count function.

SELECT count(*)AS filecount FROM Results WHERE
DatePart('m',[timestamp]) = ::monthnumber::

but i'll also try renaming and see if I get any joy.

thanks again.

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."