something strange, any ideas

[Sproul]

I was trying to get frontpage to reurn records from a database between 2
dates, i tried using the query that frontpage uses i.e.

SELECT * FROM Results WHERE (Timestamp > '::start::' OR Timestamp =
'::start::' AND Timestamp < '::finish::' OR Timestamp = '::finish::')

I discovered that for my particular results thit should be

SELECT * FROM Results WHERE ((Timestamp > '::start::' OR Timestamp =
'::start::') AND (Timestamp < '::finish::' OR Timestamp = '::finish::'))

for the start date i can get records greater than and equal to, but for the
finish date i can cane records less than but not equal to.

so i tried modifying the code (using the SQL book i bough last week) and
came up with this

SELECT * FROM Results WHERE (Timestamp >= '::start::') AND (Timestamp <=
'::finish::')

but i still cant return results equal to the finish date.

i also tried using the between clause, the code was verified, but frontpage
didn't like it when it was published.

any ideas.

 

[Sproul]

Me again, the error i get when using the between clause is

Database Results Wizard Error
Unable to find operator in query string. Query string currently is SELECT *
FROM Results WHERE Timestamp BETWEEN '::start::' AND '::finish::'



Al....

 

[Stefan B Rusynko]

You are delimiting date variables (start & finish) w/ char delimiters (')
Try
strSQL= "SELECT * FROM Results WHERE (Timestamp>=#" & start & "# AND Timestamp<=#" & finish & "#)"

PS
You were advise previously Timestamp is a reserved word in Access/SQL
See http://www.aspfaq.com/show.asp?id=2080#t
- unfortunately the FP DBRW creates that field name when you use it (change it in Access)




|I was trying to get frontpage to reurn records from a database between 2
| dates, i tried using the query that frontpage uses i.e.
|
| SELECT * FROM Results WHERE (Timestamp > '::start::' OR Timestamp =
| '::start::' AND Timestamp < '::finish::' OR Timestamp = '::finish::')
|
| I discovered that for my particular results thit should be
|
| SELECT * FROM Results WHERE ((Timestamp > '::start::' OR Timestamp =
| '::start::') AND (Timestamp < '::finish::' OR Timestamp = '::finish::'))
|
| for the start date i can get records greater than and equal to, but for the
| finish date i can cane records less than but not equal to.
|
| so i tried modifying the code (using the SQL book i bough last week) and
| came up with this
|
| SELECT * FROM Results WHERE (Timestamp >= '::start::') AND (Timestamp <=
| '::finish::')
|
| but i still cant return results equal to the finish date.
|
| i also tried using the between clause, the code was verified, but frontpage
| didn't like it when it was published.
|
| any ideas.

 

[Sproul]

ok i'm assuming that the query you wrote below should be done with SQL and
not done with Frontpage custom query, as it doesn't work.

error as follows

Description: Syntax error in date in query expression '(Timestamp>=## AND
Timestamp<=##)'.
Number: -2147217913 (0x80040E07)

to get the query to work through using frontpage i've got to put the fields
in '::Field_name::' otherwise it wont work.

Also i don't understand what you mean by delimiting, i thought the query
input fields were setting the limit.



Al....

 

[Sproul]

I've changed the field in ACCESS so that iot is not called Timestamp and is
now called TSinfo.

code now looks like this

SELECT * FROM Results WHERE (TSinfo >= '::start::' AND TSinfo <=
'::finish::')

i know you said not to use the ' but if i put #" & start & "# then the FP
DBRW doesn't accept it, i'm assuming that using " is closing the statement or
string or whatever you call it.

Al....

 

[Sproul]

i guess i'll just put a note saying the finish date is not included in the
search.


Al....

 

[Kevin Spencer]

That is actually valid SQL, Stefan, for later versions of Access, which
conform more closely to ISO standards. The ISO standard date delimiter is
the single quote.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Everybody picks their nose,
But some people are better at hiding it.