Current Date and on records only 1

[RicCross]

Right I’m still not very good at visual Interdev so some one please help

I have an asp page that links to an access database with a booking table. I have a grid that I just want to show the future bookings. I have it linked to a record set with
The following sql

SELECT `Booking number`, `Date`, `Real start time`, `Real finish time`, Room FROM Booking ORDER BY `Date`

What do I need to change so that only booking with the current date or future dates are included in the grid


Thanks for any help or suggestions
Ric
Rick@soton.ac.uk

 

[MerlinB]

Change the SQL to use the Access method that returns the current date:
WHERE datediff('day', [Date], date()) >= 0
or just
WHERE [Date] >= date()
the second method works if the date column is guaranteed to NOT have a time element (other than the default).

You may want to format the date, which you can do
* on the SQL server
SELECT format('dd mmm yy', [Date]) as fmtDate
* or when building the grid (non DTC way):
<TD><%=formatDateTime(rsBookings.fields(&quot;Date&quot.value)%>

If you use DTC's for the recordset and/or the DTC Grid, then you may have fun with the format function - as the database column is cast to a string (i.e. formatted into a string) within the Recordset DTC code using the server Locale setting - which may not be the one you expect.

[For your information...]
Finally, you may want to add a button or combo box so the end user can see different date ranges (last month/next month/all time etc.). Your SQL may change to use Query Parameter(s), for example:

WHERE datediff('day', [Date], date()) between ? and ?

if you use a Recordset DTC, then provide the number of days range as:
rsBookings.close 'should test if open first!
rsBookings.setParameter 0, nStartDays
rsBookings.setParameter 1, nEndDays
rsBookings.open
where nStartDays/nEndDays are numbers depending on the options chosen (i.e. last month = -31 to 0).

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[RicCross]

Thanks for the reply much welcome

I'm still having a few problems tho. the databse i'm trying to connection is an access database that is connected with a dns odbc conection.

Im using the sql builder when i try and a add => date() to the criteria i get a message with &quot;Your entery cannot be coverted to a valid date time value&quot; what am i doing wrong

any help much welcome

Ric

 

[MerlinB]

OK. It would have helped if I tested it first (i am used to SQLServer which uses this syntax)!

Try wrapping Access functions in { fn FUNCTION() }

eg.
SELECT { fn DateDiff('d', [DATE], { fn NOW() }) }
AS DateFunctionTest,
FROM BOOKINGS
WHERE { fn DateDiff('d', [DATE], { fn NOW() }) } < 20

..OR..

add a query to the access database with the required functionality, and reference this in ASP instead. If you use the Data Environment, these appear as 'Views'.

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[RicCross]

Thanks man that works a treat


thanks, thanks, thanks,


Ric