I have a database with a table named events and 2 date/time fields named display_date and display_time.

I need to build a sql statement that will display the proper record on the right date and the right time and then move to the next record based on the date and time in those fields.

i currently have:
<%
dte = Date()
hrs = Now()

strSQLQuery = "SELECT * FROM Events Where Display_date=Date() and Display_Time=Now()"

Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
%>

any particular reason why? i mean, the SQL would be so much easier if you used just one

not so good --
display_date: 2007-10-22 00:00:00
display_time: 1970-01-01 09:37:00

a lot better --
display_datetime: 2007-10-22 09:37:00

yes .. because we may have 2 events on the same day and i will need to display the first one till maybe noon and them i need it to switch to the next one.

but if there is a better way to handle this i would be glad to try it.

So compare the display_datetime with the current date time..?
If you want it displayed until tomorrow (24-Oct-07) 1:36pm then, as r937 has pointed out, store the full datetime value i.e. 2007-10-24 13:36:00.000

I am still having trouble and really need some help here please.

Ok I made a new field named Display_DateTime and changed my query to:

<%
dte=Date
strSQLQuery = "SELECT * FROM Events Where Display_DateTime=Now()"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
%>

<% if rs("Display_DateTime")<>"" then %>
<td background="images/events/event_bg.gif"><font face="BellGothic Blk BT" size="4" color="#FFFFFF"><%=rs("event_name" & prefixtable)%></font></td>
<%else%>
<td background="images/events/event_bg.gif"><font face="BellGothic Blk BT" size="4" color="#FFFFFF"><%response.write("There are no events scheduled")%></font></td>
<%end if%>

And it won't work. Also do a need a Do While and Loop statement?

I need to make sure the current event remains displayed until the next Display_DateTime is right

when you use Now(), it produces the current datetime, down to the millisecond at the time you execute it

i think it would be a huge coincidence to find a row in the table which had been stored with that exact time ahead of time



but i still don't understand, what does "remains displayed until the next Display_DateTime is right" mean?

how are you storing values in that column? can you show the actual INSERT statement?

What i mean is that Event 1 remains displayed until event 2 comes up based on Display_DateTime

how are you storing values in that column? can you show the actual INSERT statement?

I insert them manually .. I have the server this is hosted on.

The Table is Events

Fields Are:

Event_Name (memo field)
Event_Date (text field)
Event_Time (text field)
Event_Location (memo field)
Event_Info (memo field)
Display_DateTime (date/time field)

This isn't about inserting the data it is about displaying the data.

it is at larryricketts.com if it helps you see what i am trying to do.

okay, let me come at this a different way

your table contains past and future events, correct? i mean, in the context of the current datetime Now(), which i think you'll agree you'll never actually hit, so each event is either on one side or the other of it, yes?

is it fair to say that you want just the next upcoming event?

that's actually easier than it looks

you can remove the separate date and time columns from your table too

Ok .. yes the database will have past and future events in it. Lets say that i have an event coming up on november 1st but i want to begin dispalying it today and then on november 2nd i want it to change to the next event.

I think we agree on that part. But lets say i have an event on november 1st at 8:00 am and another one at 12:00 pm. I need it to change to that next event at 8:01 am.

Thus I have the Display_DateTime field. The Display_DateTime field is never displayed it is only the criteria to base what is displayed and when.

The Event_Name, Event_Date, Event_Time, Event_Location and Event_Info are the fields that will actually get displayed.

If we happen to get to the end of the database and there are no more records and all events have been displayed it will say "There Are No Events Scheduled"

So what i am looking for is a way to make this happen.

ok, do me a favour and try my query

kthxbye