Retrieve date value issue

Report · Mar 03, 2007

Hi all,

I am trying to retrieve a count of booking entries made 30 days ago, below
is the end of the query I am having problems with.

dbo.booking_form.TimeOfBooking = DATEADD(day, -30, GetDate()) GROUP BY
dbo.booking_form.TimeOfBooking")

When I use the = sign the error reads

Either BOF or EOF is True, or the current record has been deleted. Requested
operation requires a current record.

for the entry on the page

(rsCash30("CountOfTimeOfBooking"))

But I know the record exists, also the query works when I use the signs < or
> or <> all works ok but not with the = ??

Full SQL query reads

Set rsCash30 = myConn.Execute("SELECT Count(dbo.booking_form.TimeOfBooking)
AS CountOfTimeOfBooking FROM dbo.booking_form WHERE
dbo.booking_form.allocated = 'Completed' AND dbo.booking_form.CustomerID
='0' AND dbo.booking_form.TimeOfBooking = DATEADD(day, -30, GetDate()) GROUP
BY dbo.booking_form.TimeOfBooking")

Regards

--
Simon Gare
The Gare Group Limited

website: www.thegaregroup.co.uk
website: www.privatehiresolutions.co.uk

Report · Mar 03, 2007

Does the recordset return any results when you click the "Test" button in the recordset dialog in DW?

I notice your syntax is wrong for this: (rsCash30("CountOfTimeOfBooking"))

It should be: rsCash30.Fields.Item("CountOfTimeOfBooking").Value

and if if within html it needs to be surrounded by <%= %>

I also think you may be getting the error using "=" sign because the Date/Time datatype is seen as a string, so if you are using an Access database it needs to be surrounded by # signs.

I just noticed you are using MS SQL as your DB. So try placing single quotes around your date.

Report · Mar 03, 2007

Sorry I was abbreviating I am using SQL 2000, cant test as SQL statement is
developed outside dreamweaver in this case, what is the issue with = and
Date/Time? how would it be surrounded by #?

Thanks in advance

Simon

"envision3d" <webforumsuser@macromedia.com> wrote in message
news:escl82$ous$1@forums.macromedia.com...
> Does the recordset return any results when you click the "Test" button in
the
> recordset dialog in DW?
>
> I notice your syntax is wrong for this:
(rsCash30("CountOfTimeOfBooking"))
>
> It should be: rsCash30.Fields.Item("CountOfTimeOfBooking").Value
>
> and if if within html it needs to be surrounded by <%= %>
>
> I also think you may be getting the error using "=" sign because the
Date/Time
> datatype is seen as a string, so if you are using an Access database it
needs
> to be surrounded by # signs.
>
>

Report · Mar 03, 2007

Since you are using MS SQL you would not use the # signs. Also I notice that the customerID=0 in the WHERE clause. Make sure there is a user with the ID of 0 in the DB, normally when you use the int increment the first value is 1, and you have the customerID = '0' indecating that it is a strring value. Double check your data type. I am guessing your customerID is an int so remove the quotes from around the value.